Automate DBCC CHECKDB scan

Update 10/30/2014 – I wrote this original article a long long long time ago. Geez its terrible. If you are not using Ola scripts for this kind of stuff you should check it out. https://ola.hallengren.com/

Original Article

Nothing like that sinking feeling when you have corruption in a production database. Regardless if you have a bullet proof backup strategy, database corruption can cause data loss if corruption goes undetected. One of the best ways to combat corruption is to know about the db corruption ASAP.

This article will not discuss the nitty gritty of DBCC CHECKDB or database corruption. That may be in a future article. Below are the steps I take to monitor Database corruption. This solution is not very elaborate but gets the job done. Let me know how to make it better.

First create a job on the server you wish to run the CheckDB scan on using this script:

Set Nocount on
Declare @dbname varchar(100)

Declare db Cursor For    –Cursor that holds the names of the databases without Pubs and Northwind
Select name from master.dbo.sysdatabases

Declare @osql varchar(1000)

Open db
Fetch Next from db into @dbname
While @@Fetch_status=0
Begin
Set @osql=’EXEC master.dbo.xp_cmdshell ‘+””+’osql -E -Q”DBCC Checkdb (“‘+@dbname+'”)” -oE:\scripts\checkdb\logs\’+@dbname+’.log’+””
EXEC (@osql) –Execute the osql statement
Fetch Next from db into @dbname
End
Close db

Deallocate db

Make sure you change the location of the log files to the location you will parse in the VBScript step below. Change this: E:\scripts\checkdb\logs\ to where you want the DBCC logs to be dumped.

Next you will make a Scheduled task to execute this VBScript.

‘Change these to work in your environment
logLocation = “E:\scripts\checkdb\logs”
attachLocation = “E:\scripts\checkdb\”
fromAddress = “YOURSERVER@mail.com”
emailServer = “mail.whatever.com”
youremail = “dude@whatever.com”

set fso = CreateObject(“Scripting.FileSystemObject”)
set outputLog = fso.OpenTextFile(“checkDBScan.log”,2,True)
FailFlag = 0

Set f = fso.GetFolder(logLocation)
Set fc = f.Files
if fc.Count = 0 then
Call SendCDOEmail(“**** WARNING **** CheckDB No Logs”, “No CheckDB logs are there. This may be a problem.”, youremail)
Wscript.Quit
End if

For Each f1 in fc
if f1.DateLastModified < Now – 1 then
Call SendCDOEmail(“**** WARNING **** Log Date Problem!!!”, “The dates did not check out on the CHECKDB Script. Connect to the server and find out if the SQL job failed.”, youremail)
WScript.Quit
end if

Next
‘——————————————————-
‘–        PARSE and LOOP
‘–
‘–
‘–     Following for statement will open each checkdb
‘–        log and parse the contents. Looking for 8000 msgs
‘–        if we find an 8000 message we will set the fail
‘–        flag to 1 indicating we have some corruption
‘–         somewhere
‘–
‘–
‘——————————————————
Set f = fso.GetFolder(logLocation)
Set fc = f.Files

For Each f1 in fc

set logfile = fso.OpenTextFile(f1.path, 1,True)

while not logfile.AtEndOfStream

lineread = logfile.ReadLine

if left(lineread,5) = “Msg 8” then
FailFlag = 1
outputLog.WriteLine(Now)
outputLog.WriteLine(“We have a serious problem here:” )
outputLog.WriteLine(”      ” & f1.Name & “::::   ” &  lineread)
lineread = logfile.Readline
outputLog.WriteLine(”      ” & f1.Name & “::::   ” &  lineread)
lineread = logfile.Readline
outputLog.WriteLine(”      ” & f1.Name & “::::   ” &  lineread)
lineread = logfile.Readline
outputLog.WriteLine(”      ” & f1.Name & “::::   ” &  lineread)
outputLog.WriteLine
outputLog.WriteLine

end if
wend

logfile.Close
Next

outputLog.Close

if FailFlag = 1 then
Call SendCDOEmailAttach(“***** FAILURE ******* CHECK DB Problem!!!”, “The attached log file details a serious CHECKDB error. Connect to the server and investigate ASAP.”, youremail, attachLocation & “checkDBScan.log”)
end if

‘——————————————————-
‘–        EMAIL Sub
‘–
‘–
‘–     This subroutine will send the email. Takes in some
‘–        parameters to get the job done.
‘–
‘–
‘——————————————————

Sub SendCDOEmailAttach(sSubject, sMessage, sToAddress,sAttachment)

Set myMail=CreateObject(“CDO.Message”)
myMail.Subject=sSubject
myMail.From=fromAddress
myMail.To=sToAddress
myMail.TextBody=sMessage
myMail.AddAttachment sAttachment
myMail.Configuration.Fields.Item (“http://schemas.microsoft.com/cdo/configuration/sendusing&#8221;)=2
‘Name or IP of remote SMTP server
myMail.Configuration.Fields.Item (“http://schemas.microsoft.com/cdo/configuration/smtpserver&#8221;)=emailServer
‘Server port
myMail.Configuration.Fields.Item (“http://schemas.microsoft.com/cdo/configuration/smtpserverport&#8221;) =25
myMail.Configuration.Fields.Update
myMail.Send
set myMail=nothing

End Sub

Sub SendCDOEmail(sSubject, sMessage, sToAddress)

Set myMail=CreateObject(“CDO.Message”)
myMail.Subject=sSubject
myMail.From=fromAddress
myMail.To=sToAddress
myMail.TextBody=sMessage
myMail.Configuration.Fields.Item (“http://schemas.microsoft.com/cdo/configuration/sendusing&#8221;)=2
‘Name or IP of remote SMTP server
myMail.Configuration.Fields.Item (“http://schemas.microsoft.com/cdo/configuration/smtpserver&#8221;)=emailServer
‘Server port
myMail.Configuration.Fields.Item (“http://schemas.microsoft.com/cdo/configuration/smtpserverport&#8221;) =25
myMail.Configuration.Fields.Update
myMail.Send
set myMail=nothing

End Sub

At the top of the script there are a list of variables. You will need to change these to fit your needs. Make sure you run the Scheduled task after the DBCC CHECKDB job has finished. The script will parse the CHECKDB logs looking for “8000 Messages”. The Error code 8XXX indicates an error was found in the CHECKDB. The script will then fire off an email to let you know there is an issue.

Here is a sample Corrupted database you can play with: broken (rename this to a .bak or whatever). Just restore this .bak file to a SQL instance and run a CHECKDB against it. You will see some 8000 error messages. You can point the above VBScript to the DBCC CHECKB log and then test to make sure the script is working and properly notifies you.

Again the best tool in combating  corruption is to know about it as quickly as possible. I have read articles that say running a CHECKDB daily is excessive. However, say you have database corruption on Monday, you do not run the CHECKDB job until SUNDAY. You find out Sunday you have DB Corruption. Depending on the corruption, if your only option is to restore the db, you may lose data for the entire week because you have backed up a corrupt database all week.

If you are running a CHECKDB weekly, then at least do yourself a favor and make sure and check the logs each week or setup some kind of script to parse the logs to let you know if errors were encountered. Speaking from experience, I had a production database go corrupt. I did not know about it for 15 days. By that time we did not have a good backup to restore to (tape rotations), so I had to run a DBCC CHECKDB WITH REPAIR_ALLOW_DATA_LOSS…yeah that is as bad as it sounds. Fortunately the DB was repaired (took 12 hours of downtime).

Some Other CHECKDB Resources:

Advertisements