Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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



 


Comments

Posted by Anonymous on 5 May 2010

Pingback from  Twitter Trackbacks for                 SQL Server Central, SQL Server Integrity Checks ??? DBCC CHECKDB - The SQL DBA in the UK         [sqlservercentral.com]        on Topsy.com

Posted by Dukagjin Maloku on 6 May 2010

Seems not working ...two same brackets [ ? [ ..., so the correction is:

DECLARE @cmd1 VARCHAR(500)

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

EXEC sp_MSforeachdb @command1 = @cmd1

Posted by Gethyn Ellis on 6 May 2010

Dukagjin

You are right, not sure how that got in there I have used this on many instances I guess i may have made a typo copying and pasting.

Posted by Steve Jones on 6 May 2010

Interesting. Is this the same as the maintenance plans? I'm just wondering if there's an error, does it stop checking the rest of the databases? Are all the errors/results dropped into one result set? I'd like to know how you process the results in another post.

Posted by ThomasLL on 6 May 2010

How do you handle errors returned from CheckDB? What happens when one fails? Does the loop stop?

Thomas

Posted by Dukagjin Maloku on 6 May 2010

Gethyn Ellis,

I noticed you to edit the post and to make the correction!!

Steve,

Encouragement for the posts in the series...it's nice challenge!

Posted by Gethyn Ellis on 6 May 2010

It was definatly a typo i think i hit a few keys whith my fat fingers when I pasted and thought I'd corrected it but obviuolsy I didn't and i didn't retest, my bad sorry

Dukagjin I did amend on my blog, the syndication didn't pick it up that quickly so i have amended manually.

Steve, I'm not sure if it is the same as the maint plan, i guess I need to check. I wrote this to overcome  an issue with SQL 2005 SP2 where the maint plan for some reason didn't do all the databases and run the integrity check, it got fixed in later SPs but my job stayed.

Thomas, this runs  in job and it does report as failed if it finds a corrupt DB. I tested using Paul Randal's corrupt DB (Taken from his blog) and it did check all DB's. I then realsied that I may have overlooked something, it loops through the databases according to their DBID starting at 1 and the corrupt DB was last to be done. So i created another database (which had a higher database id) and re-ran the job. The job as expected failed BUT it does carryon to the next database. ALL databases were checked.

I have had CHECK DB fail on me a couple of times, once becuase of an IO subsystrems failure...recovered with a restore from a good backup and another when I hit a known bug with XML indexes. Probably need another post to answer all the questions this one raised. again sorry for the typo

Posted by Anonymous on 6 May 2010

Pingback from  SQL Server Integrity Checks ??? DBCC CHECKDB – SQL Server Central - sql

Leave a Comment

Please register or log in to leave a comment.