Reports and corrects inaccuracies in the counts contained in the sysindexes table and includes the new WITH COUNT_ROWS syntax.
For additional syntax information for the DBCC statement, see the Microsoft SQL Server Transact-SQL Reference.
DBCC UPDATEUSAGE ({0 | database_name} [, table_name [, index_id]])
[WITH COUNT_ROWS]
where
This statement corrects the used, reserved, and dpages columns of the sysindexes table for any clustered indexes on objects of the type U (user-defined table) or S (system table). Size information is not maintained for nonclustered indexes. You can use this statement to synchronize space usage counters in sysindexes, which will result in accurate usage information being returned. When you use 0 instead of the database_name, the update is performed in the current database.
When processing, UPDATEUSAGE acquires a shared table lock on the table being processed, so updates to the sysindexes row for syslogs can be performed only if the database is in single-user mode and the syslogs table is specified. This prevents any logging during the update and ensures accurate changes. All other changes to sysindexes are fully logged.
Note The stored procedure sp_spaceused used with the @updateusage qualifier provides the same functionality as DBCC UPDATEUSAGE. The sp_spaceused stored procedure takes longer to execute. Using this option on large tables may take longer to complete because every row in the table is counted.
When the DBCC UPDATEUSAGE statement is executed with the WITH_COUNT_ROWS clause, inaccuracies are reported and corrected in the counts contained in the sysindexes table. The counts for rows, used, reserved, and dpages columns of the sysindexes table for any clustered indexes on objects of the type U (user-defined) or S (system table) are updated.
If there are inaccuracies in sysindexes, DBCC UPDATEUSAGE returns data that shows what rows and columns are being updated in sysindexes. Otherwise, no data is returned.
This example corrects all of the page and row counts in the sysindexes table for all the indexes on the authors table.
DBCC UPDATEUSAGE (pubs, authors) WITH COUNT_ROWS