DBCC CHECKDB is the T-SQL command that checks the logical and physical integrity of all the objects in a specified database. Most DBAs probably don’t think twice about running DBCC CHECKDB regularly—until their databases start to get very large. As the size of your database increases, you’ll encounter various challenges in running DBCC CHECKDB. For example, the time it takes to complete a full DBCC CHECKDB process might become prohibitive. In addition, there might not be enough data space for the snapshot created during the DBCC CHECKDB process.
Besides the challenges of dealing with very large databases (VLDBs), your job must also be intelligent enough to recognize new databases, dropped databases, and databases that are offline or otherwise unavailable, such as a mirrored database. To deal with these problems, I created a simple solution using what I call the Admin/Worker Job concept. In the following sections, I discuss the Admin Job and the Worker Job, and I explain how the @VLDB parameter functions. All the scripts in this article will run on both SQL Server 2008 and SQL Server 2005.
The Admin Job
Web Listing 1 contains a script called ServerDailyMaintenance.txt. Running this script creates a SQL Server Agent job—the Admin Job. The Admin Job is the only job that is actually scheduled to run; it creates/updates and starts the Worker Job.
Figure 1 shows the Admin Job’s main step, which is to run msdb.dbo.mnt_DBCC. Web Listing 2 contains the mnt_DBCC stored procedure; this stored procedure identifies the available databases and begins to construct the Worker Job called Maintenance_DBCC_CHECKDB.
Figure 2 lists mnt_DBCC’s parameters and their acceptable values, including what each value is used for. For system-only databases (model and master), the @system_only parameter should be 1. To perform DBCC CHECKDB with the physical_only option, pass 1 to the @physical_only parameter. For most databases, you’ll keep 0 values for the @system_only and @physical_only parameters. For VLDBs, you might want to pass 1 to the @VLDB parameter. If you pass 1 to @VLDB, then you must also pass a value for the @days parameter. The @days parameter is ignored if @VLDB is 0. A value of 0 for @VLDB means the regular DBCC CHECKDB command will be executed.
If you set the mnt_DBCC stored procedure’s @VLDB parameter to 1, mnt_DBCC will call the mnt_DBCC_VLDB stored procedure, which Web Listing 3 contains. Figure 3 lists mnt_DBCC_VLDB’s parameters and values.
The Worker Job
The Worker Job that the Admin Job creates and starts is called Maintenance_DBCC_CHECKDB. Every Worker Job step has a subsequent error-checking step. Figure 4 shows a sample step from the Worker Job for the AdventureWorks database, where the @VLDB parameter is set to 1 and the @days parameter is set to 7.
Using the Admin/Worker Job concept means every Worker Job is dynamic, because it’s modified nightly. You won’t lose any job history for the Worker Job, because the job is updated rather than being dropped and re-created each time.
How Does the @VLDB Parameter Work?
If the @VLDB parameter is set to 0, the Worker Job will run the simple DBCC CHECKDB process. The magic happens when the @VLDB parameter is set to 1. (You have to test to determine the number of days to set for the @days parameter in your environment.)