SQL Server DBCC CHECKDB, CHECKCATALOG and CHECKALLOC for VLDBs

Problem

I support a very large SQL Server database. My maintenance window is too short to run a full database level integrity checks of the entire SQL Server database. Other than restoring a backup of the database to another server and running the integrity check against the restored copy, are there any other options?

Solution

The solution covered in this tip will show one method for executing integrity checks against very large SQL Server databases (VLDBs). The approach for VLDBs is to break down the integrity checks to the table level and run the procedure more frequently. The procedure is also versatile enough to execute integrity checks at the traditional database level for smaller databases.

SQL Server DBCC CHECKDB, CHECKCATALOG and CHECKALLOC Code for VLDBs

Below is the code to support the DBCC CHECKDB, CHECKCATALOG and CHECKALLOC maintenance processes. These objects should be created in a dedicated, administration database.

Table used to store information related to running dbo.CustomDBCC in VLDB mode

USE Admin;
GO
IF OBJECT_ID('[dbo].[CheckTableStatus]', 'U') IS NULL
 CREATE TABLE [dbo].[CheckTableStatus] ( 
  [checkTableID] [BIGINT] IDENTITY(1,1) NOT NULL,
  [databaseName] [NVARCHAR](128) NOT NULL,
  [schemaName] [NVARCHAR](128) NOT NULL,
  [tableName]  [NVARCHAR](128) NOT NULL,
  [procFlag]  [BIT] NULL,
  [startDate]  [DATETIME] NULL,
  [endDate]  [DATETIME] NULL
 );

Function used to exclude databases and/or tables from integrity checks

USE Admin;
GO
IF OBJECT_ID('dbo.CommaStringTable', 'TF') IS NULL
 EXEC('CREATE FUNCTION dbo.CommaStringTable (@p1 INT) RETURNS @t TABLE (id INT) AS BEGIN INSERT @t SELECT 0 RETURN END');
GO
ALTER FUNCTION [dbo].[CommaStringTable] ( @StringInput VARCHAR(MAX) )
RETURNS @temp TABLE ( [Value] VARCHAR(128) )
AS
BEGIN
 DECLARE @String    VARCHAR(128);
 -- Scrub the input string if necessary
 IF CHARINDEX(' , ', @StringInput) <> 0
  SET @StringInput = REPLACE(@StringInput, ' , ', ',');
  
 IF CHARINDEX(', ', @StringInput) <> 0
  SET @StringInput = REPLACE(@StringInput, ', ', ',');
  
 IF CHARINDEX(' ,', @StringInput) <> 0
  SET @StringInput = REPLACE(@StringInput, ' ,', ',');
 IF LEFT(@StringInput, 1) = ','
  SET @StringInput = SUBSTRING(@StringInput, 2, (LEN(@StringInput) - 1));
 IF RIGHT(@StringInput, 1) = ','
  SET @StringInput = SUBSTRING(@StringInput, 1, (LEN(@StringInput) - 1));
 -- Populate the table variable
 WHILE LEN(@StringInput) > 0
 BEGIN
  SET @String = LEFT(@StringInput, ISNULL(NULLIF(CHARINDEX(',', @StringInput) - 1, -1), LEN(@StringInput)));
  SET @StringInput = SUBSTRING(@StringInput, ISNULL(NULLIF(CHARINDEX(',', @StringInput), 0), LEN(@StringInput)) + 1, LEN(@StringInput));
  INSERT INTO @temp ( [Value] ) VALUES ( @String );
 END
 -- One more pass to remove extraneous spaces
 UPDATE @temp SET [Value] = LTRIM(RTRIM([Value]));
 RETURN;
END
GO

Procedure containing code to execute integrity checks

USE Admin;
GO
IF OBJECT_ID('dbo.CustomDBCC', 'P') IS NULL
 EXEC('CREATE PROCEDURE [dbo].[CustomDBCC] AS SELECT 0');
