If you haven’t experienced database corruption in your SQL Server career and had to pore over DBCC CHECKDB output, you’re a lucky soul. If you encounter corruption, the most important thing you can have is the output, because this helps determine your next course of action.
Step 1
First, you need to be running CHECKDB on a regular basis. If you’re going to ask what you mean by regular basis, I’ll direct you to a post of Paul’s that is still relevant: Importance of running regular consistency checks. There are a few factors to consider, but in general I recommend at least once a week. If you’re a 9 to 5 shop and have the maintenance window to run CHECKDB daily, feel free to do that.
Step 2
Second, running CHECKDB and capturing the output should be an automated process. SQL Agent is your friend here, and you have a few options.
Option 1: Set up an Agent Job
Within SSMS, you can create an Agent job to run DBCC CHECKDB and write the output to a file. I recommend the following syntax, which is good for most databases:
DBCC CHECKDB (‘YourDBNameHere’) WITH NO_INFOMSGS;
GO
Note: If you have a very large database (1TB or larger), or don’t have the maintenance window to run a complete CHECKDB, then you may want to look at another one of Paul’s posts for options, Consistency Checking Options for a VLDB.
Within the Agent job, it’s pretty straight-forward:
To make sure the CHECKDB output is saved, you want to write it out to a file, which you configure on the Advanced tab:
The configuration of the output file is important. If you enter a path and file name like this:
C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\Log\checkdb_output.txt
then every time the job runs it overwrite the contents of this file. You have an option to append the file, but then you have to make sure you manage the size of the file. Instead, append the date and time to the file to make it unique:
C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\Log\checkdb_output_$(ESCAPE_SQUOTE(DATE))_$(ESCAPE_SQUOTE(TIME)).txt
You also need to create a job to clean up output files regularly. With this configuration, you can view the CHECKDB output at any time, as long as the file still exists in the folder specified.
Option 2: Use Ola’s Agent Job
The syntax above works for one database…but what if you have 10 user databases on your instance? And what about system databases? Well, you can add the commands for each database, or you can make life easy and use Ola Hallengren’s scripts for maintenance, which creates two jobs for integrity checks: one for user database, and one for system databases.
Ola’s stored procedure, dbo. DatabaseIntegrityCheck, has multiple options for configuration. By default it checks all tables and indexes, and outputs error messages, and the job writes an output file each time it executes (including date and time in the name) to the instance Log folder by default. When multiple databases are checked, the output for all databases is included in one file. It’s also worth noting that Ola’s scripts create an Output File Cleanup job to clean up those files. By default NONE of these jobs are scheduled, so I recommend you review what the job does, confirm it is configured how you want, and then create a schedule.
Option 3: Use a Maintenance Plan
In every environment I manage directly, I use Ola’s scripts for maintenance tasks. However, some folks may be more comfortable using Maintenance Plans. If that’s true for you, then when you set up the plan for integrity checks, there are two things I recommend. First, you typically do not want to run with the PHYSICAL_ONLY option unless the check takes an extremely long time to run and you’re trying to make the task fit into a maintenance window. When you use the PHYSICAL_ONLY option the logical checks, inter-page checks, and things like DBCC CHECKCATALOG will be skipped.
Second, the maintenance task writes to an output file by default. Within the UI, you can select the Reporting and Logging button to confirm the location.
By default, it writes to the Log folder for the SQL Server installation (e.g. C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\Log for SQL Server 2019). Each file is created with the date and time as part of the filename. As with other options, you need to ensure that these output files are cleaned up on a regular basis.
Other Items to Consider
If you run DBCC CHECKDB in SSMS via a machine you have RDP’d to, be aware that depending on the group policy your session can disconnect after a certain amount of time. I’ve had this happen in client environments, and if you’re trying to run CHECKDB, it will stop and you won’t get any output…hence the suggestion to use an Agent job.
With any Agent job you use for integrity checks, I also recommend (dare I say insist?) that you enable notifications for job failure. This is configured on the Notifcations tab of the job, and requires an Operator and database mail. Create an Operator that writes to a team alias (e.g. DBAs@yourcompany.com) rather than just you.
Summary
If you are responsible for any SQL Server instance, you want to make sure of the following:
- You are running DBCC CHECKDB on a regular basis to look for integrity issues.
- This process should be automated.
- Your method for checking should include an option to write the CHECKDB output to a file that can be referenced later.
- If you find corruption, make a couple copies of this file. If it gets deleted, you will have to run CHECKDB again to determine what is corrupt.
- Set up Notifications for the Agent job so you are notified if corruption is found.
I hope that the information in this post was review for you, but if it wasn’t, or if you have encountered corruption, feel free to reach out to see if we can help.
8 thoughts on “Capturing DBCC CHECKDB Output”
Great post 🙂
I have with success used Ola’s solution several times. But some years ago I wanted to log how long time it took to check each object. To log this I made a PowerShell solution (https://sqladm.blogspot.com/2014/10/dbcc-checkdb-with-powershell.html) that adds a timestamp to each log line. This could be refined but was good enough in the situation as we just ordered by the size of each object to identify candidates for further investigation.
Interesting idea, thanks for sharing the script!
Hi Erin,
Great write-up and extremely practical! Your time to put this information together will help many companies avoid or minimize the grief associated with recovering from SQL Server data corruption (which fortunately is a rare occurrence with a knowledgeable dba).
Just posting here because today (9 May 20) I ran into some security issues when following the link to Ola’s site. Fortunately, he has posted the scripts on GitHub at this location:
https://github.com/olahallengren/sql-server-maintenance-solution
Best regards,
Good point about GitHub, I still default to his site! I updated the link, thanks 🙂
Hi Erin ,
I created task with option 3 (Maintinance Plan) also configured email , i do get the email with FAILURE Status (because one of my DB is having integrity issues) with only the log of job. what i want is the output of checkdb in email (either attachment or email body) ,Can you please guide me. also check the output required in point 2 below
1) What i am getting in email body (job log)
JOB RUN: ‘Check Integrety-DB.Subplan_1′ was run on 7/5/2020 at 9:09:14 AM
DURATION: 0 hours, 0 minutes, 0 seconds
STATUS: Failed
MESSAGES: The job failed. The Job was invoked by User Domain\User. The last step to run was step 1 (Subplan_1).
2) what i want in email (checkdb output either as attachment or in email body)
Microsoft(R) Server Maintenance Utility (Unicode) Version 11.0.7001
Report was generated on “SQLSERVER2012”.
Maintenance Plan: Check Integrety-DB
Duration: 00:00:13
Status: Warning: One or more tasks failed.
Details:
Check Database Integrity (SQLSERVER2012)
Check Database integrity on Local server connection
Databases: DB
Include indexes
Task start: 2020-07-05T09:02:17.
Task end: 2020-07-05T09:02:31.
Failed:(-1073548784) Executing the query “DBCC CHECKDB(N’DB’) WITH NO_INFOMSGS…” failed with the following error: “Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (1:202200) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
Page errors on the GAM, SGAM, or PFS pages prevent allocation integrity checks in database ID 8 pages from (1:202200) to (1:210287). See other errors for cause.
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (1:202200) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.
CHECKDB found 2 allocation errors and 1 consistency errors not associated with any single object.
CHECKDB found 2 allocation errors and 1 consistency errors in database ‘DB’.”. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.
Command:USE [DB]
GO
DBCC CHECKDB(N”DB”) WITH NO_INFOMSGS
GO
Have you tried checking the option “Send report to an email recipient” in the Reporting and Logging option for the plan?
Yes , it is already setup .Operator is already chosen over ther. but not getting the output of checkdb in email
I’m not sure why that’s not working – you could post to a forum to see if anyone else has had a similar issue.