DBCC UPDATEUSAGE Statement (version 6.5)

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.

Syntax

DBCC UPDATEUSAGE ({0 | database_name} [, table_name [, index_id]])
[WITH COUNT_ROWS]

where

UPDATEUSAGE ({0 | database_name} [, table_name [, index_id]])

Reports and corrects inaccuracies in the sysindexes table that can result in incorrect space usage reports by the sp_spaceused system stored procedure.

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.

WITH COUNT_ROWS

Specifies that the rows column of sysindexes is updated with the current count of the number of rows in the table. This only applies to sysindexes rows that have an index_id of 0 or 1. This option can affect performance on large tables.

Remarks

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.

Example

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