question

Pinkett avatar image
Pinkett asked

DBCC Checkdb

I am required to build a report to present DBCC Checkdb on all sql instances. DBCC Checkdb is scheduled as maintenance plans. I need a script that will assist me in presenting the following information: Server Name, Instance Name, Database Name,Last_DBCC_Checkdb_Run, Failed\Succeeded, Output-message as well as setup database mail to e-mail any output with errors found.
dbcc
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

David Wimbush avatar image
David Wimbush answered
Have a look at Ola Hallengren's excellent database maintenance solution ([ http://ola.hallengren.com][1]). It installs a number of SQL Agent jobs that log execution and results. It's easy to configure which databases to check. If you don't want one of the components you just disable that job. The work is all done via stored procedures so you can see what happens and where it's logged. I'm pretty sure you could get something together quite easily. [1]: http://ola.hallengren.com
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered
An alternative method is to build a query that searches through the SQL Server Logs - DBCC writes out to these logfiles, even in the event of there being no error. Some things to look at to help: * xp_enumerrorlogs - gives a list of the error logs currently available * xp_readerrorlog - returns all rows in the named error log file Unfortunately, these are undocumented by Microsoft, but there are posts out there. I do have a script that does all this, but it's on a computer that's in the throes of Windows Updates, and may be some time recovering...
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Usman Butt avatar image
Usman Butt answered
I guess almost everything you need is available in this excellent [blog][1] post by Gianluca Sartori. The posted script gives you the liberty to log the results in a table as well as sending the email with attachment. [1]: http://spaghettidba.com/2011/11/28/email-alert-dbcc-checkdb/
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered
You could do this very easily with LogParser. The single command for a SQL Server 2008 instance would be: logparser “select * from ‘C:\\Program Files\\Microsoft SQL Server\\MSSQL10.\\MSSQL\\Log\\*errorlog’ where text like ‘%checkdb%’and text not like ‘%found 0 errors%’ and text not like ‘%finished without errors%’ ” -i:textline using the -o parameter you could easily pass the results into a single database from every one of your servers. LogParser can be downloaded from here - http://www.microsoft.com/en-us/download/details.aspx?id=24659
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.