By: Armando Prato | Comments (2) | Related: > Database Console Commands 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
- Read more about DBCC UPDATEUSAGE and its options in the SQL Server 2000 and 2005 Books Online
- Read more about sp_spaceused in the SQL Server 2000 and 2005 Books Online
- Read more about DMV sys.dm_db_partition_stats and sys.partitions in the SQL Server 2005 Books Online
About the author
Armando Prato has close to 30 years of industry experience and has been working with SQL Server since version 6.5.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
