Setting up an e-mail alert for DBCC CHECKDB errors

Some months ago I posted a script on a SQLServerCentral forum to help a member automating the execution of DBCC CHECKDB and send and e-mail alert in case a consistency error is found.

The original thread can be found here.

I noticed that many people are actually using that script and I also got some useful feedback on the code itself, so I decided to write this post to make an enhanced version available to everyone.

The Problem

Your primary responsibility as a DBA is to safeguard your data with backups. I mean intact backups! Keep in mind that when you back up a corrupt database, you will also restore a corrupt database.

A task that checks the database integrity should be part of your backup strategy and you should be notified immediately when corruption is found.

Unfortunately, the built-in consistency check Maintenance Task does not provide an alerting feature and you have to code it yourself.

The Solution

SQL Server 2000 and above accept the “WITH TABLERESULTS” option for most DBCC commands to output the messages as a result set. Those results can be saved to a table and processed to identify messages generated by corrupt data and raise an alert.

If you don’t know how to discover the resultset definition of DBCC CHECKDB WITH TABLERESULTS, I suggest that you take a look at this post.

Here is the complete code of the stored procedure I am using on my production databases:

Once the stored procedure is ready, you can run it against the desired databases:

EXEC [maint].[dba_runCHECKDB]
	@dbName        = 'model',
	@allmessages   = 0

Setting up an e-mail alert

In order to receive an e-mail alert, you can use a SQL Agent job and schedule this script to run every night, or whenever you find appropriate.

EXEC [maint].[dba_runCHECKDB]
    @dbName           = NULL,
    @PHYSICAL_ONLY    = 0,
    @allmessages      = 0,
    @dbmail_profile   = 'DBA_profile',
    @dbmail_recipient = ''

The e-mail message generated by the stored procedure contains the summary outcome and a detailed log, attached as a text file:

Logging to a table

If needed, you can save the output of this procedure to a history table that logs the outcome of DBCC CHECKDB in time:

-- Run the stored procedure with @log_to_table = 1
    @dbName        = NULL,
    @allMessages   = 0,
    @log_to_table  = 1

-- Query the latest results
) AS dbcc_history

When invoked with the @log_to_table parameter for the first time, the procedure creates a log table that will be used to store the results. Subsequent executions will append to the table.

No excuses!

The web is full of blogs, articles and forums on how to automate DBCC CHECKDB. If your data has any value to you, CHECKDB must be part of your maintenance strategy.

Run! Check the last time you performed a successful CHECKDB on your databases NOW! Was it last year? You may be in big trouble.

