SQL SERVER – Correcting Space Allocation with DBCC UPDATEUSAGE

I was recently called an interesting scenario by my client. Let us learn today Correcting Space Allocation with DBCC UPDATEUSAGE.

SQL SERVER - Correcting Space Allocation with DBCC UPDATEUSAGE DBCCUPDATEUSAGE-800x359

They had a huge table the size of a Terabyte. They had recently removed a huge varchar column from the table and after doing so, the table details from various DMVs were incorrect. The biggest challenge they were facing was related to the number of rows and size of the table. This was big deal as the difference was in many GB and they wanted the correct details to reflect for their newly modified table.

Here is the command we ran for the modified table to update rows and page count.

DBCC UPDATEUSAGE (DBName,'Schema.TableName');
GO 

Once we ran the command we got the correct information for the page count as well as row count.

If you want to update every single object in your database, you can also run the following command.

DBCC UPDATEUSAGE (0);
GO 

One more thing to do add here is that you should not confuse updating the row count or page count with updating statistics. They are both very different things.

If you liked this blog, please do not forget to subscribe to my YouTube Channel – SQL in Sixty Seconds.

Here are my few recent videos and I would like to know what is your feedback about them.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

, ,
Previous Post
SQL SERVER – Using CASE Expression in Default Value
Next Post
SQL SERVER – MAX Column ID Used in Table

Related Posts

Leave a Reply

Menu