By: Robert Pearl | Updated: 2018-05-03 | Comments (37) | Related: > Database Console Commands DBCCs
Problem
We've all used the command DBCC CHECKDB from time to time in our DBA careers to verify database integrity and repair corrupt databases. The latest version of this command checks the logical and physical integrity of all objects in the specified database by performing the following operations:
- Runs DBCC CHECKALLOC on the database.
- Runs DBCC CHECKTABLE on every table and view in the database.
- Runs DBCC CHECKCATALOG on the database.
- Validates the contents of every indexed view in the database.
- Validates link-level consistency between table metadata and file system directories and files when storing varbinary(max) data in the file system using FILESTREAM.
- Validates the Service Broker data in the database.
This means that the DBCC CHECKALLOC, DBCC CHECKTABLE, or DBCC CHECKCATALOG commands do not need to be run when you use DBCC CHECKDB.
This command is often run ad-hoc in the event of a corrupt database, but it is also recommended that you run it against your database(s) as part of your regular database maintenance routine. Although you can use the Maintenance Plan Wizard to Check Database Integrity, it does not give you the flexibility to store and query historical data.
The standard output can be quite extensive, not formatted for easy analysis, and often is not persisted in any useful way for historical analysis.
Solution
To insert the DBCC results into a readable and manageable format, you can use the 'WITH TABLERESULTS' option for DBCC CHECKDB to get an output that can be piped to a table for analysis. This option has been implemented for most of the DBCC statements in versions SQL 2000 and above. My script was created on a SQL Server 2008 instance. There are several diagnostic and repair options that you can use and you can read about them here: DBCC CHECKDB.
The purpose of this article is to enable you to capture and store database integrity history in a custom table that can be used for historical analysis on an on-going basis as part of your maintenance routine.
Collect The Data
A stored procedure will be created that can be scheduled as a SQL Agent job that writes to a history table where the DBCC CHECKDB output is stored and can later be queried.
First, you will want to run the following script to create the table 'dbcc_history' in some database used for DBA tasks and maintenance. If you do not have a database for this, then simply create one.
There are two versions of the collection table and code depending on version of SQL Server, so refer to the correct version you are using below.
Code to Collect DBCC CheckDB data for SQL Server 2005, 2008, and 2008 R2
All the columns here, except one, match the definition of the columns output in DBCC CHECKDB WITH TABLERESULTS. I added the [TimeStamp] column with a default constraint of getdate(). This will allow each run to be stored with the current time and date automatically upon insert of the rows.
-- table structure for SQL Server 2005, 2008 and 2008 R2 CREATE TABLE [dbo].[dbcc_history]( [Error] [int] NULL, [Level] [int] NULL, [State] [int] NULL, [MessageText] [varchar](7000) NULL, [RepairLevel] [int] NULL, [Status] [int] NULL, [DbId] [int] NULL, [Id] [int] NULL, [IndId] [int] NULL, [PartitionID] [int] NULL, [AllocUnitID] [int] NULL, [File] [int] NULL, [Page] [int] NULL, [Slot] [int] NULL, [RefFile] [int] NULL, [RefPage] [int] NULL, [RefSlot] [int] NULL, [Allocation] [int] NULL, [TimeStamp] [datetime] NULL CONSTRAINT [DF_dbcc_history_TimeStamp] DEFAULT (GETDATE()) ) ON [PRIMARY] GO
Next, we will need to create our stored procedure called 'usp_CheckDBIntegrity'. In this example, the stored proc accepts one parameter - '@database_name'. If you specify a database name during execution, then the script will run DBCC CHECKDB against only that particular database. By simply executing the procedure as 'EXEC usp_CheckDBIntegrity', you can run the dbcc against ALL databases on the server.
In addition, this script excludes system databases, only runs against databases that are online, excludes snapshot databases, and excludes 'READ-ONLY' databases. You can modify the script's behavior by commenting out the appropriate line.
NOTE: It is important to test this in order to understand the time it takes to run against any one or all databases. DO NOT run this on your production environment during normal operating hours! This process should be run off-hours during an available maintenance window.
/****** Object: StoredProcedure [dbo].[usp_CheckDBIntegrity] Created by Robert Pearl ******/
/****** for SQL Server 2005, 2008 and 2008 R2 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[usp_CheckDBIntegrity]
@database_name SYSNAME=NULL
AS
IF @database_name IS NULL -- Run against all databases
BEGIN
DECLARE database_cursor CURSOR FOR
SELECT name
FROM sys.databases db
WHERE name NOT IN ('master','model','msdb','tempdb')
AND db.state_desc = 'ONLINE'
AND source_database_id IS NULL -- REAL DBS ONLY (Not Snapshots)
AND is_read_only = 0
OPEN database_cursor
FETCH next FROM database_cursor INTO @database_name
WHILE @@FETCH_STATUS=0
BEGIN
INSERT INTO dbcc_history ([Error], [Level], [State], MessageText, RepairLevel, [Status],
[DbId], Id, IndId, PartitionId, AllocUnitId, [File], Page, Slot, RefFile, RefPage,
RefSlot,Allocation)
EXEC ('dbcc checkdb(''' + @database_name + ''') with tableresults')
FETCH next FROM database_cursor INTO @database_name
END
CLOSE database_cursor
DEALLOCATE database_cursor
END
ELSE -- run against a specified database (ie: usp_CheckDBIntegrity 'DB Name Here'
INSERT INTO dbcc_history ([Error], [Level], [State], MessageText, RepairLevel, [Status],
[DbId], Id, IndId, PartitionId, AllocUnitId, [File], Page, Slot, RefFile, RefPage, RefSlot,Allocation)
EXEC ('dbcc checkdb(''' + @database_name + ''') with tableresults')
GO
Code to Collect DBCC CheckDB data for SQL Server 2012, 2014, 2016 and 2017
Here is the table structure for later versions of SQL Server.
-- table structure for SQL Server 2012, 2014, 2016 and 2017 CREATE TABLE [dbo].[dbcc_history]( [Error] [int] NULL, [Level] [int] NULL, [State] [int] NULL, [MessageText] [varchar](7000) NULL, [RepairLevel] [int] NULL, [Status] [int] NULL, [DbId] [int] NULL, [DbFragId] [int] NULL, [ObjectId] [int] NULL, [IndexId] [int] NULL, [PartitionID] [int] NULL, [AllocUnitID] [int] NULL, [RidDbId] [int] NULL, [RidPruId] [int] NULL, [File] [int] NULL, [Page] [int] NULL, [Slot] [int] NULL, [RefDbId] [int] NULL, [RefPruId] [int] NULL, [RefFile] [int] NULL, [RefPage] [int] NULL, [RefSlot] [int] NULL, [Allocation] [int] NULL, [TimeStamp] [datetime] NULL CONSTRAINT [DF_dbcc_history_TimeStamp] DEFAULT (GETDATE()) ) ON [PRIMARY] GO
Here is the code to collect the data.
/****** Object: StoredProcedure [dbo].[usp_CheckDBIntegrity] Created by Robert Pearl ******/
/****** for SQL Server 2012, 2014, 2016 and 2017 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[usp_CheckDBIntegrity]
@database_name SYSNAME=NULL
AS
IF @database_name IS NULL -- Run against all databases
BEGIN
DECLARE database_cursor CURSOR FOR
SELECT name
FROM sys.databases db
WHERE name NOT IN ('master','model','msdb','tempdb')
AND db.state_desc = 'ONLINE'
AND source_database_id IS NULL -- REAL DBS ONLY (Not Snapshots)
AND is_read_only = 0
OPEN database_cursor
FETCH next FROM database_cursor INTO @database_name
WHILE @@FETCH_STATUS=0
BEGIN
INSERT INTO dbcc_history ([Error], [Level], [State], MessageText, RepairLevel, [Status],
[DbId], DbFragId, ObjectId, IndexId, PartitionId, AllocUnitId, RidDbId, RidPruId, [File], Page, Slot,
RefDbId, RefPruId, RefFile, RefPage, RefSlot,Allocation)
EXEC ('dbcc checkdb(''' + @database_name + ''') with tableresults')
FETCH next FROM database_cursor INTO @database_name
END
CLOSE database_cursor
DEALLOCATE database_cursor
END
ELSE -- run against a specified database (ie: usp_CheckDBIntegrity 'DB Name Here'
INSERT INTO dbcc_history ([Error], [Level], [State], MessageText, RepairLevel, [Status],
[DbId], DbFragId, ObjectId, IndexId, PartitionId, AllocUnitId, RidDbId, RidPruId, [File], Page, Slot,
RefDbId, RefPruId, RefFile, RefPage, RefSlot,Allocation)
EXEC ('dbcc checkdb(''' + @database_name + ''') with tableresults')
GO
Collect the Data
Once the table and stored procedure are created, you can run the proc with the following code:
EXEC usp_CheckDBIntegrity 'MyDatabase' -- specifies particular database, otherwise ALL DBS GO
Schedule a Job
For recommended on-going database maintenance, navigate SSMS to the 'Jobs' folder under SQL Server Agent, and right-click 'New Job'
Name the job appropriately, and select 'Steps' and then click NEW at the bottom-left hand corner. The 'New Job Step' window will open. Give the step a name. As shown below, select the database where you created the stored proc, and in the command box type "Exec usp_CheckDBIntegrity 'MyDatabase'", substituting this for the db you want to schedule the checkdb to run against. Click OK, and then goto 'Schedule' to schedule the job.
Analyze Data
Once the job has completed, you should see data in your table 'dbcc_history'. You can query the table to show the pertinent info. I have included here, the error no, severity level, the database and object name, as well as the timestamp column.
-- for SQL 2005, 2008 and 2008 R2 SELECT Error, LEVEL, DB_NAME(dbid) AS DBName, OBJECT_NAME(id,dbid) AS ObjectName, Messagetext, TimeStamp FROM dbcc_history -- for SQL 2012, 2014, 2016 and 2017 SELECT Error, LEVEL, DB_NAME(dbid) AS DBName, OBJECT_NAME(objectid,dbid) AS ObjectName, Messagetext, TimeStamp FROM dbcc_history
The output should look like this:
If there are no errors, it will return the number of rows and pages in each object otherwise you will see the error messages for that object.
You can refine your query and filter out data for a particular error, database, time range, object, etc. If you wanted to simply return the overall outcome of the dbcc checkdb, you can query 'where error=8989'
SELECT Error, LEVEL, DB_NAME(dbid) AS DBName, Messagetext, TimeStamp FROM dbcc_history WHERE Error = 8989
Next Steps
- Read this previous tip, SQL Server Database Integrity Checks Checklist, which will address some of the items you should consider when putting your integrity check process in place.
- SQL Server 2005 offers a new option called 'DATA_PURITY' to the DBCC CHECKDB and DBCC CHECKTABLE commands. Read about it here.
- You can also refer to the Microsoft TechNet Reference on DBCC CHECKDB.
- You can actually download sample corrupt databases by clicking on the highlighted link and run the Check Database Integrity process against them.
About the author
Robert Pearl is a SQL MVP, and President of Pearl Knowledge Solutions, Inc., offering SQLCentric monitoring and DBA services.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2018-05-03
