Learn more about SQL Server tools

   
   















































Use DBCC UPDATEUSAGE to get accurate SQL Server space allocation

MSSQLTips author Armando Prato By:   |   Read Comments (1)   |   Related Tips: More > Database Consistency Checks DBCCs

Problem
Microsoft does not guarantee that table and database space allocation information will be maintained accurately. In a high transaction OLTP database or after a large bulk copy of data has been performed, this can lead to inaccurate database space reporting when using the system stored procedure sp_spaceused.

Solution
Procedure sp_spaceused is a system stored procedure that is provided with SQL Server to report on database table and index space allocation (total rows, data space used, index space used, etc). In addition, it can be used to just report a summary on the total disk space usage for an entire database. It allows the DBA to monitor disk usage and it allows for capacity planning.

The likely reason Microsoft cannot guarantee accurate allocation information and row counts is to prevent database blocking. If table space allocation and row count information were to be maintained accurately on every INSERT and DELETE, or when an index is dropped, or when a large bulk copy operation is performed, database concurrency could suffer as users in a high transaction environment wait for their transactions to complete as the space information is maintained in real time.

To ensure accurate space allocation reporting by sp_spaceused, DBCC UPDATEUSAGE can be executed against the database to scan all data pages and to update all space allocation information. This command can be run against a specific database table or against an entire database.

DBCC UPDATEUSAGE can either be run standalone prior to running sp_spaceused against your target database or it can be run as a parameter to system stored procedure sp_spaceused as follows:

exec sp_spaceused @updateusage = ‘true'

Running sp_spaceused with the @updateusage = ‘true' parameter tells the sp_spaceused procedure to update space allocation information prior to reporting on the space allocation. Under the covers, this sp_spaceused parameter actually runs DBCC UPDATEUSAGE against the database effectively saving an extra command.

However, you may still see the number of rows being reported inaccurately by sp_spaceused. That's because running DBCC UPDATEUSAGE only updates space allocation information (which is what occurs when the @updateusage = ‘true' parameter is used) but does not update the row count information that is stored in the sysindexes table which is the source of the rows data in the report generated (in SQL Server 2005, sp¬_spaceused reads this information from DMV sys.dm_db_partition_stats).

To get an accurate row count to go along with your allocation information, DBCC UPDATEUSAGE can be run with option COUNT_ROWS. Extending the command with this option will ensure the most accurate reporting possible. As a result, you may prefer to run DBCC UPDATEUSAGE WITH COUNT_ROWS first and then run sp_spaceused without the @updateusage parameter.

The row count produced by sp_spaceused should not be confused with performing SELECT COUNT(*) on your tables. Performing SELECT COUNT(*) will always produce an accurate count since it scans the table in real time. The row count produced by sp_spaceused is pulled from information stored in sysindexes (SQL Server 2000) and sys.dm_db_partition_stats (SQL Server 2005). They are guideline values that are leveraged by the SQL Server query optimizer for producing execution plans.

One thing to note is that DBCC UPDATEUSAGE holds share (S) locks on target tables and can take some time to run if a table is very large. Depending on your environment, you may wish to run this command during a maintenance window in order to prevent unnecessary and potentially harmful blocking in your database as it compiles the required information.

Next Steps

 



Last Update: 10/24/2007


About the author
MSSQLTips author Armando Prato
Armando Prato has close to 30 years of industry experience and has been working with SQL Server since version 6.5.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Wednesday, July 17, 2013 - 1:43:19 PM - Sri Read The Tip

Well written and nice article.




 
Sponsor Information