Home > SQL server DBA administration > SQL Script to Update Usage for all databases and rectify inaccuracies in the catalog view

SQL Script to Update Usage for all databases and rectify inaccuracies in the catalog view


During production work we sometimes face a problem where when we verify the log space, it shows us as 3274MB. To rectify the same we have to run DBCC Update Usage for all databases to correct 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, these values are always maintained correctly. Databases created on SQL Server 2005 should never experience incorrect counts, however, databases upgraded to SQL Server 2005 and 2008 may contain invalid counts. It is recommended by Microsoft to run DBCC UPDATEUSAGE after upgrading to SQL Server 2005, 7, 2008 to correct any invalid counts.

Note: To rectify the same we need to run the below script at off business hours.

–SQL Script to Update Usage for all databases

declare @servername varchar(50), –variable to hold the servername

@dbname varchar(100), –variable to hold the database name

@command varchar(1000) –variable to hold the sql command

– set variables

select @servername=@@servername

– declare the cursor

declare dbccuu cursor for

select name from master.dbo.sysdatabases

where (status & 32 <> 32 and status & 128 <> 128 and status & 512 <> 512 and status & 1024 <> 1024 and status & 4096 <> 4096 and status & 2048 <> 2048) and (name not in (‘Northwind’, ‘Pubs’))

– open the cursor

open dbccuu

– fetch the first record into the cursor

fetch dbccuu into @dbname

– while the fetch was successful

while @@fetch_status=0

begin

– print the header for each database

print ”

print ‘***************************’

print ‘DBCC UPDATEUSAGE Report For ‘+@DBNAME

print ‘***************************’

print ”

– set the command to execute

set @command=’dbcc updateusage(‘+@dbname+’)’

– execute the command

exec(@command)

– fetch the next record into the cursor

fetch dbccuu into @dbname

end

– close the cursor

close dbccuu

– deallocate the cursor

deallocate dbccuu

– tell user when the script was last run

select ‘This script was executed on ‘ + cast(getdate() as varchar(50))

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: