Step 2<\/h1>\n
Second, running CHECKDB and capturing the output should be an automated process.\u00a0 SQL Agent is your friend here, and you have a few options.<\/p>\n
Option 1: Set up an Agent Job<\/h2>\n
Within SSMS, you can create an Agent job to run DBCC CHECKDB and write the output to a file.\u00a0 I recommend the following syntax, which is good for most databases:<\/p>\n
DBCC CHECKDB (‘YourDBNameHere’) WITH NO_INFOMSGS;
\nGO<\/p>\n
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<\/a>.<\/em><\/p>\n Within the Agent job, it’s pretty straight-forward:<\/p>\n <\/p>\n To make sure the CHECKDB output is saved, you want to write it out to a file<\/strong>, which you configure on the Advanced tab:<\/p>\n <\/p>\n The configuration of the output file is important.\u00a0 If you enter a path and file name like this:<\/p>\n C:\\Program Files\\Microsoft SQL Server\\MSSQL15.SQL2019\\MSSQL\\Log\\checkdb_output.txt<\/span><\/p>\n then every time the job runs it overwrite the contents of this file.\u00a0 You have an option to append the file, but then you have to make sure you manage the size of the file.\u00a0 Instead, append the date and time to the file to make it unique:<\/p>\n C:\\Program Files\\Microsoft SQL Server\\MSSQL15.SQL2019\\MSSQL\\Log\\checkdb_output_$(ESCAPE_SQUOTE(DATE))_$(ESCAPE_SQUOTE(TIME)).txt<\/span><\/p>\n You also need to create a job to clean up output files regularly.\u00a0 With this configuration, you can view the CHECKDB output at any time, as long as the file still exists in the folder specified.<\/p>\n The syntax above works for one database…but what if you have 10 user databases on your instance?\u00a0 And what about system databases?\u00a0 Well, you can add the commands for each database, or you can make life easy and use Ola Hallengren’s scripts<\/a> for maintenance, which creates two jobs for integrity checks: one for user database, and one for system databases.<\/p>\n <\/p>\n Ola’s stored procedure, dbo. DatabaseIntegrityCheck, has multiple options for configuration<\/a>.\u00a0 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.\u00a0 When multiple databases are checked, the output for all databases is included in one file.\u00a0 It’s also worth noting that Ola’s scripts create an Output File Cleanup job to clean up those files.\u00a0 By default NONE<\/strong> 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.<\/p>\n In every environment I manage directly, I use Ola’s scripts for maintenance tasks.\u00a0 However, some folks may be more comfortable using Maintenance Plans.\u00a0 If that’s true for you, then when you set up the plan for integrity checks, there are two things I recommend.\u00a0 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.\u00a0 When you use the PHYSICAL_ONLY option the logical checks, inter-page checks, and things like DBCC CHECKCATALOG <\/em>will be skipped.<\/p>\n <\/p>\n Second, the maintenance task writes to an output file by default.\u00a0 Within the UI, you can select the Reporting and Logging button to confirm the location.<\/p>\n <\/p>\n 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).\u00a0 Each file is created with the date and time as part of the filename.\u00a0 As with other options, you need to ensure that these output files are cleaned up on a regular basis.<\/p>\n 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.\u00a0 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.<\/p>\n With any Agent job you use for integrity checks, I also recommend (dare I say insist?) that you enable notifications for job failure.\u00a0 This is configured on the Notifcations tab of the job, and requires an Operator and database mail.\u00a0 Create an Operator that writes to a team alias (e.g. DBAs@yourcompany.com) rather than just you.<\/p>\n <\/p>\n If you are responsible for any SQL Server instance, you want to make sure of the following:<\/p>\n![\"Running](\"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2020\/05\/checkdb-job-step_new-1024x941.jpg\")
<\/a>![\"CHECKDB](\"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2020\/05\/checkdb-file-output-1024x745.jpg\")
<\/a>Option 2: Use Ola’s Agent Job<\/h2>\n
![\"SQL](\"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2020\/05\/Agent-jobs.jpg\")
<\/a>Option 3: Use a Maintenance Plan<\/h2>\n
![\"Options](\"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2020\/05\/maintenance-task_2.jpg\")
<\/a>![\"Reporting](\"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2020\/05\/Plan-logging.jpg\")
<\/a>Other Items to Consider<\/h1>\n
![\"Agent](\"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2020\/05\/notification-1024x990.jpg\")
<\/a>Summary<\/h1>\n