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

SQL Server 2008 and SQL Server 2008 R2 - OUT of SUPPORT July 2019

Both SQL Server 2008 and SQL Server 2008 R2 go out of extended support with Microsoft in July 2019. That’s fast approaching and as we enter ...