GO
/********************************************************************************************************************
*Author: Mike Eastland                                *
*                                         *
*Notes:  The purpose of this stored procedure is to run one or more DBCC commands as dictated by the parameters *
*    passed at run-time. It has been designed to accommodate VLDBs. It is recommended to create this  *
*    procedure in a dedicated administrative database rather than a system or application database.  *
********************************************************************************************************************/
ALTER PROCEDURE [dbo].[CustomDBCC] (
 @checkAlloc  BIT = 0,     -- Execute DBCC CHECKALLOC
 @checkCat  BIT = 0,     -- Execute DBCC CHECKCATALOG
 @checkDB  BIT = 1,     -- Execute DBCC CHECKDB (which includes CHECKALLOC and CHECKCATALOG)
 @checkNdx  BIT = 1,     -- Include indexes in DBCC commands
 @dbName   SYSNAME = NULL,    -- Run for a single database
 @dbExcludeList VARCHAR(MAX) = NULL,  -- Comma-separated list of databases to exclude
 @debugMode  BIT = 0,     -- Prevent execution of DBCC commands (@debugMode = 1)
 @maxDuration INT = 0,     -- Number of hours the procedure is allowed to run (0 = to completion)
 @physOnly  BIT = 0,     -- Run CHECKDB with PHYSICAL_ONLY option
 @tableName  SYSNAME = NULL,    -- Run for a single table
 @tblExcludeList VARCHAR(MAX) = NULL,  -- Comma-separated list of tables to exclude
 @vldbMode  BIT = 0      -- Execute DBCC commands at the table-level for VLDBs
)
AS
SET NOCOUNT, XACT_ABORT ON
DECLARE @db   VARCHAR(128),
  @dbclause VARCHAR(128),
  @end  DATETIME,
  @msg  VARCHAR(1024),
  @restart BIT,
  @sql  NVARCHAR(MAX),
  @tbl  VARCHAR(128),
  @tblid  INT;
DECLARE @db_tbl  TABLE ( DatabaseName VARCHAR(128), ProcFlag  BIT DEFAULT(0) );
DECLARE @check_tbl TABLE ( DatabaseName VARCHAR(128),
       SchemaName  VARCHAR(128),
       TableName  VARCHAR(128) );
SET @msg = 'DBCC job on ' + @@SERVERNAME + ' started at ' + CONVERT(VARCHAR, GETDATE()) + '.' + CHAR(10) + CHAR(13);
 RAISERROR(@msg, 0, 0) WITH NOWAIT;
-- Set initial / default variable values
SELECT @vldbMode = ISNULL(@vldbMode, 0), @physOnly = ISNULL(@physOnly, 0), @restart = 1,
  @maxDuration = CASE WHEN @maxDuration IS NULL THEN 0 ELSE @maxDuration END,
  @dbName = CASE LTRIM(RTRIM(@dbName)) WHEN '' THEN NULL ELSE LTRIM(RTRIM(@dbName)) END,
  @dbExcludeList = CASE ISNULL(@dbName, 'NULL') WHEN 'NULL' THEN @dbExcludeList ELSE NULL END;
SELECT @end = CASE @maxDuration WHEN 0 THEN '9999-12-31 23:59:59:997' ELSE DATEADD(MINUTE, @maxDuration * 60, GETDATE()) END,
  @checkDB = CASE @vldbMode WHEN 0 THEN @checkDB ELSE 0 END,
  @checkCat = CASE @checkDB WHEN 1 THEN 0 ELSE @checkCat END,
  @checkAlloc = CASE @checkDB WHEN 1 THEN 0 ELSE @checkAlloc END;
-- Validate variables
IF @dbName IS NOT NULL AND DB_ID(@dbName) IS NULL
BEGIN
 SET @msg = 'Database {' + @dbName + '} does not exist. Procedure aborted at ' + CONVERT(VARCHAR, GETDATE()) + '.';
  RAISERROR(@msg, 0, 0) WITH NOWAIT;
   RETURN;
END
ELSE
BEGIN
 SET @msg = 'DBCC job will execute for a single database {' + @dbName + '}';
  RAISERROR(@msg, 0, 0) WITH NOWAIT;
END
IF @tableName IS NOT NULL
BEGIN
 IF @vldbMode <> 1
 BEGIN
  SET @msg = 'The @vldbMode parameter must be set if @tableName is not null. Procedure aborted at ' + CONVERT(VARCHAR, GETDATE()) + '.';
   RAISERROR(@msg, 0, 0) WITH NOWAIT;
    RETURN;
 END
 ELSE
 BEGIN
  SET @msg = 'DBCC job will execute for a single table {' + @tableName + '} in each target database.';
   RAISERROR(@msg, 0, 0) WITH NOWAIT;
 END
