DBCC CHECKDB for Very Large Databases

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 (below) 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 (below) 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 (below) 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.)

When the @VLDB parameter is set to 1, mnt_DBCC makes a call to mnt_DBCC_VLDB, which audits all of the user tables, system tables, indexed views, and internal tables in the database and determines the size of each. The tables are then separated into a number of groups equal to the number passed for the @days parameter. The tables are spread as evenly as possible in each group, so the load on the server should be the same each night. Each group of tables is placed in a numbered group (VLDB_GROUP) based on the @days value. The numbered group to run on any given day is based on the formula

VLDB_Group = DATEDIFF(dd, N'01-01-2009', GETDATE()) % @days

This formula means the subsequent group will always run, no matter when the job started. The job always knows which groups ran and which is next to run without needing to store data in a table somewhere.

To illustrate how the @VLDB parameter works, let’s consider an example. Suppose you have 10 tables, and you pass 2 for the @days parameter. The stored procedure locates the tables and calculates their size, putting the largest table in VLDB_Group 0, the next largest in VLDB_Group 1, then back to VLDB_Group 0, and so on until all the tables are grouped.

If you run

SELECT DATEDIFF(dd, N'01-03-2009', GETDATE()) % 2

the result will either be 0 or 1. If today is 0, tomorrow will be 1, the next day 0, and so on. This is how each group of tables will run without the job storing information about which tables are in each group.

But what happens if the second-largest table on day 1 becomes the largest table on day 2? The table will be missed because it shifted to group 1. What if table 1 and table 2 both grow or shrink but the relative sizes stay the same? In that case, the tables won’t be missed. In most cases, the largest table will always be the largest table and the second-largest table will always be the second-largest table; no tables will ever be missed because the relative sizes will stay the same. If you want a guarantee that no tables will ever be missed, you’ll have to modify the code to store data in a table somewhere. Save the groups on day 1 and refer to the table throughout the cycle. In addition, you must verify that no tables were dropped or added since the calculation on day 1. Growth isn’t an issue (e.g., if your largest table today becomes your second-largest table tomorrow).

When the @VLDB parameter is set to 1, it also creates a Worker Job that will run DBCC CHECKALLOC and DBCC CHECKCATALOG every time, as Figure 4 shows.

Put It to Use

The DBCC CHECKDB job that uses the Admin/Worker Job method is both intelligent and maintenance-free, and you can run it on any size database. The job automatically runs against databases you add and removes databases from the job that are dropped. In addition, the job can identify whether a database is mirrored or is otherwise offline. You can specify whether to run the job against only system databases or only physical databases. One of the best features is that you can spread the DBCC CHECKDB load over any number of days that you specify.

Web Listing 1: ServerDailyMaintenance.txt

USE [msdb]

GO

IF EXISTS (SELECT * FROM msdb.dbo.sysjobs WHERE [name] = 'Server Daily Maintenance')

BEGIN

   EXEC msdb.dbo.sp_delete_job

       @job_name = 'Server Daily Maintenance'

END

GO

/****** Object:  Job [Server Daily Maintenance]    Script Date: 06/27/2009 17:56:35 ******/

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

/****** Object:  JobCategory [Database Maintenance]    Script Date: 06/27/2009 17:56:35 ******/

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)

BEGIN

EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)

EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Server Daily Maintenance',

       @enabled=1,

       @notify_level_eventlog=2,

       @notify_level_email=0,

       @notify_level_netsend=0,

       @notify_level_page=0,

       @delete_level=0,

       @description=N'',

       @category_name=N'Database Maintenance',

       @owner_login_name=N'sa', @job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object:  Step [Start DBCC CHECKDB]    Script Date: 06/27/2009 17:56:35 ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Start DBCC CHECKDB',

       @step_id=1,

       @cmdexec_success_code=0,

       @on_success_action=1,

       @on_success_step_id=0,

       @on_fail_action=2,

       @on_fail_step_id=0,

       @retry_attempts=0,

       @retry_interval=0,

       @os_run_priority=0, @subsystem=N'TSQL',

       @command=N'SET NOCOUNT ON

EXEC msdb.dbo.mnt_DBCC

   @system_only  = 0,

   @physical_only  = 0,

   @VLDB = 1,

   @days = 7

GO

WAITFOR DELAY ''00:00:10''

GO

