Recently a friend of mine asked to help, their SQL Server was not showing correct database free space information even when they have added new data file. As they add new data into their database the previously existing database data files are not getting filled with data, rather the newer data files are getting filled. 

As soon as I heard the issue from him, I knew it was the due to the inaccuracies in the catalog views maintained by the SQL Server. I suggested him to run DBCC UPDATEUSAGE command on his database to correct this inaccuracies. 

In order to show you an example, I simulated the same scenario on my personal server, I added few tables and filled plenty of data into those tables. Later I purged bulk of the data in those tables to simulate the inaccuracies. Below is an example DBCC UPDATEUSAGE command that I ran on one of the tables in my database.  

Note that I used the database name ‘LearnSQLWithBru‘ and the table ‘transaction_history‘ in the above example. 

The DBCC command can be run on a database to correct inaccuracies of all objects in that database or you can also correct the inaccuracies of a single table by including that table name. If you would like to run the command for a particular database, then you can run the following command..

DBCC UPDATEUSAGE ('DATABASENAME')

You can also run the below command to correct the inaccuracies for the current database.

DBCC UPDATEUSAGE (0)

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.

— Bru Medishetty