Automatically check results of DBCC CHECK DB

Question

Post reply

Automatically check results of DBCC CHECK DB

SteveBell

SSCrazy

Points: 2008
More actions
October 22, 2010 at 6:21 am

#228225

Hello all,
From a thread in the data corruption forum, I noted this advice:
I don't know any DBA who runs checkDB manually on all their databases or checks the results of checkDBs manually.
We want to run CHECK DB often enough so that we can restore from a non-corrupt backup if required. (I'm working on determining how often "often enough" is, but we can set that aside for the moment).
I'm sure that we can automate this in an integrity-check maintenance plan or a script of some kind; however, my question is related to the last part of the above quote - i.e. how would we automatically check the results of CHECK DB and automatically notify the DBA of an issue?
Any thoughts are much appreciated.
Steve

Viewing 15 posts - 1 through 15 (of 29 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

This is the script I'm using:

CREATE PROCEDURE [maint].[dba_runCHECKDB]

@dbName sysname = NULL,

@PHYSICAL_ONLY bit = 0,

@allMessages bit = 0

AS

BEGIN

IF OBJECT_ID('tempdb..#DBCC_OUTPUT') IS NOT NULL

DROP TABLE #DBCC_OUTPUT

CREATE TABLE #DBCC_OUTPUT(

Error int NOT NULL,

[Level] int NOT NULL,

State int NOT NULL,

MessageText nvarchar(256) NOT NULL,

RepairLevel int NULL,

Status int NOT NULL,

DbId int NOT NULL,

ObjectId int NOT NULL,

IndexId int NOT NULL,

PartitionId int NOT NULL,

AllocUnitId int NOT NULL,

[File] int NOT NULL,

Page int NOT NULL,

Slot int NOT NULL,

RefFile int NOT NULL,

RefPage int NOT NULL,

RefSlot int NOT NULL,

Allocation int NOT NULL

)

DECLARE c_databases CURSOR LOCAL FAST_FORWARD

FOR

SELECT Name

FROM master.sys.databases

WHERE Name = ISNULL(@dbName, Name)

OPEN c_databases

FETCH NEXT FROM c_databases INTO @dbName

WHILE @@FETCH_STATUS = 0

BEGIN

DECLARE @sql nvarchar(4000)

SET @sql = 'DBCC CHECKDB('+ @dbName +') WITH TABLERESULTS, ALL_ERRORMSGS'

IF @PHYSICAL_ONLY = 1

SET @sql = @sql + ', PHYSICAL_ONLY '

INSERT INTO #DBCC_OUTPUT

EXEC(@sql)

FETCH NEXT FROM c_databases INTO @dbName

END

CLOSE c_databases

DEALLOCATE c_databases

IF NOT EXISTS (

SELECT 1 FROM #DBCC_OUTPUT

)

BEGIN

RAISERROR('No database matches the name specified.',10,1)

END

SET @sql =

'SELECT DB_NAME(DbId) AS DatabaseName, ' +

CASE @allMessages

WHEN 1 THEN '*'

ELSE 'Outcome = CASE WHEN MessageText LIKE ''%0 allocation errors and 0 consistency errors%'' THEN 0 ELSE 1 END, MessageText '

END + '

FROM #DBCC_OUTPUT ' +

CASE @allMessages WHEN 1 THEN '' ELSE 'WHERE Error = 8989' END

EXEC(@sql)

END

To get a notification, you can set up a job that runs the sp as query for a dbmail.

-- Gianluca Sartori

Lowell SSC Guru Points: 323518 More actions · Answer 2

nice proc Gianluca; i added that to my snippets.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

spaghettidba

SSC Guru

Points: 105732

October 22, 2010 at 7:37 am

#1240910

Glad you liked it. 😉

-- Gianluca Sartori

SteveBell SSCrazy Points: 2008 More actions · Answer 4

Thank you for the responses and script.

Much appreciated,

Steve

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

SwayneBell (10/22/2010)
(I'm working on determining how often "often enough" is, but we can set that aside for the moment).

How long do you retain your backups for?

If you retain full and log backups for 2 days, you need to do checkDB at least once every 2 days. If you retain all full (diff if applicable) and logs for a week, you need to checkDB at least once a week.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SteveBell SSCrazy Points: 2008 More actions · Answer 6

Thank you Gail.

That's very helpful.

PS: I think I'll just add "thank you Gail" to my signature in this forum and save the typing.

spaghettidba SSC Guru Points: 105732 More actions · Answer 7

I posted a corrected version of this code on my blog:

http://spaghettidba.com/2011/11/28/email-alert-dbcc-checkdb/

I hope you find it useful.

-- Gianluca Sartori

SteveBell SSCrazy Points: 2008 More actions · Answer 8

SteveBell

SSCrazy

Points: 2008

December 19, 2011 at 5:34 am

#1423060

Much appreciated Gianluca.

Steve

brendan woulfe Hall of Fame Points: 3888 More actions · Answer 9

Gianluca, your script looks very similar to what I've been using the last couple of months. It works awesomely....

http://www.heydba.net/2011/05/get-dbcc-checkdb-output-tool-for-sql-server-2005/

spaghettidba SSC Guru Points: 105732 More actions · Answer 10

bwoulfe (12/19/2011)

Gianluca, your script looks very similar to what I've been using the last couple of months. It works awesomely....

http://www.heydba.net/2011/05/get-dbcc-checkdb-output-tool-for-sql-server-2005/%5B/quote%5D

The idea behind is very similar. We got to slightly different implementations though.

Nice code!

Thanks for sharing.

-- Gianluca Sartori

hmbtx SSCarpal Tunnel Points: 4664 More actions · Answer 11

bwoulfe

I downloaded the script from http://www.heydba.net/2011/05/get-dbcc-checkdb-output-tool-for-sql-server-2005/ and got it to work in SSMS.

I am running SBS 2011 with SQL Server 2008R2.

However, when I place the scipt in a SQL Server Agent Job, the job fails and I cannot determine why it failed.

Is there something that I need to add or change to the script in order for it to run as a job via Sql Server Agent?

Thanks,

Howard

spaghettidba SSC Guru Points: 105732 More actions · Answer 12

hmbtx (12/23/2011)
bwoulfe
I downloaded the script from http://www.heydba.net/2011/05/get-dbcc-checkdb-output-tool-for-sql-server-2005/ and got it to work in SSMS.
I am running SBS 2011 with SQL Server 2008R2.
However, when I place the scipt in a SQL Server Agent Job, the job fails and I cannot determine why it failed.
Is there something that I need to add or change to the script in order for it to run as a job via Sql Server Agent?
Thanks,
Howard

Howard, just out of curiosity, is there a particular reason why you decided to run bwoulfe's code instead of mine?

I'm looking for suggestions to improve my solutiion.

Thanks

Gianluca

-- Gianluca Sartori

brendan woulfe Hall of Fame Points: 3888 More actions · Answer 13

Howard, the script I posted was designed for SQL Server 2005. Not sure if it works on 2008. I haven't tested it on a SQL Server 2008 box. I just noticed the similarities between that one and the one Gianluca posted. You should probably use Gianluca's instead because it should work for what you're trying.

hmbtx SSCarpal Tunnel Points: 4664 More actions · Answer 14

Gianluca

The only reason that I decided to run bwoulfe's code instead of yours is that his already had the code to email the report.

You stated "To get a notification, you can set up a job that runs the sp as query for a dbmail" but I had never done that before and was not certain how to write the proper code."

You also said "I posted a corrected version of this code on my blog: http://spaghettidba.com/2011/11/28/email-alert-dbcc-checkdb/" however, when I went to that site I did not find a way to easily download the code. When I tried to cut and paste it, it also captured the line numbers which I would have had to remove by hand for each line.

Can you tell me an easy way to get your corrected version and also do you happen to have the proper code that I can use in order to email the report?

Any help will be appreciated.

Howard