EXEC msdb.dbo.sp_start_job

   @job_name = N''Maintenance_DBCC_CHECKDB''',

       @database_name=N'msdb',

       @flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'SDM_DailySchedule',

       @enabled=1,

       @freq_type=4,

       @freq_interval=1,

       @freq_subday_type=1,

       @freq_subday_interval=0,

       @freq_relative_interval=0,

       @freq_recurrence_factor=0,

       @active_start_date=20080927,

       @active_end_date=99991231,

       @active_start_time=93205,

       @active_end_time=235959

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave:

GO

Web Listing 2: mnt_DBCC.txt

USE [msdb]

GO

IF object_id('dbo.mnt_DBCC') IS NULL BEGIN

    EXEC('CREATE PROCEDURE dbo.mnt_DBCC AS RETURN 0')

END

GO

ALTER PROCEDURE dbo.mnt_DBCC

   @system_only bit = 0,   -- 0 for all, 1 for master & msdb only

   @physical_only bit = 0, -- 0 for all, 1 for physical only

   @VLDB bit = 0,                 -- option to break out DBCC CHECKTABLE over time; 0 for no, 1 for yes

   @days tinyint = 7              -- If @VLDB = 1, then @days is the number of days to spread load

AS

SET NOCOUNT ON

SET QUOTED_IDENTIFIER ON

/***************************************************************************

Stored Procedure: mnt_DBCC                            

Written by: David Paul Giroux                                             

Date: 9/26/2008                                                                                              

Purpose: Creates Maintenance_DBCC_CHECKDB SQL Server Agent Job                                        

Input Parameters: @system_only bit, @physical_only bit, @VLDB bit, @days tinyint

Output Parameters: None   

Usage Example: EXEC dbo.mnt_DBCC 0, 0, 1, 1           

Called By: SQL Server Agent Job: Server Daily Maintenance

Calls: msdb.dbo.mnt_DBCC_VLDB,

   sp_add_category, sp_add_job, sp_add_jobstep,

   sp_update_jobstep, sp_delete_jobstep

If @VLDB = 1, @physical_only is ignored

@days is only used with @VLDB and is otherwise ignored

@days determines the number of days to spread the load for DBCC CHECKTABLE

Uses: WITH ALL_ERRORMSGS, NO_INFOMSGS;

***************************************************************************/

DECLARE       @version smallint                        -- SQL Server Version

IF CONVERT(sysname, SERVERPROPERTY(N'ProductVersion')) LIKE N'10%'

BEGIN

   SET @version  = 2008

END

ELSE IF CONVERT(sysname, SERVERPROPERTY(N'ProductVersion')) LIKE N'9%'

BEGIN

   SET @version = 2005

END

ELSE BEGIN

   SELECT N'This version is only for SQL Server 2005 or 2008'

   RETURN

END

IF @system_only NOT IN (0, 1)

BEGIN

   EXEC xp_logevent 77775, N'Illegal value for @system_only.  Choices are 0 or 1', ERROR

   RETURN

END

IF @physical_only NOT IN (0, 1)

BEGIN

   EXEC xp_logevent 77775, N'Illegal value for @physical_only.  Choices are 0 or 1', ERROR

   RETURN

END

BEGIN TRANSACTION

DECLARE       @ReturnCode int

SET    @ReturnCode = 0

 -- Add Job Category if not exist

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WITH (NOLOCK) WHERE name=N'Database Maintenance' AND category_class=1)

BEGIN

   EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'

   IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

-- Create empty job if not exist

IF NOT EXISTS (SELECT [name] FROM msdb.dbo.sysjobs WITH (NOLOCK) WHERE [name] = N'Maintenance_DBCC_CHECKDB')

BEGIN

   DECLARE @jobId BINARY(16)

   EXEC @ReturnCode =  msdb.dbo.sp_add_job

              @job_name=N'Maintenance_DBCC_CHECKDB',

              @enabled=1,

              @notify_level_eventlog=0,

              @notify_level_email=0,

              @notify_level_netsend=0,

              @notify_level_page=0,

              @delete_level=0,

              @description=N'Dynamic Database DBCC Job - updated daily.',

              @category_name=N'Database Maintenance',

              @owner_login_name=N'sa', @job_id = @jobId OUTPUT

   IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

   EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

   IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE       @script       nvarchar(MAX)              -- DBCC CHECKDB command

DECLARE @error nvarchar(1000)                   -- Static error command

DECLARE       @StepName sysname                        -- Step Name

DECLARE       @StepNameE sysname                       -- Step Name for Error Checking

DECLARE       @counter tinyint                         -- a counter equal to number of databases to check

DECLARE       @DBName sysname                                 -- database to be checked

DECLARE       @stepID tinyint                                 -- StepID

DECLARE       @DBNameID tinyint                        -- Identity value of db to check

DECLARE       @crlf nchar(2)                                  -- Carriage return line feed

SET    @stepID = 1

SET    @DBNameID = 1

SET    @crlf = NCHAR(13) + NCHAR(10)            -- Carriage return line feed

-- Table to hold database names to be checked

DECLARE       @Databases TABLE (

   DBNameID tinyint IDENTITY(1,1) primary key,

   DBName sysname

   )

-- Get all online user database names

-- Few exclusions

IF @system_only = 0

BEGIN

   INSERT    @Databases (DBName)

   SELECT    [name] FROM   master.sys.databases WITH (NOLOCK)

   WHERE     [state] = 0

   AND       is_in_standby = 0

   AND       [name] <> N'TempDB'

END

ELSE BEGIN

   INSERT    @Databases (DBName)

   SELECT    N'master' UNION ALL

   SELECT    N'msdb'

END

SET    @counter = SCOPE_IDENTITY()

-- Delete all current steps

EXEC msdb.dbo.sp_delete_jobstep

   @job_name = N'Maintenance_DBCC_CHECKDB',

   @step_id = 0

-- Add new steps

WHILE  @DBNameID <= @counter

BEGIN

   -- Grab first record

   SELECT    @DBName = DBName,

              @StepName = N'DBCC CHECKDB ' + DBName,

              @StepNameE = N'Error Checker ' + DBName

   FROM      @Databases

   WHERE     DBNameID = @DBNameID

SELECT @error =

N'IF DB_ID(''' + @DBName + N''') IS NULL

BEGIN

   RETURN

END

DECLARE @runstatus int

SELECT TOP 1 @runstatus =  run_status

FROM   msdb.dbo.sysjobhistory

WHERE  job_id = CONVERT(uniqueidentifier, $' + N'(ESCAPE_NONE(JOBID)))

AND step_id = CONVERT(int, $' + N'(ESCAPE_NONE(STEPID))) -1  

ORDER BY instance_id DESC

IF @runstatus = 0

BEGIN

   DECLARE @msg nvarchar(250)

   DECLARE @crlf nchar(2)

   SET @crlf = NCHAR(13) + NCHAR(10)

   SELECT    @msg = @crlf +

       ''Please create a Bug; notify ________ via e-mail only.'' + @crlf +

       ''Job: '' + sj.name + '' had an issue at Step: '' + 

        CAST(step_id as varchar(2)) + ''.'' + @crlf +

       ''Step Name: '' + ss.step_name

   FROM      msdb.dbo.sysjobs sj WITH (NOLOCK)

   JOIN      msdb.dbo.sysjobsteps ss WITH (NOLOCK)

   ON sj.job_id = ss.job_id

   WHERE     sj.job_id = CONVERT(uniqueidentifier, $' + N'(ESCAPE_NONE(JOBID))) 

   AND ss.step_id = CONVERT(int, $' + N'(ESCAPE_NONE(STEPID))) -1

   EXEC xp_logevent 77776, @msg, ERROR

END'

   IF @VLDB = 1

   BEGIN

       EXEC msdb.dbo.mnt_DBCC_VLDB

              @days = @days,

             @db = @DBName,

              @version = @version,

              @results = @script OUTPUT

       SELECT @script = N'IF DB_ID(''' + @DBName + N''') IS NULL' + @crlf +

                                  N'BEGIN' + @crlf +

                                  N'     RETURN' + @crlf +

                                  N'END' + @crlf + @crlf +

                                  N'SET NOCOUNT ON' + @crlf + @crlf +

                                  @script

   END

   ELSE BEGIN

       -- Build command

       IF     @physical_only = 1

       BEGIN

              SELECT @script = N'IF DB_ID(''' + @DBName + N''') IS NULL' + @crlf +

                                         N'BEGIN' + @crlf +

                                         N'     RETURN' + @crlf +

                                         N'END' + @crlf + @crlf +

                                         N'SET NOCOUNT ON' + @crlf + @crlf +

                                         N'DBCC CHECKDB ([' + @DBName + N']) WITH PHYSICAL_ONLY, ALL_ERRORMSGS, NO_INFOMSGS;'

       END

       ELSE BEGIN

              SELECT @script = N'IF DB_ID(''' + @DBName + N''') IS NULL' + @crlf +

                                         N'BEGIN' + @crlf +

                                          N'     RETURN' + @crlf +

                                         N'END' + @crlf + @crlf +

                                         N'SET NOCOUNT ON' + @crlf + @crlf +

                                         N'DBCC CHECKDB ([' + @DBName + N']) WITH ALL_ERRORMSGS, NO_INFOMSGS;'

       END

   END

   -- Add worker step

   EXEC @ReturnCode = msdb.dbo.sp_add_jobstep

              @job_name = N'Maintenance_DBCC_CHECKDB',

              @step_id = @stepID,               /*******/

              @step_name = @StepName,           /*******/

              @subsystem = N'TSQL',

              @command = @script,               /*******/

              @cmdexec_success_code = 0,

              @on_success_action = 3,

              @on_success_step_id = 0,

              @on_fail_action = 3,

              @on_fail_step_id = 0,

              @database_name = @DBName,

              @retry_attempts = 0,

              @retry_interval = 0,

              @os_run_priority = 0,

              @flags = 0

   IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

   SET @stepID = @stepID + 1

   -- Checks whether prior step succeeded.

   EXEC @ReturnCode = msdb.dbo.sp_add_jobstep

       @job_name = N'Maintenance_DBCC_CHECKDB',

       @step_id= @stepID,

       @step_name= @StepNameE,

       @cmdexec_success_code=0,

       @on_success_action=3,

       @on_success_step_id=0,

       @on_fail_action=3,

       @on_fail_step_id=0,

       @retry_attempts=0,

       @retry_interval=0,

       @os_run_priority=0,

       @subsystem=N'TSQL',

       @command= @error,

       @database_name=N'msdb',

       @flags=0

   IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

   SET @stepID = @stepID + 1

   SET @DBNameID = @DBNameID + 1

END

-- Update last job step to quit

SET @stepID = @stepID - 1

EXEC @ReturnCode = msdb.dbo.sp_update_jobstep

       @job_name = N'Maintenance_DBCC_CHECKDB',

       @step_id = @stepID,               /*******/

       @on_success_action = 1,           -- Quit With Success

       @on_fail_action = 2               -- Quit With Failure

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

   IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave:

GO

Web Listing 3: mnt_DBCC_VLDB.txt

USE [msdb]

GO

IF object_id('dbo.mnt_DBCC_VLDB') IS NULL BEGIN

    EXEC('CREATE PROCEDURE dbo.mnt_DBCC_VLDB AS RETURN 0')

END

GO

ALTER PROCEDURE dbo.mnt_DBCC_VLDB

   @days tinyint = 7,

   @db sysname,

   @version smallint = 2008,

   @results nvarchar(MAX) OUTPUT

AS

SET NOCOUNT ON

SET QUOTED_IDENTIFIER ON

/***************************************************************************

Stored Procedure: mnt_DBCC_VLDB                              

Written by: David Paul Giroux                                             

Date: 04/2009                                                                                            

Purpose: Produces DBCC CHECKTABLE script              

Input Parameters: @days tinyint, @db sysname

Output Parameters: @results nvarchar(MAX)             

Called By: msdb.dbo.mnt_DBCC

This scproc can be called directly but is designed to be called by msdb.dbo.mnt_DBCC.

   Example Usage if called directly:

       DECLARE       @results nvarchar(MAX)

       EXEC msdb.dbo.mnt_DBCC_VLDB

              @days = 7,

              @db = N'AdventureWorks',

              @version = 2008,

              @results = @results OUTPUT

       SELECT @Results     

Calls: None

Data Modifications: Updates SQL Server Agent Job: Maintenance_DBCC_CHECKDB

Uses: WITH ALL_ERRORMSGS, NO_INFOMSGS;

The sproc calculates the size of each table and then spreads the weight into

a number of groups based on @days.  This allows for an even daily load (close as possible)

A different group is returned each day.

This script knows on any given day which set of tables to execute because of the following statement:

WHERE  VLDB_Group = DATEDIFF(dd, N'01-01-2009', GETDATE()) % @days

***************************************************************************/

DECLARE       @cmd nvarchar(700)

DECLARE @crlf nchar(2)

SET    @crlf = NCHAR(13) + NCHAR(10)

-- All tables

DECLARE @Pool TABLE (

   [Name] sysname,

   ObjectID int primary key,

   ReservedPC bigint

   )

-- Info for tables with XML or Fulltext Indexes

DECLARE @Others TABLE (

   ObjectID int primary key,

   ReservedPC bigint

   )

-- Tables with final ReservedPC amount and grouped by @days

DECLARE @Final TABLE (

   VLDB_Group tinyint,

   [name] sysname primary key,

   ReservedPC bigint

   )

IF @version = 2008

BEGIN

   -- User Tables, System Base Table, Indexed Views, Internal Tables

   SELECT  @cmd =

   N'USE [' + @db + N']' + @crlf +

   N'SELECT SCHEMA_NAME(o.schema_id) + N''.'' + o.name, ' + @crlf +

   N'        o.object_id, SUM(ps.reserved_page_count)' + @crlf +

   N'FROM    [' + @db + N'].sys.objects o WITH (NOLOCK)' + @crlf +

   N'JOIN    [' + @db + N'].sys.dm_db_partition_stats ps WITH (NOLOCK)' + @crlf +

   N'ON             o.object_id = ps.object_id' + @crlf +

   N'WHERE   o.[type] IN (N''U'', N''S'', N''V'', N''IT'')' + @crlf +

   N'GROUP BY  SCHEMA_NAME(o.schema_id) + N''.'' + o.name, o.object_id'

END

ELSE BEGIN

   -- User Tables, Indexed Views, Internal Tables

   SELECT  @cmd =

   N'USE [' + @db + N']' + @crlf +

   N'SELECT SCHEMA_NAME(o.schema_id) + N''.'' + o.name, ' + @crlf +

   N'        o.object_id, SUM(ps.reserved_page_count)' + @crlf +

   N'FROM    [' + @db + N'].sys.objects o WITH (NOLOCK)' + @crlf +

   N'JOIN    [' + @db + N'].sys.dm_db_partition_stats ps WITH (NOLOCK)' + @crlf +

   N'ON             o.object_id = ps.object_id' + @crlf +

   N'WHERE   o.[type] IN (N''U'', N''V'', N''IT'')' + @crlf +

   N'GROUP BY  SCHEMA_NAME(o.schema_id) + N''.'' + o.name, o.object_id'

END

INSERT INTO @Pool

EXEC (@cmd)

-- Check if table has XML Indexes or Fulltext Indexes which use internal tables tied to this table

-- Row counts in these internal tables don't contribute towards row count of original table. 

SELECT  @cmd =

N'USE [' + @db + N']' + @crlf +

N'SELECT      it.object_id, sum(ps.reserved_page_count)' + @crlf +

N'FROM [' + @db + N'].sys.dm_db_partition_stats ps WITH (NOLOCK)' + @crlf +

N'JOIN [' + @db + N'].sys.internal_tables it WITH (NOLOCK)' + @crlf +

N'ON          ps.object_id = it.object_id' + @crlf +

N'WHERE       it.internal_type IN (202,204)' + @crlf +

N'GROUP BY it.object_id'

INSERT INTO @Others

EXEC (@cmd)

UPDATE @Pool

SET    ReservedPC = a.ReservedPC + b.ReservedPC

FROM   @Pool a

JOIN   @Others b

ON     a.ObjectID = b.ObjectID

-- This additional table is needed because cannot filter on ROW_NUMBER function

INSERT @Final

SELECT ROW_NUMBER() OVER(ORDER BY ReservedPC DESC) % @days,

       [name],

       ReservedPC * 8

FROM   @Pool

-- Final results filtered by VLDB_Group

-- The VLDB_Group changes daily

SET    @results = N''

SELECT @results = @results + N'DBCC CHECKTABLE ([' + @db + N'.' + [name] + N']) WITH ALL_ERRORMSGS, NO_INFOMSGS;' + @crlf

FROM   @Final

WHERE  VLDB_Group = DATEDIFF(dd, N'01-01-2009', GETDATE()) % @days -- 01-01-2009 is arbitrary

SET    @results =

N'DBCC CHECKALLOC ([' + @db + N']) WITH ALL_ERRORMSGS, NO_INFOMSGS;' + @crlf +

N'DBCC CHECKCATALOG ([' + @db + N']) WITH NO_INFOMSGS;' + @crlf + @crlf +

@results

GO