Automatically check results of DBCC CHECK DB

  • SteveBell

    SSCommitted

    Points: 1948

    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

  • spaghettidba

    SSC Guru

    Points: 105661

    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.

  • Lowell

    SSC Guru

    Points: 323321

    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: 105661

    Glad you liked it. 😉

  • SteveBell

    SSCommitted

    Points: 1948

    Thank you for the responses and script.

    Much appreciated,

    Steve

  • Gail Shaw

    SSC Guru

    Points: 1004424

    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

    SSCommitted

    Points: 1948

    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: 105661

    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.

  • SteveBell

    SSCommitted

    Points: 1948

    Much appreciated Gianluca.

    Steve

  • brendan woulfe

    Hall of Fame

    Points: 3888

    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: 105661

    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.

  • hmbtx

    SSCarpal Tunnel

    Points: 4664

    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: 105661

    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

  • brendan woulfe

    Hall of Fame

    Points: 3888

    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

    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

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

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