DBCC UPDATEUSAGE (Transact-SQL)

Article
12/03/2008

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. In SQL Server 2005, these values are always maintained correctly. Databases created on SQL Server 2005 should never experience incorrect counts, however, databases upgraded to SQL Server 2005 may contain invalid counts. We recommend running DBCC UPDATEUSAGE after upgrading to SQL Server 2005 to correct any invalid counts.

Topic link icon Transact-SQL Syntax Conventions

Syntax

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

Arguments

database_name | database_id | 0
Is the name or ID of the database for which to report and correct space usage statistics. If 0 is specified, the current database is used. Database names must comply with the rules for identifiers.

table_name | table_id | view_name | view_id
Is the name or ID of the table or indexed view for which to report and correct space usage statistics. Table and view names must comply with the rules for identifiers.

index_id | index_name
Is the ID or name of the index to use. If not specified, the statement processes all indexes for the specified table or view.

WITH
Allows options to be specified.

NO_INFOMSGS
Suppresses all informational messages.

COUNT_ROWS
Specifies that the row count column is updated with the current count of the number of rows in the table or view.

Result Sets

DBCC UPDATEUSAGE returns (values may vary):

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Remarks

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.

Use DBCC UPDATEUSAGE 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. sp_spaceused accepts an optional parameter to run DBCC UPDATEUSAGE before returning space information for the table or index.

Upgrading Databases

In earlier versions of SQL Server, the values for the table and index row counts and page counts can become incorrect. Databases that were created on versions prior to SQL Server 2005 may contain incorrect counts. Therefore, we recommend running DBCC UPDATEUSAGE after upgrading to SQL Server 2005 to correct any invalid counts.

In SQL Server 2005, 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. Although it may appear as if upgrading the database to SQL Server 2005 caused this issue, the invalid counts existed before the upgrade procedure.

Permissions

Requires membership in the sysadmin fixed server role or the db_owner fixed database role.

Examples

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.

DBCC UPDATEUSAGE (0);
GO

B. Updating page or row counts or both for AdventureWorks, and suppressing informational messages

The following example specifies AdventureWorks as the database name and suppresses all informational messages.

USE AdventureWorks;
GO
DBCC UPDATEUSAGE (AdventureWorks) WITH NO_INFOMSGS; 
GO

C. Updating page or row counts or both for the Employee table

The following example reports updated page or row count information for the Employee table in the AdventureWorks database.

USE AdventureWorks;
GO
DBCC UPDATEUSAGE (AdventureWorks,"HumanResources.Employee");
GO

D. Updating page or row counts or both for a specific index in a table

The following example specifies IX_Employee_ManagerID as the index name.

USE AdventureWorks;
GO
DBCC UPDATEUSAGE (AdventureWorks, "HumanResources.Employee", IX_Employee_ManagerID);
GO

Share via