Subscribe to this blog
Briefcase
Print
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
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