SQL Server DBCC CHECKDB, CHECKCATALOG and CHECKALLOC for VLDBs


By:   |   Updated: 2015-01-28   |   Comments (9)   |   Related: More > Database Console Commands DBCCs

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.

check Table ID database_name schemaName tableName proc Flag startDate endDate
3 AdventureWorks2012 dbo AWBuildVersion 1 Dec 30 2014 1:38:30:227PM Dec 30 2014 1:38:30:320PM
16 AdventureWorks2012 dbo DatabaseLog 1 Dec 30 2014 1:38:30:323PM Dec 30 2014 1:38:30:430PM
17 AdventureWorks2012 HumanResources Department 1 Dec 30 2014 1:38:30:520PM Dec 30 2014 1:38:30:610PM
20 AdventureWorks2012 HumanResources Employee 1 Dec 30 2014 1:38:30:613PM Dec 30 2014 1:38:30:723PM
21 AdventureWorks2012 HumanResources EmployeeDepartment History 1 Dec 30 2014 1:38:30:727PM Dec 30 2014 1:38:30:813PM
22 AdventureWorks2012 HumanResources EmployeePayHistory 1 Dec 30 2014 1:38:30:817PM Dec 30 2014 1:38:30:900PM
25 AdventureWorks2012 HumanResources JobCandidate 1 Dec 30 2014 1:38:30:903PM Dec 30 2014 1:38:31:007PM
65 AdventureWorks2012 Sales Store 1 Dec 30 2014 1:38:39:880PM Dec 30 2014 1:38:39:980PM

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.


Last Updated: 2015-01-28


get scripts

next tip button



About the author
MSSQLTips author Mike Eastland Mike Eastland has been a SQL Server DBA since 1999. His main areas of interest are monitoring and the automation of administrative tasks.

View all my tips
Related Resources




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Thursday, March 26, 2015 - 9:55:52 AM - Dennis Back To Top

Ok - I'll change that and test this out.  Thanks for responding.


Thursday, March 26, 2015 - 9:12:27 AM - Mike Back To Top

Dennis - Without testing it, that solution sounds viable to me.  Also, I think there is one other section of code that needs to be updated to ensure that the excluded tables are not always skipped.  At the end of the procedure, there is an update statement that is conditional on the value of @debugMode.  I think the update statement should be executed regardless of the value of debugMode because the procFlag field is set to NULL when tables are exlcuded OR if the procedure is executed with @debugMode = 1.  So it really should execute the update regardless.


Wednesday, March 25, 2015 - 5:07:36 PM - Dennis Back To Top

After reveiwing your code more, I came up with a solution.  Please let me know if you see an issue.   When @restart = 1, I removed the check against the exclude list.  That way, all tables will get into the initial pool.  The exclude list will only come into play when actually looping through to run the dbcc on the table.  I can leave my large tables for the weekend.  If the sp is finished will all the other tables and runs, it should just exit with doing no work because the checkTableStatus will have tables with procFlag = 0 still, until the weekend run.

IF @restart = 1

INSERT INTO [dbo].[CheckTableStatus] ([databaseName], [schemaName], [tableName], [procFlag])

SELECT DatabaseName, SchemaName, TableName, 0

FROM @check_tbl c

WHERE LOWER(c.tableName) = LOWER(ISNULL(@tableName, c.tableName));


Wednesday, March 25, 2015 - 4:25:42 PM - Dennis Back To Top

Thanks,  I think I understand now.  I basically need to exclude them, let everything process.  Then run my big tables one by one.  Then when the week starts over, start the entire process again.  Does that sound correct?


Wednesday, March 25, 2015 - 12:07:18 PM - Mike Back To Top

Dennis - Thanks for reading the tip.  The answer to your question depends on whether or not there is remaining data to process in the status table.  The only time the @tblExcludeList parameter comes into play is where the procedure runs without any additional data to process.  That is when the status table is populated with the list of candidate tables.  Here is the section of code from the procedure to illustrate:

-- 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

In the case of your example, unless the @restart flag is set to 1 on a day when the large tables are NOT excluded, those tables won't make it into the list of tables to be processed.

Hope this helps.


Tuesday, March 24, 2015 - 8:31:56 PM - Dennis Back To Top

This approach is great.  What happens though if you exclude large tables mon-fri, then on saturday you don't exclude any.  Will it pick up the missed ones?  Does it just use the date fields to figure out what it needs to do?


Thursday, January 29, 2015 - 2:16:52 PM - Renato Back To Top

Interesting approach. I think Yup said have sense after all, but it's apossible and future implementation, don't hurt this nice article in the present  moment.

[]'s


Wednesday, January 28, 2015 - 2:18:10 PM - Rick Willemain Back To Top

Excellent article describing an approach to managing within large environments. Thank you !!!


Wednesday, January 28, 2015 - 9:33:22 AM - Yup Back To Top

Nice attempt but when it comes to VLDB, you should also include a bit about error 665, format options with /L and the Sparse file size limitations etc...



download


get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools