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
GOProcedure 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;
GOSyntax 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.
Mike has been a SQL Server DBA since 1999. His main areas of interest are monitoring and the automation of administrative tasks.
Hi, I came across your code and liked it. I did some testing and one thing stood out. If there is an error, where is that logged? I don’t see a place for it. Am I missing something?
Thanks