Transact-SQL Reference

DBCC UPDATEUSAGE

Reports and corrects inaccuracies in the sysindexes table, which may result in incorrect space usage reports by the sp_spaceused system stored procedure.

Syntax

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

Arguments

'database_name' | 0

Is the name of the database for which to report and correct space usage statistics. Database names must conform to the rules for identifiers. For more information, see Using Identifiers. If 0 is specified, then the current database is used.

'table_name' | 'view_name'

Is the name of the table or indexed view for which to report and correct space usage statistics. Table and view names must conform to the rules for identifiers.

index_id | 'index_name'

Is the identification (ID) number or index name of the index to use. If not specified, the statement processes all indexes for the specified table or view.

COUNT_ROWS

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

NO_INFOMSGS

Suppresses all informational messages.

Remarks

DBCC UPDATEUSAGE corrects the rows, used, reserved, and dpages columns of the sysindexes table for tables and clustered indexes. Size information is not maintained for nonclustered indexes.

If there are no inaccuracies in sysindexes, DBCC UPDATEUSAGE returns no data. If inaccuracies are found and corrected and the WITH NO_INFOMSGS option is not used, UPDATEUSAGE returns the rows and columns being updated in sysindexes.

Use UPDATEUSAGE to synchronize space-usage counters. DBCC UPDATEUSAGE can take some time to run on large tables or databases, so it should typically be used only when you suspect incorrect values returned by sp_spaceused. sp_spaceused accepts an optional parameter to run DBCC UPDATEUSAGE before returning space information for the table or index.

Result Sets

DBCC UPDATEUSAGE returns this result set for the Northwind database (values may vary):

DBCC UPDATEUSAGE: sysindexes row updated for table 'Orders' (index ID 4):
        USED pages: Changed from (2) to (4) pages.
        RSVD pages: Changed from (2) to (4) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'Orders' (index ID 5):
        USED pages: Changed from (2) to (4) pages.
        RSVD pages: Changed from (2) to (4) pages.
'...'
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Permissions

DBCC UPDATEUSAGE permissions default to members of the sysadmin fixed server role or the db_owner fixed database role, and are not transferable.

Examples

A. Update sysindexes by specifying 0 for the current database

This example specifies 0 for the database name and Microsoft® SQL Server™ reports information for the current database.

DBCC UPDATEUSAGE (0)
GO

B. Update sysindexes for pubs, suppressing informational messages

This example specifies pubs as the database name, and suppresses all informational messages.

DBCC UPDATEUSAGE ('pubs') WITH NO_INFOMSGS 
GO

C. Update sysindexes for the authors table

This example reports information about the authors table.

DBCC UPDATEUSAGE ('pubs','authors')
GO

D. Update sysindexes for a specified index

This example uses the index name, UPKCL_auidind.

DBCC UPDATEUSAGE ('pubs', 'authors', 'UPKCL_auidind')