DBCC UPDATEUSAGE (T-SQL)

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' [, index_id]
            ]
    )    [    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'

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

index_id

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

COUNT_ROWS

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

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 for Table 'sysobjects' (IndexId=2) updated:

RSVD Pages: Changed from (2) to (1) pages

DBCC UPDATEUSAGE: Sysindexes row for Table 'sysobjects' (IndexId=3) updated:

RSVD Pages: Changed from (2) to (1) pages

DBCC UPDATEUSAGE: Sysindexes row for Table 'syscolumns' (IndexId=2) updated:

USED Pages: Changed from (5) to (6) pages

DBCC UPDATEUSAGE: Sysindexes row for Table 'syscolumns' (IndexId=1) updated:

USED Pages: Changed from (18) to (17) pages

RSVD Pages: Changed from (22) to (21) pages

DBCC UPDATEUSAGE: Sysindexes row for Table 'systypes' (IndexId=2) updated:

RSVD Pages: Changed from (2) to (1) pages

DBCC UPDATEUSAGE: Sysindexes row for Table 'systypes' (IndexId=1) updated:

RSVD Pages: Changed from (6) to (3) pages

DBCC UPDATEUSAGE: Sysindexes row for Table 'sysusers' (IndexId=2) updated:

RSVD Pages: Changed from (2) to (1) pages

DBCC UPDATEUSAGE: Sysindexes row for Table 'sysfulltextcatalogs' (IndexId=2) updated:

RSVD Pages: Changed from (2) to (1) pages

DBCC UPDATEUSAGE: Sysindexes row for Table 'sysfilegroups' (IndexId=2) updated:

RSVD Pages: Changed from (2) to (1) pages

DBCC UPDATEUSAGE: Sysindexes row for Table 'Categories' (IndexId=2) updated:

RSVD Pages: Changed from (2) to (1) pages

DBCC UPDATEUSAGE: Sysindexes row for Table 'Categories' (IndexId=255) updated:

USED Pages: Changed from (13) to (18) pages

RSVD Pages: Changed from (18) to (17) pages

DBCC UPDATEUSAGE: Sysindexes row for Table 'Categories' (IndexId=1) updated:

USED Pages: Changed from (6) to (5) pages

RSVD Pages: Changed from (6) to (3) pages

DBCC UPDATEUSAGE: Sysindexes row for Table 'Customers' (IndexId=2) updated:

RSVD Pages: Changed from (2) to (1) pages

DBCC UPDATEUSAGE: Sysindexes row for Table 'Customers' (IndexId=3) updated:

RSVD Pages: Changed from (2) to (1) pages

DBCC UPDATEUSAGE: Sysindexes row for Table 'Customers' (IndexId=4) updated:

RSVD Pages: Changed from (2) to (1) pages

DBCC UPDATEUSAGE: Sysindexes row for Table 'Customers' (IndexId=5) updated:

RSVD Pages: Changed from (2) to (1) pages

DBCC UPDATEUSAGE: Sysindexes row for Table 'Customers' (IndexId=1) updated:

USED Pages: Changed from (14) to (13) pages

RSVD Pages: Changed from (14) to (8) pages

DBCC UPDATEUSAGE: Sysindexes row for Table 'Employees' (IndexId=2) updated:

RSVD Pages: Changed from (2) to (1) pages

DBCC UPDATEUSAGE: Sysindexes row for Table 'Employees' (IndexId=3) updated:

RSVD Pages: Changed from (2) to (1) pages

DBCC UPDATEUSAGE: Sysindexes row for Table 'Employees' (IndexId=255) updated:

USED Pages: Changed from (28) to (34) pages

RSVD Pages: Changed from (34) to (33) pages

DBCC UPDATEUSAGE: Sysindexes row for Table 'Employees' (IndexId=1) updated:

USED Pages: Changed from (8) to (7) pages

RSVD Pages: Changed from (8) to (4) pages

DBCC UPDATEUSAGE: Sysindexes row for Table 'Suppliers' (IndexId=2) updated:

RSVD Pages: Changed from (2) to (1) pages

DBCC UPDATEUSAGE: Sysindexes row for Table 'Suppliers' (IndexId=3) updated:

RSVD Pages: Changed from (2) to (1) pages

DBCC UPDATEUSAGE: Sysindexes row for Table 'Suppliers' (IndexId=255) updated:

USED Pages: Changed from (1) to (2) pages

RSVD Pages: Changed from (2) to (1) pages

DBCC UPDATEUSAGE: Sysindexes row for Table 'Suppliers' (IndexId=1) updated:

USED Pages: Changed from (8) to (7) pages

RSVD Pages: Changed from (8) to (4) pages

DBCC UPDATEUSAGE: Sysindexes row for Table 'Products' (IndexId=2) updated:

RSVD Pages: Changed from (2) to (1) pages

DBCC UPDATEUSAGE: Sysindexes row for Table 'Products' (IndexId=3) updated:

RSVD Pages: Changed from (2) to (1) pages

DBCC UPDATEUSAGE: Sysindexes row for Table 'Products' (IndexId=1) updated:

USED Pages: Changed from (9) to (8) pages

RSVD Pages: Changed from (9) to (5) pages

DBCC UPDATEUSAGE: Sysindexes row for Table 'Shippers' (IndexId=1) updated:

USED Pages: Changed from (4) to (3) pages

RSVD Pages: Changed from (4) to (2) pages

DBCC UPDATEUSAGE: Sysindexes row for Table 'Orders' (IndexId=2) updated:

USED Pages: Changed from (6) to (5) pages

RSVD Pages: Changed from (6) to (4) pages

DBCC UPDATEUSAGE: Sysindexes row for Table 'Orders' (IndexId=3) updated:

USED Pages: Changed from (5) to (4) pages

RSVD Pages: Changed from (5) to (3) pages

DBCC UPDATEUSAGE: Sysindexes row for Table 'Orders' (IndexId=4) updated:

USED Pages: Changed from (6) to (5) pages

RSVD Pages: Changed from (6) to (4) pages

DBCC UPDATEUSAGE: Sysindexes row for Table 'Orders' (IndexId=5) updated:

USED Pages: Changed from (6) to (5) pages

RSVD Pages: Changed from (6) to (4) pages

DBCC UPDATEUSAGE: Sysindexes row for Table 'Orders' (IndexId=6) updated:

USED Pages: Changed from (5) to (4) pages

RSVD Pages: Changed from (5) to (3) pages

DBCC UPDATEUSAGE: Sysindexes row for Table 'Orders' (IndexId=7) updated:

USED Pages: Changed from (6) to (5) pages

RSVD Pages: Changed from (6) to (4) pages

DBCC UPDATEUSAGE: Sysindexes row for Table 'Orders' (IndexId=0) updated:

DATA Pages: Changed from (21) to (22) pages

USED Pages: Changed from (55) to (50) pages

RSVD Pages: Changed from (59) to (46) pages

DBCC UPDATEUSAGE: Sysindexes row for Table 'Order Details' (IndexId=2) updated:

USED Pages: Changed from (7) to (6) pages

DBCC UPDATEUSAGE: Sysindexes row for Table 'Order Details' (IndexId=3) updated:

USED Pages: Changed from (7) to (6) pages

DBCC UPDATEUSAGE: Sysindexes row for Table 'Order Details' (IndexId=1) updated:

USED Pages: Changed from (27) to (24) pages

DBCC UPDATEUSAGE: Sysindexes row for Table 'SouthAmericanCustomers' (IndexId=0) updated:

DATA Pages: Changed from (2) to (3) pages

RSVD Pages: Changed from (10) to (9) pages

DBCC UPDATEUSAGE: Sysindexes row for Table 'CustomerResults' (IndexId=0) updated:

DATA Pages: Changed from (2) to (3) pages

RSVD Pages: Changed from (10) to (9) pages

DBCC UPDATEUSAGE: Sysindexes row for Table 'CustomersOne' (IndexId=0) updated:

DATA Pages: Changed from (2) to (3) pages

RSVD Pages: Changed from (10) to (9) pages

DBCC UPDATEUSAGE: Sysindexes row for Table 'CustomersTwo' (IndexId=0) updated:

DATA Pages: Changed from (2) to (3) pages

RSVD Pages: Changed from (10) to (9) pages

DBCC UPDATEUSAGE: Sysindexes row for Table 'CustomersThree' (IndexId=0) updated:

DATA Pages: Changed from (2) to (3) pages

RSVD Pages: Changed from (10) to (9) pages

DBCC UPDATEUSAGE: Sysindexes row for Table 'tbl1' (IndexId=0) updated:

DATA Pages: Changed from (1) to (2) pages

USED Pages: Changed from (1) to (2) pages

RSVD Pages: Changed from (2) to (1) 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 on the authors table.

DBCC UPDATEUSAGE ('pubs','authors')

GO

D. Update sysindexes for a specified index

This example obtains the index ID for the UPKCL_auidind index of the authors table and uses that index ID in the DBCC UPDATEUSAGE example.

-- Get the index ID.

DECLARE @indid int

SELECT @indid = indid

FROM sysindexes

WHERE id = object_id('authors')

    AND name = 'UPKCL_auidind'

DBCC UPDATEUSAGE ('pubs', 'authors', @indid)

GO

See Also