END
IF @tblExcludeList IS NOT NULL AND @vldbMode <> 1
BEGIN
 SET @msg = 'The @vldbMode parameter must be set if @tblExcludeList is not null. Procedure aborted at ' + CONVERT(VARCHAR, GETDATE()) + '.';
  RAISERROR(@msg, 0, 0) WITH NOWAIT;
   RETURN;
END
IF @checkAlloc = 0 AND @checkCat = 0 AND @checkDB = 0 AND @vldbMode = 0
BEGIN
 SET @msg = 'Invalid parameter combination would result in no DBCC commands executed. Procedure aborted at ' + CONVERT(VARCHAR, GETDATE()) + '.';
  RAISERROR(@msg, 0, 0) WITH NOWAIT;
   RETURN;
END
IF @debugMode = 1
BEGIN
 SET @msg = 'Procedure [' + OBJECT_NAME(@@PROCID) + '] is running in debug mode. No integrity check commands will be executed.';
  RAISERROR(@msg, 0, 0) WITH NOWAIT;
END
INSERT INTO @db_tbl (DatabaseName)
SELECT [name]
FROM [master].sys.databases
WHERE [source_database_id] IS NULL
AND [database_id] <> 2
AND DATABASEPROPERTYEX([name], 'Status') = 'ONLINE'
AND LOWER([name]) = LOWER(ISNULL(@dbName, [name]));
-- Exlude databases
IF (@dbExcludeList IS NOT NULL AND LTRIM(RTRIM(@dbExcludeList)) <> '')
BEGIN
 IF OBJECT_ID('dbo.CommaStringTable') IS NULL
 BEGIN
  SET @msg = 'The function required by skip-database code does not exist.  All databases will be checked.';
   RAISERROR(@msg, 0, 0) WITH NOWAIT;
 END
 ELSE
 BEGIN
  SET @msg = 'The following databases will be skipped: (' + LTRIM(RTRIM(@dbExcludeList)) + ').';
   RAISERROR(@msg, 0, 0) WITH NOWAIT;
  
  DELETE d
  FROM @db_tbl d
   INNER JOIN dbo.CommaStringTable(@dbExcludeList) f ON LOWER(d.DatabaseName) = LOWER(f.[Value]);
 END
END
IF NOT EXISTS ( SELECT * FROM @db_tbl WHERE ProcFlag = 0 )
BEGIN
 SET @msg = 'No databases match the supplied parameters. Procedure aborted at ' + CONVERT(VARCHAR, GETDATE()) + '.';
  RAISERROR(@msg, 0, 0) WITH NOWAIT;
   RETURN;
