Adding DBCC to your SQL Maintenance plan

This document assumes you have existing knowledge of DBCC.

For a production Service Center database server, you should avoid using the default Check Database Integrity Task in the SQL Maintenance plan options. To quote Microsoft’s page on DBCC CHECKDB Recommendations, DBCC CHECKDB is a CPU- and disk-intensive operation. Each data page that requires checking must first be read from the disk into memory. In addition, DBCC CHECKDB uses tempdb to do the sorting. While this DBCC check is running, the SQL TempDB can grow significantly. You can use DBCC CHECKDB WITH ESTIMATEONLY to determine how much disk space the TempDB will require for this operation. See the Example below:

The recommendation for a production environment is to run DBCC CHECKDB with the PHYSICAL_ONLY flag. The default Check Database Integrity Task in the Maintenance Plan options does not allow this, however. Instead, you should use the Execute T-SQL Statement Task and add the following query. This query assumes you are running a standard Self-Hosted Service Center.

USE [SCData_Default]
GO
DBCC CHECKDB(N'SCData_Default') WITH PHYSICAL_ONLY
GO
USE [SCMaster]
GO
DBCC CHECKDB(N'SCMaster') WITH PHYSICAL_ONLY

To add an Execute T-SQL Statement Task to your Maintenance Plan, perform the following steps

1. Launch SQL Server Management Studio and connect to the appropriate SQL Instance.
2. In the left hand navigation window, go to Management > Maintenance Plans.
3. If you don’t already have a maintenance plan, create one, otherwise, modify the existing plan.
4. On the Maintenance Plan Design screen, open the Toolbox (CTRL+ALT+X).
5. From the Toolbox, drag the Execute T-SQL Statement Task into your Maintenance Plan.
6. Right-Click the newly added Execute T-SQL Statement Task and choose Edit.
7. Add the query to run DBCC CHECKDB. It should appear like the following:
   
8. Click OK.