SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Alert to monitor DBCC CheckDB


Alert to monitor DBCC CheckDB

Author
Message
giles.clapham
giles.clapham
SSChasing Mays
SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)

Group: General Forum Members
Points: 615 Visits: 475
Hello,
I've configured an Alert to fire when DBCC CheckDB runs for a given Database by monitoring for EventID 8957, at that point it runs a predefined job.
I've tampered with using the keyword attribute but what I really want it to do is only run the job when it finds an error. If I had a damaged DB then I could monitor what got written to the Application Log but I haven't.
So my question is how can I monitor a DBCC CheckDB returning problems? is EventID 8957 only returned when everything is OK?

Declare @AlertName varchar(50);set @AlertName='Respond to CheckDB Failure'
EXEC msdb.dbo.sp_add_alert
@name=@AlertName,
@Database_Name='Example',
@Job_Name='JobName',
@message_ID=8957,--CheckDB message
@event_description_keyword ='DBCC'

Many thanks
Giles
Lowell
Lowell
SSC Guru
SSC Guru (123K reputation)SSC Guru (123K reputation)SSC Guru (123K reputation)SSC Guru (123K reputation)SSC Guru (123K reputation)SSC Guru (123K reputation)SSC Guru (123K reputation)SSC Guru (123K reputation)

Group: General Forum Members
Points: 123524 Visits: 41464
Gianluca Sartori here on SSC posted the code below in another thread, and i really like how well it works. this might help you with what you were after...it pretty much only emails errors/issues.

http://www.sqlservercentral.com/Forums/Topic1009121-146-1.aspx

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




--Proc from Gianluca Sartori @SSC
--add to a job to send results of sp as mail
--updated table definition based on real world experience from
--Bliar (jbhauser @SSC)
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 varchar(255) NULL,
Status int NOT NULL,
DbId int NOT NULL,
ObjectId int NOT NULL,
IndexId int NOT NULL,
PartitionId bigint NOT NULL,
AllocUnitId bigint 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



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!
giles.clapham
giles.clapham
SSChasing Mays
SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)

Group: General Forum Members
Points: 615 Visits: 475
Thank you very much, all I need to do now is corrupt a database to test it out!
Thanks again
Giles
giles.clapham
giles.clapham
SSChasing Mays
SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)

Group: General Forum Members
Points: 615 Visits: 475
Just a quick note to say I downloaded some sample corrupt databases from http://sqlskills.com/pastConferences.asp
jbhauser
jbhauser
SSC-Enthusiastic
SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)

Group: General Forum Members
Points: 180 Visits: 226
I am trying to utilize Gianluca's procedure (nice work by the way) against the corrupt databases found in the conference link above. I cannot get around the error "converting data type nvarchar to int". When I run the DBCC CheckDb statement by itself, there is an additional record returned that reads "repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECK DB...".

I believe this additional record is causing the insert statement to fail.

The statement I am running is "DBCC CHECKDB(Corrupt2008Demo) WITH TABLERESULTS, ALL_ERRORMSGS,NO_INFOMSGS". This is executed against a SQL 2008, Standard Edition database with service pack 2 installed.

Does anyone have any suggestions on how to prevent or suppress the informational record from being returned by the DBCC command?

Thanks for any help in advance.



Lowell
Lowell
SSC Guru
SSC Guru (123K reputation)SSC Guru (123K reputation)SSC Guru (123K reputation)SSC Guru (123K reputation)SSC Guru (123K reputation)SSC Guru (123K reputation)SSC Guru (123K reputation)SSC Guru (123K reputation)

Group: General Forum Members
Points: 123524 Visits: 41464
jbhauser (6/27/2011)
I am trying to utilize Gianluca's procedure (nice work by the way) against the corrupt databases found in the conference link above. I cannot get around the error "converting data type nvarchar to int". When I run the DBCC CheckDb statement by itself, there is an additional record returned that reads "repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECK DB...".

I believe this additional record is causing the insert statement to fail.

The statement I am running is "DBCC CHECKDB(Corrupt2008Demo) WITH TABLERESULTS, ALL_ERRORMSGS,NO_INFOMSGS". This is executed against a SQL 2008, Standard Edition database with service pack 2 installed.

Does anyone have any suggestions on how to prevent or suppress the informational record from being returned by the DBCC command?

Thanks for any help in advance.


ok definitely one of the columns in that temp table we created doesn't match the DBCC results, so you'll have to help out: when you get the error you identified when running DBCC all by itself,""repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECK DB...".", look at the row of data...i suspect the column "RepairLevel" is some text instead of NULL? change that temp table to have a varchar definition for the offending varchar column and let us know if that was the issue:


RepairLevel int NULL,
RepairLevel varchar(255) NULL,



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!
SQL Galaxy
SQL Galaxy
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11819 Visits: 3517
Hi,

This is very nice script for quickly get and clear output consistancy checking database for all database.

Can anyone help me, I want to know how ouput file attached or placed to email body?

I configured like at SQLJobs and output stroing in "C" drive (file name consistancy_check.txt)

Thanks
ananda
jbhauser
jbhauser
SSC-Enthusiastic
SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)

Group: General Forum Members
Points: 180 Visits: 226
Changing the RepairLevel to varchar led me to other errors (thanks for pointing me in the right direction Lowell). I changed the following table columns and am now able to execute the procedure without errors:

MessageText nvarchar(300) NOT NULL,
RepairLevel varchar(255) NULL,
...
...
PartitionId bigint NOT NULL,
AllocUnitId bigint NOT NULL,

Thanks for your help,
Blair



Lowell
Lowell
SSC Guru
SSC Guru (123K reputation)SSC Guru (123K reputation)SSC Guru (123K reputation)SSC Guru (123K reputation)SSC Guru (123K reputation)SSC Guru (123K reputation)SSC Guru (123K reputation)SSC Guru (123K reputation)

Group: General Forum Members
Points: 123524 Visits: 41464
Bliar thanks for posting what you found back; I updated my post, and PM'd Gianluca to make him aware of what you found;
I'm sure that this will help someone else out in the future.

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!
Gianluca Sartori
Gianluca Sartori
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38827 Visits: 13367
Sorry it took me 6 months... Blush
I posted a corrected version of this code on my blog:
http://spaghettidba.com/2011/11/28/email-alert-dbcc-checkdb/

I hope you will find it useful.

--Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search