END
WHILE EXISTS ( SELECT * FROM @db_tbl WHERE ProcFlag = 0 )
BEGIN
 SELECT TOP 1 @db = DatabaseName FROM @db_tbl WHERE ProcFlag = 0 ORDER BY DatabaseName;
 SET @dbclause = '[' + @db + CASE @checkNdx WHEN 1 THEN ']' ELSE '], NOINDEX' END;
 -- Execute database-level DBCC commands
 BEGIN TRY
  IF @checkAlloc = 1
  BEGIN
   SET @msg = 'DBCC CHECKALLOC against ' + QUOTENAME(@db) + ' started at ' + CONVERT(VARCHAR, GETDATE()) + '.';
    RAISERROR(@msg, 0, 0) WITH NOWAIT;
   SET @sql = 'SET QUOTED_IDENTIFIER OFF SET ARITHABORT ON DBCC CHECKALLOC (' + @dbclause + ') WITH ALL_ERRORMSGS, NO_INFOMSGS';
    RAISERROR(@sql, 0, 0) WITH NOWAIT;
   IF @debugMode = 0
    EXEC sp_ExecuteSQL @sql;
   SET @msg = 'DBCC CHECKALLOC against ' + QUOTENAME(@db) + ' completed at ' + CONVERT(VARCHAR, GETDATE()) + '.';
    RAISERROR(@msg, 0, 0) WITH NOWAIT;
  END
  
  IF @checkCat = 1
  BEGIN
   SET @msg = 'DBCC CATALOG against ' + QUOTENAME(@db) + ' started at ' + CONVERT(VARCHAR, GETDATE()) + '.';
    RAISERROR(@msg, 0, 0) WITH NOWAIT;
   SET @sql = 'SET QUOTED_IDENTIFIER OFF SET ARITHABORT ON DBCC CHECKCATALOG ([' + @db + ']) WITH NO_INFOMSGS';
    RAISERROR(@sql, 0, 0) WITH NOWAIT;
   IF @debugMode = 0
    EXEC sp_ExecuteSQL @sql;
   SET @msg = 'DBCC CATALOG against ' + QUOTENAME(@db) + ' completed at ' + CONVERT(VARCHAR, GETDATE()) + '.';
    RAISERROR(@msg, 0, 0) WITH NOWAIT;
  END
  IF @checkDB = 1
  BEGIN
   SET @msg = 'DBCC CHECKDB against ' + QUOTENAME(@db) + ' started at ' + CONVERT(VARCHAR, GETDATE()) + '.';
    RAISERROR(@msg, 0, 0) WITH NOWAIT;
   SET @sql = 'SET QUOTED_IDENTIFIER OFF SET ARITHABORT ON DBCC CHECKDB (' + @dbclause + ') WITH ALL_ERRORMSGS, NO_INFOMSGS' + 
      CASE @physOnly WHEN 1 THEN ', PHYSICAL_ONLY' ELSE '' END;
    RAISERROR(@sql, 0, 0) WITH NOWAIT;
   IF @debugMode = 0
    EXEC sp_ExecuteSQL @sql;
   SET @msg = 'DBCC CHECKDB against ' + QUOTENAME(@db) + ' completed at ' + CONVERT(VARCHAR, GETDATE()) + '.';
    RAISERROR(@msg, 0, 0) WITH NOWAIT;
  END
  IF @vldbMode = 1
  BEGIN
   SET @sql = 'SELECT [TABLE_CATALOG], [TABLE_SCHEMA], [TABLE_NAME] FROM [' + @db + 
      '].[INFORMATION_SCHEMA].[TABLES] WHERE [TABLE_TYPE] = ''BASE TABLE'' ORDER BY [TABLE_NAME]';
   INSERT INTO @check_tbl ([DatabaseName], [SchemaName], [TableName])
   EXEC sp_ExecuteSQL @sql;
  END
  UPDATE @db_tbl SET ProcFlag = 1 WHERE DatabaseName = @db;
  IF @end < GETDATE()
  BEGIN
   SET @msg = 'Procedure has exceeded max run time based on @maxDuration parameter and will exit at ' + CONVERT(VARCHAR, GETDATE()) + '.';
    RAISERROR(@msg, 0, 0) WITH NOWAIT;
     RETURN;
  END
 END TRY
 
 BEGIN CATCH
  SET @msg = 'Failed to execute command {' + @sql + '} against database {' + @db + '} with error number: ' + CAST(ERROR_NUMBER() AS VARCHAR) + 
     '; error message: ' + ERROR_MESSAGE() + '.  Procedure terminated at ' + CONVERT(VARCHAR, GETDATE()) + '.';
   RAISERROR(@msg, 16, 1) WITH LOG, NOWAIT;
    RETURN(-1);  
 END CATCH
