Wednesday, 5 May 2010

SQL Server Integrity Checks – DBCC CHECKDB

I like to run integrity checks against my databases very regularly, sometimes daily if I can get away with it. For some of the larger databases that I manage I change how this is run but on my consolidated instances which can house several different smaller databases I run the following script in a job and schedule the job to run daily, out of hours, using the SQL Server agent. My script looks something like this:

--Gethyn Ellis  April 2009
--runs DBCC checkdb against ALL databases on a server
--Needs to be run against master database i.e. that is where sp_MSForEachdb is found
--To exclude a database add the database name to the NOT IN list in @cmd1
DECLARE @cmd1 VARCHAR(500)


SET @cmd1 = 'if ''?'' NOT IN (''tempdb'') DBCC CHECKDB([?]) WITH NO_INFOMSGS'
EXEC sp_MSforeachdb @command1 = @cmd1



2 comments:

  1. Cool usage of sp_MSForEachDB, when you want to operate with all DBs at once!

    ReplyDelete

Featured post

OUT OF SUPPORT: SQL Server 2008 and SQL Server 2008 R2

Welcome to this post, on SQL Server 2008 and 2008 R2, both of these versions of SQL server will go out of  extended support with Microsoft t...