One important tool in managing disk space is to make sure the system accurately reports space used by tables.  DBCC UPDATEUSAGE will update the values returned by sp_spaceused.

The output of sp_spaceused “displays the number of rows, disk space reserved, and disk space used by a table, indexed view, or Service Broker queue in the current database, or displays the disk space reserved and used by the whole database.”  One thing to note when reviewing the output is you may see the the reserved size is disportionately larger than the data and index space used. The reserved space should always be greater, but in one case I did see > 20 GB of difference. The space reported by system can get out of whack (technical term) from repeated use of TRUNCATE statements and frequent DDL statements.

Excellent article on DBCC UPDATEUSAGE by Armando Prato.

Google search for DBCC UPDATEUSAGE.

Best Practices from BOL

  • Always run DBCC UPDATEUSAGE after upgrading a database from SQL Server 2000. The page and row counts are corrected and are maintained thereafter.
  • Do not run DBCC UPDATEUSAGE routinely for databases created in SQL Server 2005 or higher or on upgraded databases that have been corrected once by using DBCC UPDATEUSAGE. Because DBCC UPDATEUSAGE can take some time to run on large tables or databases, it should not be used only unless you suspect incorrect values are being returned by sp_spaceused.
  • Consider running DBCC UPDATEUSAGE routinely (for example, weekly) only if the database undergoes frequent Data Definition Language (DDL) modifications, such as CREATE, ALTER, or DROP statements.

I use the below script to script out DBCC UPDATEUSAGE commands for all the databases in a SQL instance.  The script runs the DBBC command on each table individually which includes indexes, too. You can pass th the database name, a table name, view name or index name.

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

Make sure you script the output to text.

A few of To-Do’s  – 1.) Exclude databases offline. 2.) Exclude database snapshots.3.)Include views.

-- JackDonnell.com

USE '+name+';
PRINT '''+name+'--------------------------------->>>>>'';
Execute sp_MSforEachTable ''DBCC UPDATEUSAGE (['+name+'],[?]);''
WHERE name NOT in ('tempdb','msdb','master','model');

Tiny URL for this post:

Share the joy

Comments are closed.