END
IF @vldbMode = 1
BEGIN
 IF OBJECT_ID('[dbo].[CheckTableStatus]', 'U') IS NULL
  CREATE TABLE [dbo].[CheckTableStatus] ( [checkTableID] [BIGINT] IDENTITY(1,1) NOT NULL,
            [databaseName] [NVARCHAR](128) NOT NULL,
            [schemaName] [NVARCHAR](128) NOT NULL,
            [tableName]  [NVARCHAR](128) NOT NULL,
            [procFlag]  [BIT] NULL,
            [startDate]  [DATETIME] NULL,
            [endDate]  [DATETIME] NULL );
 ELSE
  DELETE FROM [dbo].[CheckTableStatus] WHERE [endDate] < GETDATE() - 367 AND ISNULL([procFlag], 1) = 1;
 -- Check for outstanding CHECKTABLE commands
 IF EXISTS ( SELECT * FROM [dbo].[CheckTableStatus] WHERE [procFlag] = 0 )
  SET @restart = 0;
 IF @restart = 1
  INSERT INTO [dbo].[CheckTableStatus] ([databaseName], [schemaName], [tableName], [procFlag])
  SELECT DatabaseName, SchemaName, TableName, 0
  FROM @check_tbl c
  WHERE NOT EXISTS ( SELECT *
       FROM dbo.CommaStringTable(@tblExcludeList) f
       WHERE LOWER(f.[Value]) = LOWER(c.tableName) )
  AND LOWER(c.tableName) = LOWER(ISNULL(@tableName, c.tableName));
 ELSE
 BEGIN
  SET @msg = 'Procedure has unfinished business in VLDB mode.';
   RAISERROR(@msg, 0, 0) WITH NOWAIT;
 END
 -- Exclude tables
 IF (@tblExcludeList IS NOT NULL AND LTRIM(RTRIM(@tblExcludeList)) <> '')
 BEGIN
  IF OBJECT_ID('dbo.CommaStringTable') IS NULL
  BEGIN
   SELECT @msg = 'The function required by skip-table code does not exist. All tables will be checked.', @tblExcludeList = NULL;
    RAISERROR(@msg, 0, 0) WITH NOWAIT;
  END
  ELSE
  BEGIN
   SET @msg = 'The following tables will be skipped for all databases: (' + REPLACE(@tblExcludeList, ' ', '') + ').';
    RAISERROR(@msg, 0, 0) WITH NOWAIT;
   UPDATE cts
   SET cts.[procFlag] = NULL
   FROM [dbo].[CheckTableStatus] cts
    INNER JOIN dbo.CommaStringTable(@tblExcludeList) cst ON LOWER(cts.tableName) = LOWER(cst.[Value])
   WHERE ISNULL(cts.[procFlag], 0) = 0;
  END
 END
 WHILE EXISTS ( SELECT c.*
     FROM [dbo].[CheckTableStatus] c
      INNER JOIN @db_tbl t ON c.databaseName = t.DatabaseName
     WHERE c.procFlag = 0
     AND LOWER(c.tableName) = LOWER(ISNULL(@tableName, c.tableName)) )
 BEGIN  
  SELECT TOP 1 @tbl = '[' + c.databaseName + '].[' + c.schemaName + '].[' + c.tableName + ']', 
      @sql = 'SET QUOTED_IDENTIFIER OFF SET ARITHABORT ON DBCC CHECKTABLE (' + CHAR(39) + @tbl + CHAR(39) + 
      CASE @checkNdx WHEN 0 THEN ', NOINDEX' ELSE '' END + ') WITH ALL_ERRORMSGS, NO_INFOMSGS' + 
      CASE @physOnly WHEN 1 THEN ', PHYSICAL_ONLY' ELSE '' END, @tblid = c.checkTableID
  FROM [dbo].[CheckTableStatus] c
   INNER JOIN @db_tbl t ON c.databaseName = t.DatabaseName
  WHERE c.procFlag = 0
  AND LOWER(c.tableName) NOT IN ( SELECT LOWER([Value]) FROM dbo.CommaStringTable(@tblExcludeList) )
  AND LOWER(c.tableName) = LOWER(ISNULL(@tableName, c.tableName))
  ORDER BY c.databaseName, c.schemaName, c.tableName;
  -- Execute table-level DBCC commands
  BEGIN TRY
   RAISERROR(@sql, 0, 0) WITH NOWAIT;
         
   IF @debugMode = 0
   BEGIN
    UPDATE [dbo].[CheckTableStatus] SET startDate = GETDATE() WHERE checkTableID = @tblid;
    
    IF OBJECT_ID(@tbl) IS NOT NULL
     EXEC sp_ExecuteSQL @sql;
    UPDATE [dbo].[CheckTableStatus] SET procFlag = CASE ISNULL(OBJECT_ID(@tbl), 0) WHEN 0 THEN NULL ELSE 1 END, endDate = GETDATE() WHERE checkTableID = @tblid;
   END
   ELSE
    UPDATE [dbo].[CheckTableStatus] SET procFlag = NULL WHERE checkTableID = @tblid;
          
   IF @end < GETDATE()
   BEGIN
    SET @msg = 'Procedure has exceeded max run time based on @maxDuration parameter and will exit at ' + CONVERT(VARCHAR, GETDATE()) + '.';
     RAISERROR(@msg, 0, 0) WITH NOWAIT;
      RETURN;
   END
  END TRY
  
  BEGIN CATCH
   SET @msg = 'Failed to execute command {' + @sql + '} with error number: ' + CAST(ERROR_NUMBER() AS VARCHAR) + '; error message: ' + 
      ERROR_MESSAGE() + '.  Procedure terminated at ' + CONVERT(VARCHAR, GETDATE()) + '.';
    RAISERROR(@msg, 16, 2) WITH LOG, NOWAIT;
     RETURN(-2);  
  END CATCH
 END
