DBCC UPDATEUSAGE (Transact-SQL)
Reports and corrects pages and row count inaccuracies in the catalog views. These inaccuracies may cause incorrect space usage reports returned by the sp_spaceused system stored procedure.
DBCC UPDATEUSAGE corrects the rows, used pages, reserved pages, leaf pages and data page counts for each partition in a table or index. If there are no inaccuracies in the system tables, DBCC UPDATEUSAGE returns no data. If inaccuracies are found and corrected and WITH NO_INFOMSGS is not used, DBCC UPDATEUSAGE returns the rows and columns being updated in the system tables.
DBCC CHECKDB has been enhanced to detect when page or row counts become negative. When detected, the DBCC CHECKDB output contains a warning and a recommendation to run DBCC UPDATEUSAGE to address the issue.
Always run DBCC UPDATEUSAGE after upgrading a database from SQL Server 2000. The page and row counts are corrected and are maintained thereafter.
Do not run DBCC UPDATEUSAGE routinely for databases created in SQL Server 2005 or higher or on upgraded databases that have been corrected once by using DBCC UPDATEUSAGE. Because DBCC UPDATEUSAGE can take some time to run on large tables or databases, it should not be used only unless you suspect incorrect values are being returned by sp_spaceused.
Consider running DBCC UPDATEUSAGE routinely (for example, weekly) only if the database undergoes frequent Data Definition Language (DDL) modifications, such as CREATE, ALTER, or DROP statements.
A. Updating page or row counts or both for all objects in the current database
The following example specifies 0 for the database name and DBCC UPDATEUSAGE reports updated page or row count information for the current database.
B. Updating page or row counts or both and suppressing informational messages
The following example specifies AdventureWorks2008R2 as the database name and suppresses all informational messages.
C. Updating page or row counts or both for a table
The following example reports updated page or row count information for the Employee table in the AdventureWorks2008R2 database.