This article describes best practices for adding DBCC checks to your SQL Maintenance plan for your Service Center database. The Database Console Commands are a series of commands used to check the consistency of Microsoft SQL server database. These statements are a programming language in Transact-SQL. For a production Service Center database server, you should avoid using the default Check Database Integrity Task in the SQL Maintenance plan options.
For a production Service Center database server, you should avoid using the default Check Database Integrity Task in the SQL Maintenance plan options.
Microsoft’s page on DBCC CHECKDB Recommendations states:
DBCC CHECKDB is a CPU- and disk-intensive operation. Each data page that requires checking must first be read from disk into memory. In addition, DBCC CHECKDB uses tempdb to do sorting.
For a more detailed description, please see Microsoft's DBCC article.
Examples of DBCC running with SQL TempDB
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 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. 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
Adding an Execute T-SQL Statement Task
To add an Execute T-SQL Statement Task to your Maintenance Plan, perform the following steps.
- Launch SQL Server Management Studio and connect to the appropriate SQL Instance.
- In the left hand navigation window, go to Management - Maintenance Plans.
- If you don’t already have a maintenance plan, create one, otherwise, modify the existing plan.
- On the Maintenance Plan Design screen, open the Toolbox (CTRL+ALT+X).
- From the Toolbox, drag the Execute T-SQL Statement Task into your Maintenance Plan.
- Right-Click the newly added Execute T-SQL Statement Task and choose Edit.
- Add the query to run DBCC CHECKDB. See the screenshot below for reference.
- Click OK.
Note: this article is intended to provide guidance on running regular occurring DBCC checks. You should also consider running a full DBCC check periodically to ensure data integrity.