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

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


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


Featured post

Creating a Linked Server to an Azure SQL Database

Why would you create a Linked Server to an Azure SQL Database? If you work in a hybrid environment with some databases stored on your on ...