Monday, April 13, 2015

Run DBCC UPDATEUSAGE on All Databases in an Instance

A few lines of code for those of us migrating SQL Server 2000 databases to a higher version of SQL Server and having CHECKDB fail because the counts are wrong. This simply runs DBCC UPDATEUSAGE on all the databases in the instance. I can't say it any better than BOL:

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.

-- This can take a while.... 
set nocount on
declare @Sql nvarchar(4000)
set @Sql = 'RAISERROR(''?'', 10, 1) WITH NOWAIT; DBCC UPDATEUSAGE ([?])'
exec sp_MSforeachdb @Sql

Interestingly, even for SQL Server 2014, BOL says this:

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.

1 comments:

Dazy said...

This post is very informative and interesting. Thanks for sharing with us.

Post a Comment