END
IF @debugMode = 1
 UPDATE dbo.CheckTableStatus SET procFlag = 0, startDate = NULL, endDate = NULL WHERE procFlag IS NULL;
SET @msg = CHAR(10) + CHAR(13) + 'DBCC job on ' + @@SERVERNAME + ' ended at ' + CONVERT(VARCHAR, GETDATE()) + '.';
 RAISERROR(@msg, 0, 0) WITH NOWAIT;
GO

Syntax for dbo.CustomDBCC

EXEC [dbo].[CustomDBCC] 
            @checkAlloc = checkAlloc
          , @checkCat = checkCat
          , @checkDB = checkDB
          , @checkNdx = checkNdx
          , @dbName = 'dbName'
          , @dbExcludeList = 'ExcludeDB1, ExcludeDB2, ExcludeDBN'
          , @debugMode = debugMode
          , @maxDuration = maxDuration
          , @physOnly = physOnly
          , @tableName = 'tableName'
          , @tblExcludeList = 'ExcludeTable1,ExcludeTable2, ExcludeTableN'
          , @vldbMode = vldbMode;

CustomDBCC Arguments

The following parameters are relevant to the execution of the dbo.CustomDBCC procedure.

@checkAlloc = checkAlloc

Flag to determine if DBCC CHECKALLOC is executed. checkAlloc is bit with a default of 0.

@checkCat = checkCat

Flag to determine if DBCC CHECKCATALOG is executed. checkCat is bit with a default of 0.

@checkDB = checkDB

Flag to determine if DBCC CHECKDB is executed. checkDB is bit with a default of 1. DBCC CHECKDB includes DBCC CHECKALLOC and DBCC CHECKCATALOG. If @checkDB is set to 1, both @checkAlloc and @checkCat will be set to 0.

@checkNdx = checkNdx

Flag to determine if indexes are included for all DBCC commands. checkNdx is bit with a default of 1. If @checkNdx is set to 0, the NOINDEX clause will be used on all relevant DBCC commands.

@dbName = ‘dbName’

The name of the database against which DBCC commands will be executed. ‘dbName’ is sysname with a default of NULL. Use @dbName to isolate DBCC commands to an individual database.

@dbExcludeList = ‘ExcludeDB1, ExcludeDB2, ExcludeDBN‘

Comma-separated list of databases against which DBCC commands will NOT be executed. ‘ExcludeDB1, ExcludeDB2, ExcludeDBN‘ is varchar(max) with a default of NULL. Requires table-valued function dbo.CommaStringTable.

@debugMode = debugMode

Flag to prevent execution of DBCC commands. debugMode is bit with a default of 0. If @debugMode = 1, commands will be printed but not executed.

@maxDuration = maxDuration

Maximum number of hours the procedure is allowed to run. maxDuration is int with a default of 0. If @maxDuration is set to 0, no time limit is imposed and the procedure will be allowed to run to completion.
Note: There is one caveat to consider when using the @maxDuration parameter. The code only checks the time limit in between DBCC CHECKTABLE commands. Therefore, if the time limit is exceeded while a DBCC CHECKTABLE command is running, the command will finish before the procedure exits.

@physOnly = physOnly

Flag to determine if DBCC commands are executed with the PHYSICAL_ONLY clause. physOnly is bit with a default value of 0. See SQL Server Books Online for more information on the PHYSICAL_ONLY clause.

@tableName = ‘tableName’

The name of the table against which DBCC commands will be executed. ‘tableName’ is sysname with a default of NULL. Use @tblName to isolate DBCC commands to an individual table.

@tblExcludeList = ‘ExcludeTable1, ExcludeTable2, ExcludeTableN‘

Comma-separated list of tables against which DBCC commands will NOT be executed. ‘ExcludeTable1, ExcludeTable2, ExcludeTableN‘ is varchar(max) with a default of NULL. Requires table-valued function dbo.CommaStringTable.

@vldbMode = vldbMode

