SQL Q&A: Inside Clustering, Mysterious Hangs, the SA Account, and More -- TechNet Magazine, July 2007
Are you upgrading from SQL Server 2000 to SQL Server 2005?
If so, make sure you run DBCC UPDATEUSAGE right after your databases are upgraded.
DBCC UPDATEUSAGE reports and corrects pages and row count inaccuracies in the catalog views. These inaccuracies need to be corrected because they may cause incorrect space usage reports to be returned by the sp_spaceused system stored procedure. In SQL Server 2005, these values are always maintained correctly, so these databases should never experience incorrect counts. However, databases upgraded to SQL Server 2005 may contain invalid counts, so you should run DBCC UPDATEUSAGE after upgrading.
Here's how DBCC UPDATEUSAGE works. It 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 you have not used WITH NO_INFOMSGS, DBCC UPDATEUSAGE returns the rows and columns being updated in the system tables.
DBCC UPDATEUSAGE can also be used to synchronize space-usage counters. Because DBCC UPDATEUSAGE can take some time to run on large tables or databases, it should typically be used only when you suspect incorrect values are being returned by sp_spaceused. Note that sp_spaceused accepts an optional parameter to run DBCC UPDATEUSAGE before returning space information for the table or index.
DBCC CHECKDB has been enhanced in SQL Server 2005 to detect when page or row counts become negative. When negatives are detected, DBCC CHECKDB will output a warning and a recommendation to run DBCC UPDATEUSAGE to address the issue. Although it may appear as if upgrading the database to SQL Server 2005 caused this problem, be assured that the invalid counts existed before the upgrade procedure. As an example, here's how you would update page or row counts or both for all objects in the current database. The following command specifies 0 for the database name and DBCC UPDATEUSAGE reports updated information for the current database: DBCC UPDATEUSAGE (0); GO To update page or row counts or both for, say, AdventureWorks, and also to suppress informational messages, you would run a command similar the following, which specifies AdventureWorks as the database name and then suppresses all informational messages: USE AdventureWorks; GO DBCC UPDATEUSAGE ('AdventureWorks') WITH NO_INFOMSGS; GO For more information, search for DBCC UpdateUsage in SQL Server Books Online.