Posted on November 28, 2011, in SQL Server and tagged , , , , . Bookmark the permalink. 87 Comments.

  1. Just thought you’d like to see I changed that final query for a couple of reasons:
    1) It was only returning the row for the most recent DB, not the most recent for each DB.
    2) In the case of one check, the RunDate was not specific enough to get the last/best row.

    So here’s what I’m doing:
    SELECT *
    FROM (
    ) AS dbcc_history
    WHERE RN = 1

  2. IS there a way we can cut and paste this code easily, when I try cutting and pastng the formatting is all screwed up and it can take me an hour to fix the formatting.

    Or I am doing something wrong ?

  3. This is great information, thanks for the post. I’ve successfully been able to run and test this against 1 specific DB, however, whenever I try all with NULL I receive “Msg 50000, Level 16, State 1, Procedure dba_runCHECKDB, Line 387 – The current transaction cannot be committee and cannot support operations that write to the log file.”
    Any suggestions?

    • JC, thanks for reporting the error.
      It’s a strange error message, as the COMMIT/ROLLBACK commands are placed inside an IF block to handle doomed transactions.
      Does any of your databases report DBCC errors?
      Can you debug or trace the execution?
      What version of SQL Server are you running?


      • It turns out that the code didnt like our DBs with bad naming convention that started with numbers (even with quotes). I’ve since renamed and it seems to be working fine. Thanks again.

      • Ha! You’re right. The code did not account for illegal identifiers.
        Now I fixed it.
        Thanks again for reporting!

  4. Glad I could help. Is the ajusted code reflected above?

    • Yes, the code in this page has been fixed.
      Thanks again.

      • I have created and run this procedure on 9 different instances of 2005/2008 R2. On every one, it gives me the error “current transaction cannot be committee and cannot support operations that write to the log file”. Any ideas?

      • The error message suggests that you’re running into a ddl error, which results in a doomed transaction. I’m out of office now and can’t check the code. I recently updated the code for SQL server 2012 and the new code might be broken for the older versions.
        I’ll be back in a few hours. Sorry for the inconvenience.

      • The code has been fixed. Thanks for reporting!

  5. Thank you for this from a beginning DBA (who wants to be a Great DBA). How can I test for a corrupted DB.Is there a way I can make a testDB that is corrupted to check the entire process?

  6. One of the problems that occured is – if a corrupt DB is found, it does not store the results. I tried downloading a corrupt DB from SQLSkills and testing your code against it. Even the home grown script that I had did not work with the corrupt DB, so I was looking at the alternatives. Unless someone has eyes for date (checking it everyday), its hard detect problems. Maybe you want to work it out, or let me know if you need any help.

    • Weird. I tested the code with the “broken” database from SQLSkills and it works just fine for me.
      Can you post your exact SQLServer version and the exact errors you are getting?

  7. Gianlucca, I suggest that the code being used for checkdb be updated to also include running checkdb against tempdb. Tempdb can get corruption and the fix isn’t just to restart SQL Server (you have to delete the tempdb files). So it would be good to know if/when tempdb becomes corrupt.

    • Thanks Jason, that’s a great suggestion!
      I didn’t think about that, it makes perfect sense.
      I’ll update the script as soon as possible.
      Now I wonder how to notify all the people that may be using this script that it has to be fixed. I get quite a lot of hits for this post.

  8. Hi I am a fairly new dba. This script was a great help but i have encoutered an error testing it.
    I installed the corrupted DB’s from sqlskills. The DB’s are named DemoFatalCorruption1 and DemoFatalCorruption2. While running the script against #2 the DBCC check errors out with a “A severe error occurred on the current command. The results, if any, should be discarded.”

    It then does not send an email. Any suggestions?

    • Hi Dennis, the DemoFatalCorruption2 database contains fatal corruption that raises an error of level 20 and terminates DBCC and also terminates the connection.
      In this case, you should set up a notification for failed jobs and be notified when the job fails.
      Thanks for pointing it out. I will code a fix and post it here.

  9. Thanks – but there’s a comma missing in the “Setting up an e-mail alert” section…. @dbmail_profile = ‘DBA_profile’
    @dbmail_recipient = ‘’

    Also, I am using the “broken’ db to generate results to be emailed to me but nothing..
    I have other job results that are sent to me via email but I can’t get yours to work.
    I am using a job to send the email like you explained but it seems like I am missing something. Can you explain that section in just a little more detail? Thanks!

    • Thanks for reporting the syntax error, I fixed the code now.
      My test instances contain the “broken” database from sqlskills and it works for me. I’m using both SQL Server 2008R2 SP2 and SQL Server 2012 SP1.
      What version are you using? Are you getting any errors?
      You can drop me an email if you want.
      My address is spaghettidba AT

      • I had this long drafted email waiting to send you with screen shots and when a revelation came to me…
        The @dbmail_profile that I was using was totally wrong – Here I thought that it meant to use an operator profile – nope it was as easy as using my dbmail profile name and viola…it worked!!!
        By the way – the script is cool – and I like that you suggested to create a “Tools” database to be used for maintenance scripts and such…
        Again THANKS – cool stuff

  10. This is not working for me because I don’t have TOOLS.dbo.sp_send_cdosysmail. Looking at the Microsoft link, it looks like this is a workaround for Windows 2000 not having a MAPI subsystem. But I am using Windows 7 (test system) and SQL 2008 R2. When I added the procedure and ran the test command, I got numerious errors reading “SQL Server blocked access to procedure ‘sys.sp_OASetProperty’ of component ‘Ole Automation Procedures’ because this component is turned off as part of the security configuration for this server.”

    Shouldn’t this procedure with with Database Mail XPs enabled without having to open additional potential security holes?

    • Don’t know exactly what’s happening here. For some reason your SQL server version is not recognized correctly. Try running the version recognition code and see what’s wrong there.

      • Turns out that the test command from the Microsoft page doesn’t matter. After creating sp_send_cdosysmail I ran dba_runCHECKDB against the demo currupt database from SQL skills nothing happened – until I checked my Outlook junk email folder and found the messages there. After changing my spam settings it is working correctly – thanks!

      • Great! Glad you sorted it out.
        I’m not sure I understand what you mean with “Turns out that the test command from the Microsoft page doesn’t matter.”

  11. Does this work on a Log shipped Database ? I created the sproc and executed it on the job with Log Ship Read-Only Database and found that no CHECKDB ran against the database. Have anyone tried this on a Log shipping Standby database ? Appreciate your suggestions..

  12. Works great now – just one suggestion. How about an option to use the default e-mail profile instead of having to specify one. Then it could be deployed enterprise-wide without having to worry about inconsistent e-mail profile names.

  13. Hi Gianluca, Great script and thanks a lot for your work and sharing it. I’m running this as a task in a SSIS package and want the next task (backing up databases) to execute only if your DBCC CHECKDB outputs no errors. I have a feeling in SSIS the next task will execute as long as your stored procedure runs to completion (errors or none). Should I alter your your script to return a value that is TRUE for any errors and test for this in SSIS? Thanks for any advice as this is new to me. Best wishes, David

  14. Hi Gianluca, Thank you for the nice script.
    I want to make one more suggestion, Its not critical but would be nice to have
    in the subject line of email alert, include the server (host) name.

    DECLARE @subject varchar(256)
    SET @subject = ‘Host: ‘+@@servername+’ – Consistency errors found!’

    that way if we have more than 1 server (in our case we have about 30 sql servers) in production, then its easy to find out which server has issues.

    • Thanks for the suggestion. Usually I create the dbmail profile with a fakemail account that mimicks the server name such as server.instance@mydomain and I never thought of adding that piece of info in the subject for this reason.
      However I agree it’s a sensible improvement and I will try to code it as soon as I can.

    • I always put the server name in the “Display Name” field in the mail account. Then you know for every alert regardless of what generated it.

  15. I have Run_CHECKDB running on several instances running successfully for several weeks now. Yesterday morning, I see that on one server it has failed with an interesting error:

    Executed as user: \. The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction. [SQLSTATE 42000] (Error 50000) DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Error 2528). The step failed.”

    The failure was 29 seconds into the job, which normally takes about 11 minutes to run checkDB on 28 databases. No email was sent. Is the Log file referred to TOOLS.ldf or one of the other DB’s log files? I thought there might be a full hard drive but the drive TOOLS.ldf is on has more then 40GB free, and other drives with transaction log files have 100s of GB free.

    A few hours later when I got to the office I ran the RunCheckDB command manually and the job completed successfully in the normal time. This morning the job ran successfully as well. What do you make of this?

  16. Hi and tx for a brilliant piece of work. Can you please add an option to always send mail? This way we have a check if it ran at all.

  17. I second Dennis’ suggestion. Add a notification email to a sqlagent operator and you should be fine.

    • I added the ability to exclude databases for my application of it. I had a database that was close to a terabyte. It was killing the process of doing the rest.

      • Thanks Dennis. You may have noticed that I changed the code some time ago to add the ability to include or exclude a particular database from the check. If you want to skip a database, you can simply use the ^ char in front of its name.

  18. I have created database email profile and I am getting email that job ran fine but I am not getting any logs attachments. Can some helP?

  19. another question I have is does this log file need to be save on a local disk?

  20. Ramanujam Paravastu

    I have visited this site for the first time. I am new to SQL Server. I have taken the code and tried to compile on y 2005 server after creating a tools database. I have the following error when compiling the stored procedure:

    Msg 156, Level 15, State 1, Procedure dba_runCHECKDB, Line 26
    Incorrect syntax near the keyword ‘VALUES’.

    Is there something that I am doing wrong or was there something that I am missing? Please guide.


    • Hi Ram,
      You’re right: the code could not work on 2005 because of the VALUES in the help section. I changed it to use UNION ALL instead. See if it helps and thank you for pointing it out.

  21. Ramanujam Paravastu

    I Thank You for the quick reply and updating the code. Sorry to bother you once again, I have encountered 2 more errors which are as follows:

    Msg 102, Level 15, State 1, Procedure dba_runCHECKDB, Line 184
    Incorrect syntax near ‘<'.
    Msg 102, Level 15, State 1, Procedure dba_runCHECKDB, Line 394
    Incorrect syntax near '<'.

    The code lines are follows respective to the errors:

    FROM master.sys.databases

    EXEC msdb.dbo.sp_send_dbmail

    Can you please let me know what I am doing is wrong?


  22. Ramanujam Paravastu

    I am sure I copied the code correctly. Selected the code from this web page and copied to a notepad just to make sure there are no control characters coming through. I compiled and I get the same errors.
    Are there any settings that need to done for the tools database. I just created a blank database called TOOLS and executed the code provided.

    Is there a possibility of e-mailing me the code as an attachment in text format?


    • Turns out it was Internet Explorer messing with the code block and adding some tags that should have been hidden instead. I use Chrome normally and it never happened to mangle the code.

  23. Hi there…

    This is a useful script. Out of the box it runs fine. I did run into two issues when trying to either email myself or log the job to a table. I’m using SQL 2008R2 Latin General Bin collation.
    For the mail I have updated the stored proc with email profile and recipient (email works fine using the test email feature in sql). I updated the job to use the same profile and recipient. However no email is generated.

    For the log once I have configured the stored proc and sql job it fails with “Invalid column name ‘RowId'”

    Also can you procedure both email and log to a table together?
    Appreciate insight.

    • Hi Peter,

      I’m sorry the script didn’t work for you. I just tried to both log to table and send the notification email and it worked flawlessly. I don’t know where the error you are seeing comes from.
      Regarding the email, you will receive a message only when a consistency error is found. You can change that behaviour in the code if you want.


      • Jeff Stanforth

        Hi Gianluca,

        Fantastic script and works like a charm for me in SQL Server 2012. However, in SQL Server 2008 R2 I am getting the same issue as Peter D. My collation is SQL_Latin1_General_CP850_BIN2 if you have the opportunity to test. In the 2012 build it is the default collation.

    • Jeff Stanforth

      Hi GianLuca,

      You will see that I replied earlier mentioning that I ran in a particular collation (SQL_Latin1_General_CP850_BIN2) and ran into the same issue as Peter. Please feel free to remove as I have discovered the issue.

      @Peter D,

      Perhaps you have discovered the same as myself by now –> Some collations are, in fact, CaSe Sensitive.


      Your script is awesome and to say you need to tighten it up a little bit would be an insult.

      @Et al,

      For those of you using a non-default collation that run into this issue, please take some time to make sure all the cases are the same. You will see there are a couple items whose case does match the whole script: @RowID and @allMessages do come a couple iterations.

  24. It’s in point of fact a great and helpful piece of information. I’m satisfied that you simply shared this helpful information with us.

    Please stay us informed like this. Thank you for

  25. Hello Gianluca
    Thank you for this greate piece of work; it is exactly was I was looking for,

    However, I have a couple of issues with the computed column “Outcome” in table ##DBCC_OUTPUT.

    First, the expression

    MessageText LIKE ‘%0 allocation errors and’ — etc

    is also true for ’10 allocation errors’ — etc, or ‘130 allocation errors’ — etc. Thus it should be

    MessageText LIKE ‘% 0 allocation errors and’ — etc

    with a blank between the %-wildcard and the 0.

    Second, this is language dependent. If you set the language to german, Outcome will always be 1 because the message returned from DBCC then is

    Von CHECKDB wurden 0 Zuordnungsfehler und 0 Konsistenzfehler in der XY-Datenbank gefunden.

    Below I propose a solution which works on SQL Server 2010 (version 11.0.5058). I have not tested it on other versions.

    With kind regards
    Matthias Kläy
    Kläy Computing AG

    Solve the language dependency issue with the Outcome computed column in table ##DBCC_OUTPUT: Replace the original lines

    — Add a computed column
    WHEN Error = 8989 AND MessageText LIKE ‘%0 allocation errors and 0 consistency errors%’ THEN 0
    WHEN Error 8989 THEN NULL
    ELSE 1

    with the following

    — Add a computed column
    Declare @Msg nvarchar(4000)
    Declare @MsgLang int

    — First find message language number (LCID) from sys.syslanguages
    Select @MsgLang = msglangid From sys.syslanguages Where langid = @@LANGID

    — Next find corresponding text in sys.messages table
    Select @Msg = sys.messages.text From sys.messages
    Where sys.messages.message_id = 8989
    And sys.messages.language_id = @MsgLang

    — Not all languages have an entry in the sys.messages table. In this case, the message is returned in english (LCID = 1033)
    If @Msg Is Null Begin
    Set @MsgLang = 1033
    Select @Msg = sys.messages.text From sys.messages
    Where sys.messages.message_id = 8989
    And sys.messages.language_id = @MsgLang

    If @MsgLang = 1033 Begin — english has different structure of the message
    Set @Msg = Replace(@Msg, N’%.*ls’, N’CHECKDB’)
    Set @Msg = Replace(@Msg, N’%d’, N’0′)
    Set @Msg = Replace(@Msg, N’%ls’, N’%’)
    Else Begin
    Set @Msg = Replace(@Msg, N’%1!’, N’CHECKDB’)
    Set @Msg = Replace(@Msg, N’%2!’, N’0′)
    Set @Msg = Replace(@Msg, N’%3!’, N’0′)
    Set @Msg = Replace(@Msg, N’%4!’, N’%’)

    — The message may contain quotes, thus
    Set @Msg = Replace(@Msg, N””, N”””)

    — now we have the message string where the database name is replaced by % for the Like-Search
    — everything else must match

    Set @sql = N’ALTER TABLE ##DBCC_OUTPUT ADD Outcome AS
    WHEN Error = 8989 AND MessageText LIKE N”’ + @Msg + ”’ THEN 0
    WHEN Error 8989 THEN NULL
    ELSE 1
    exec (@sql)

    • Matthias, thanks for your comment. You make really good points and I will address the issues as soon as possible. Thanks a lot for the valuable feedback!

      • I solved the issue pointed out by Matthias by adding a new variable

        DECLARE @language SYSNAME;

        just after the other variables’ declarations, saving the current language and switching to us_english adding the following code

        SELECT @language = @@LANGUAGE;
        SET LANGUAGE ‘us_english’;

        just after line #209, which reads

        FETCH NEXT FROM c_databases INTO @dbName

        and reverting back to the original language by adding

        SET LANGUAGE @language

        just after line #290, which ends the “WHILE @@FETCH_STATUS = 0” loop.

        I also added the extra space before the first 0 while declaring the Outcome column calculation.

        Thanks for the script, Gianluca!

  26. Hello,
    I would like to start the procedure for certain bases.
    how to do ?
    this script does not work:
    EXEC TOOLS.maint.dba_runCHECKDB
    dbname = basic 1, base2, Base3
    allMessages = 0,
    log_to_table = 1

    Thank you

  27. Nice tool!. Our SQL instance has many databases, and our weekly integrity check may take a very long time (e.g. 40 hours). Any chance to add the associated started time and finished time in the table maint.DBCC_CHECKDB_HISTORY. This may help to identify the bottlenecks.

  28. Hey – great script! I am trying to get this to run for a LinkedServer and having little success!
    I know when you put a NULL entry in for the @dbName it does all databases on the server – however this is not working for LinkedServers!

    So…what I did was do to wrap the SPROC and send each @dbname in the below script:

    –All Databases CHECK DBCCDB
    DECLARE @databaseList as CURSOR;
    DECLARE @databaseName as NVARCHAR(500);
    DECLARE @tsql AS NVARCHAR(500);

    FROM [LINKEDSERVERNAME].master.sys.databases
    WHERE [state] = 0
    AND [is_read_only] = 0;

    OPEN @databaseList;
    FETCH NEXT FROM @databaseList into @databaseName;
    EXEC [tools].[maint].[dba_runCHECKDB]
    @dbName = @databaseName,
    @allmessages = 0,
    @log_to_table = 1
    FETCH NEXT FROM @databaseList into @databaseName;
    CLOSE @databaseList;
    DEALLOCATE @databaseList;

    — Query the latest results
    FROM [tools].[maint].[DBCC_CHECKDB_HISTORY]
    ) AS dbcc_history
    WHERE RN = 1

    This works – but only on the first database that it returns!
    The message I get back for all the other databases on the LinkedServer is:

    “No database matches the name specified”

    A debug is showing that the @dbname is working – but it errors on the ##DBCC

    Are you able to help in getting this to work with LinkedServers?


    • Your code seems to iterate databases on the remote server, then execute the checkdb procedure on the local server. However, I would simply invoke the procedure on the remote server instead.

      Hope this helps

      • Hi – that is exactly what it is trying to do . The reason for that is that our Infrastructure mostly has SQL Express (2016) instillation which do not have a SQL Agent to schedule the maintenance tasks for.

        My idea was to create a Maintenance Instance from a SQL Server Standard instillation and create LinkedServers from that Instance to create a Maintenance Plan against them and for it to iterate through each LinkedServer to run the maintenance. Single SQL Agent to then provide the dbmail alerts if failed/error.

        Without setting up Scheduled Tasks to execute the code on each SQL Express server I am a little stuck for ideas as to how to complete some maintenance scripts without losing hours doing it manually each day!

      • I suggest that you either try one of the SQL Agent replacements or use automation tools like DBATOOLS —

      • OK great, thanks for the reply – appreciate it and your time on this project 🙂

  29. Hi,
    first thanks for that great script! 8)
    It works fine on most of our SQL servers, but on some i didn`t get a email.
    We have about 20 MS SQL express servers,in all versions from 2005 to 2014.

    Sending a test email with EXEC msdb.dbo.sp_send_dbmail works fine, but the script dosen`t send a email on some servers.

    If i run the script on a cmd with sqlcmd, the output is diffrent.
    On servers that send a mail, it`s like this:
    CHECKDB found 0 allocation errors and 0 consistency errors in database ‘XY’

    On servers that dosen`t send a mail, it`s like this:
    DBCC execution completed. If DBCC printed error messages, contact your system administrator

    Any suggestions?


  30. hmm on 12 of our servers i get a daily mail from a scheduled task, on 8 servers not. All Servers run the same script with the same parameters.

  31. Received the same error as someone previously on one database out of eight.

    Msg 50000, Level 16, State 1, Procedure dba_runCHECKDB, Line 437 [Batch Start Line 33]
    The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

    Removed XACT_ABORT then received the emailed error

    Unable to run DBCC on database [databasename]: Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be locked.

    When I try to create a db snapshot manually:

    CREATE DATABASE dbname_dbss1800 ON
    ( NAME = dbname, FILENAME =
    ‘N:\test\’ )
    AS SNAPSHOT OF dbname;
    DROP DATABASE dbname_dbss180

    I get error
    CREATE FILE encountered operating system error 5(Access is denied.)

    After granting Network Service (account sql server runs under as default) modify permission to the database data file location, this is resolved.

  1. Pingback: dba_runCHECKDB v2(012) | spaghettidba

  2. Pingback: dennis sartoriFind 123 | Find 123

Leave a Reply to spaghettidba Cancel reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: