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 (
SELECT *, RN = ROW_NUMBER() OVER (PARTITION BY DBId ORDER BY RunDate DESC)
FROM DBCC_CHECKDB_HISTORY
WHERE Outcome IS NOT NULL
) AS dbcc_history
WHERE RN = 1

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?

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

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

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:

“Message
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?

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.

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
ALTER TABLE ##DBCC_OUTPUT ADD Outcome AS
CASE
WHEN Error = 8989 AND MessageText LIKE ‘%0 allocation errors and 0 consistency errors%’ THEN 0
WHEN Error 8989 THEN NULL
ELSE 1
END

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
End

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’%’)
End
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’%’)
End

— 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
CASE
WHEN Error = 8989 AND MessageText LIKE N”’ + @Msg + ”’ THEN 0
WHEN Error 8989 THEN NULL
ELSE 1
END’
exec (@sql)

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.

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?

Regards
Rouven