Flag to determine if procedure will run in VLDB mode. vldbMode is bit with a default of 0. Setting this flag to 1 will instruct the procedure to execute individual DBCC CHECKTABLE commands instead of DBCC CHECKDB. If @vldbMode is set to 1, @checkDB will be set to 0. @vldbMode must be set to 1 if either @tableName or @tblExcludeList are set to a value other than NULL.

Examples using CustomDBCC

The following example will execute database-level DBCC commands for all databases on the instance (excluding the MSSQLTips database) without VLDB mode.

EXEC dbo.[CustomDBCC] @checkDB = 1,
      @dbExcludeList = 'MSSQLTips';

An abbreviated version of the output is listed below:

DBCC job on MyServerName started at Dec 30 2014 1:32PM.
The following databases will be skipped: (MSSQLTips).
DBCC CHECKDB against Admin started at Dec 30 2014  1:32PM.
SET QUOTED_IDENTIFIER OFF SET ARITHABORT ON DBCC CHECKDB ([Admin])
WITH ALL_ERRORMSGS, NO_INFOMSGS
DBCC CHECKDB against Admin completed at Dec 30 2014  1:32PM.
DBCC CHECKDB against AdventureWorks2012 started at Dec 30
2014  1:32PM.
SET QUOTED_IDENTIFIER OFF SET ARITHABORT ON DBCC CHECKDB
([AdventureWorks2012]) WITH ALL_ERRORMSGS, NO_INFOMSGS
DBCC CHECKDB against AdventureWorks2012 completed at Dec 30
2014  1:32PM.
DBCC CHECKDB against AdventureWorksDW2012 started at Dec 30
2014  1:32PM.
SET QUOTED_IDENTIFIER OFF SET ARITHABORT ON DBCC CHECKDB
([AdventureWorksDW2012]) WITH ALL_ERRORMSGS, NO_INFOMSGS
DBCC CHECKDB against AdventureWorksDW2012 completed at Dec 30
2014  1:32PM.
DBCC CHECKDB against master started at Dec 30 2014  1:32PM.
SET QUOTED_IDENTIFIER OFF SET ARITHABORT ON DBCC CHECKDB
([master]) WITH ALL_ERRORMSGS, NO_INFOMSGS
DBCC CHECKDB against master completed at Dec 30 2014  1:32PM.
DBCC CHECKDB against model started at Dec 30 2014  1:32PM.
SET QUOTED_IDENTIFIER OFF SET ARITHABORT ON DBCC CHECKDB ([model])
WITH ALL_ERRORMSGS, NO_INFOMSGS
DBCC CHECKDB against model completed at Dec 30 2014  1:32PM.
DBCC CHECKDB against msdb started at Dec 30 2014  1:32PM.
SET QUOTED_IDENTIFIER OFF SET ARITHABORT ON DBCC CHECKDB ([msdb])
WITH ALL_ERRORMSGS, NO_INFOMSGS
DBCC CHECKDB against msdb completed at Dec 30 2014  1:32PM.
DBCC CHECKDB against Northwind started at Dec 30 2014 
1:32PM.
SET QUOTED_IDENTIFIER OFF SET ARITHABORT ON DBCC CHECKDB
([Northwind]) WITH ALL_ERRORMSGS, NO_INFOMSGS
DBCC CHECKDB against Northwind completed at Dec 30 2014 
1:32PM.
DBCC CHECKDB against pubs started at Dec 30 2014  1:32PM.
SET QUOTED_IDENTIFIER OFF SET ARITHABORT ON DBCC CHECKDB ([pubs])
WITH ALL_ERRORMSGS, NO_INFOMSGS
DBCC CHECKDB against pubs completed at Dec 30 2014  1:32PM.
DBCC job on MyServerName ended at Dec 30 2014 1:32PM.

The next example executes DBCC commands against the AdventureWorks2012 database in VLDB mode while excluding the ErrorLog table.

EXEC dbo.[CustomDBCC] 
          @checkAlloc = 1,
          @checkCat = 1,
          @dbName = 'AdventureWorks2012',
          @tblExcludeList = 'ErrorLog',
          @vldbMode = 1;

An abbreviated version of the output is listed below:

