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