Alert to monitor DBCC CheckDB

  • 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

  • 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!

  • Thank you very much, all I need to do now is corrupt a database to test it out!

    Thanks again

    Giles

  • Just a quick note to say I downloaded some sample corrupt databases from http://sqlskills.com/pastConferences.asp

  • 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.

  • 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!

  • 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

  • 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

  • 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!

  • 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

Viewing 10 posts - 1 through 9 (of 9 total)

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