DBCC job on MyServerName started at Dec 30 2014 1:38PM.
DBCC job will execute for a single database {AdventureWorks2012}
DBCC CHECKALLOC against AdventureWorks2012 started at Dec 30
2014  1:38PM.
SET QUOTED_IDENTIFIER OFF SET ARITHABORT ON DBCC CHECKALLOC
([AdventureWorks2012]) WITH ALL_ERRORMSGS, NO_INFOMSGS
DBCC CHECKALLOC against AdventureWorks2012 completed at Dec 30
2014  1:38PM.
DBCC CATALOG against AdventureWorks2012 started at Dec 30
2014  1:38PM.
SET QUOTED_IDENTIFIER OFF SET ARITHABORT ON DBCC CHECKCATALOG
([AdventureWorks2012]) WITH NO_INFOMSGS
DBCC CATALOG against AdventureWorks2012 completed at Dec 30
2014  1:38PM.
The following tables will be skipped for all databases:
(ErrorLog).
SET QUOTED_IDENTIFIER OFF SET ARITHABORT ON DBCC CHECKTABLE
('[AdventureWorks2012].[dbo].[AWBuildVersion]') WITH
ALL_ERRORMSGS, NO_INFOMSGS
SET QUOTED_IDENTIFIER OFF SET ARITHABORT ON DBCC CHECKTABLE
('[AdventureWorks2012].[dbo].[DatabaseLog]') WITH ALL_ERRORMSGS,
NO_INFOMSGS
SET QUOTED_IDENTIFIER OFF SET ARITHABORT ON DBCC CHECKTABLE
('[AdventureWorks2012].[HumanResources].[Department]') WITH
ALL_ERRORMSGS, NO_INFOMSGS
SET QUOTED_IDENTIFIER OFF SET ARITHABORT ON DBCC CHECKTABLE
('[AdventureWorks2012].[HumanResources].[Employee]') WITH
ALL_ERRORMSGS, NO_INFOMSGS
SET QUOTED_IDENTIFIER OFF SET ARITHABORT ON DBCC CHECKTABLE
('[AdventureWorks2012].[HumanResources].[EmployeeDepartmentHistory]')
WITH ALL_ERRORMSGS, NO_INFOMSGS
SET QUOTED_IDENTIFIER OFF SET ARITHABORT ON DBCC CHECKTABLE
('[AdventureWorks2012].[HumanResources].[EmployeePayHistory]')
WITH ALL_ERRORMSGS, NO_INFOMSGS
SET QUOTED_IDENTIFIER OFF SET ARITHABORT ON DBCC CHECKTABLE
('[AdventureWorks2012].[HumanResources].[JobCandidate]') WITH
ALL_ERRORMSGS, NO_INFOMSGS
...
SET QUOTED_IDENTIFIER OFF SET ARITHABORT ON DBCC CHECKTABLE
('[AdventureWorks2012].[Sales].[Store]') WITH ALL_ERRORMSGS,
NO_INFOMSGS
DBCC job on MyServerName ended at Dec 30 2014 1:38PM.

When the dbo.CustomDBCC procedure executes in VLDB mode, it writes the output to a table named dbo.CheckTableStatus in the same database, which the procedure will create if it doesn’t already exist. This table is also used to track the progress when executing the procedure in VLDB mode while imposing a limit on the procedure run time.

In one of the environments we support, we have two SQL Server Agent Jobs that execute this procedure. The first job runs on the weekend when our maintenance window is a bit longer. This job executes DBCC CHECKALLOC and DBCC CHECKCATALOG commands at the database level and then DBCC CHECKTABLE commands for a duration of 4 hours:

EXEC dbo.[CustomDBCC] 
          @checkAlloc = 1,
          @checkCat = 1,
          @checkDB = 0,
          @maxDuration = 4,
          @vldbMode = 1;

The second job runs during the week when our maintenance window is shorter. This job continues executing DBCC CHECKTABLE commands for a duration of two hours:

EXEC dbo.[CustomDBCC] @maxDuration = 2,
      @vldbMode = 1;

Conclusion

The code and methodology presented in this tip shows one way to distribute DBCC commands across multiple maintenance windows for very large databases.

Next Steps

Review the larger databases in your environment.
Consider scheduling a SQL Server agent job to execute dbo.CustomDBCC.
Refer to the DBCC CHECKDB and DBCC CHECKTABLE entries in SQL Server Books Online for more information on the DBCC commands referenced in this tip.
Check out other tips related to DBCC operations.

Resources

Community

Subscribe