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.

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.

• Read Only Tables – Is it Possible? – SQL in Sixty Seconds #179
• One Scan for 3 Count Sum – SQL in Sixty Seconds #178
• SUM(1) vs COUNT(1) Performance Battle – SQL in Sixty Seconds #177
• COUNT(*) and COUNT(1): Performance Battle – SQL in Sixty Seconds #176
• COUNT(*) and Index – SQL in Sixty Seconds #175
• Index Scans – Good or Bad? – SQL in Sixty Seconds #174
• Optimize for Ad Hoc Workloads – SQL in Sixty Seconds #173
• Avoid Join Hints – SQL in Sixty Seconds #172
• One Query Many Plans – SQL in Sixty Seconds #171

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