Learn more about SQL Server tools

solving sql server problems for millions of dbas and developers since 2006
join MSSQLTips for free SQL Server tips

































Top SQL Server Tools






















Capture and Store SQL Server Database Integrity History using DBCC CHECKDB

MSSQLTips author Robert Pearl By:   |   Read Comments (16)   |   Related Tips: More > Database Consistency Checks 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.

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.

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

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'

navigate ssms to the jobs tab under sql server agent

Name the job appropriately, and select 'Steps' and then click 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 , and then goto 'Schedule' to schedule the job.

in the command box type exec usp_checkdbintegrity my database

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.

SELECT Error, LEVEL, DB_NAME(dbid) AS DBName, OBJECT_NAME(id,dbid) AS ObjectName, Messagetext, TimeStamp
FROM
dbcc_history

The output should look like this:

query the table dbcc_history to see your data

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

refine your query as needed

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.


Last Update: 3/2/2011


About the author
MSSQLTips author Robert Pearl
Robert Pearl is a SQL MVP, and President of Pearl Knowledge Solutions, Inc., offering SQLCentric monitoring and DBA services.

View all my tips
Related Resources


print tip Print  
Become a paid author




Recommended For You








Learn more about SQL Server tools
Comments and Feedback:
Wednesday, March 02, 2011 - 2:17:16 PM - Dave Stokes Read The Tip

Thanks for the great post.  I ran into an error when I tried to run the SP ("Failed to convert Bigint to int").  I changed the datatype for the PartionID and the AllocUnitID to bigint and it worked great.  Thanks again!


Thursday, March 03, 2011 - 2:40:39 PM - Kay Read The Tip

Nice post


Thursday, March 03, 2011 - 4:44:27 PM - Martin Lundblad Read The Tip

Thanks for the post.  This gives me a good place to start.  There is a broiken link at the bottom of the page. (SQL Server Database Integrity Checks Checklist)


Thursday, March 03, 2011 - 4:47:21 PM - admin Read The Tip

The link has been fixed.

Thanks for pointing this out.


Thursday, April 14, 2011 - 12:07:41 PM - Fabrizio Arrigucci Read The Tip

Thank you is very helpful.

I ran into an error when i tried to run the SP. If you have a corrput Database the Column [RepairLevel] has an invalid Datatype. The Datatype should be varchar. The Output of DBCC for the mentioned Column is 'REPAIR_ALLOW_DATA_LOSS or REPAIR_FAST or REPAIR_REBUILD'

Thanks


Sunday, February 12, 2012 - 9:04:27 PM - JPG Read The Tip

Probably most people will say -  this guy is smoking pot.  Perfectly acceptable to me :-) 

 

But when I run this script -- why does the cursor skip some Databases.

We have large DBs and all DBs together take about 24 - 36 hours to complete (if it ever did).

But the script skips some DBs.  When I comment out the 'Execute DBCC' and do a Print DBName (variable) -- I get all DB names.  Funny isnt it?

But any reason anybody can think ?

 


Tuesday, March 20, 2012 - 7:42:58 PM - bpnchan Read The Tip

 

HI there,

I am running SQL Server 2008 and implemented this stored procedure, and would like to share some experience. Overall, I love the solution that it record the results, if a few concerns is addressed, this is just prefect in my opinion.

I got the same issue here like JPG. Sometime, in random maner, a few of databases was skipped for the check. I am thinking if this is to do with the cursor and the timing when it was executed. I am still investigating it because it does not happen regaularly.

On the other hand, I have a rare issue returned over the runtime for some specific databases, where the stored procedure failed on the following error.

[SQLSTATE 01000] (Message 2528)  Error converting data type bigint to int. [SQLSTATE 42000] (Error 8114)  DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Error 2528).  The step failed.

The difference on these databases are that they are at the compatability level for sql 2000 (80). Have a change request to increase them all to SQL 2008 (100), so will update here once this in place.

bpnchan

 


Wednesday, July 04, 2012 - 8:50:27 AM - Sharon Smith Read The Tip

 

Thank you for this.  I was struggling to find how to capture the output to a file.

Regards,

Sharon


Thursday, July 05, 2012 - 2:54:46 PM - rick Read The Tip

thanks much for this.  my only suggestion would be the bigint datatype fix (which I had to do because corrupt dbs were not writing to the history table) and variables/code for logging to a remote database rather than localhost.


Wednesday, July 11, 2012 - 7:17:53 AM - Morne Read The Tip

 

 

Hello,

 

New SQL and need to setup a maintenance task. Have tried to create that table but no luck, Please what am I doing wrong.

 

 

M

 

 


Friday, August 31, 2012 - 12:30:10 PM - Vijay Read The Tip

hiiiiiiiiiiiiiiiiiiii

 

 I am sql dba beginer.  pls forward  frequntly using DBCC COMMANDS with syntax. 

 

Thank you


Thursday, January 10, 2013 - 10:13:03 AM - Jim Read The Tip

for Sql Server 2012

 

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

 

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], [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], [IndexId], PartitionId, AllocUnitId,[RidDbid], [RidPruid],[File], Page, Slot, [RefDbId], [RefPruId], RefFile, RefPage,
       RefSlot,Allocation)
   EXEC ('dbcc checkdb(''' + @database_name + ''') with tableresults')
GO

 


Tuesday, January 15, 2013 - 12:06:34 PM - pjc Read The Tip

Hello

The RepairLevel field in the DBCC_HISTORY table is created as an INT. When the DBCC CHECKDB command runs if there are no problems then this field remains as a NULL, so it doesn't really matter. However, if the DBCC CHECKDB finds any corruption in the database, it will try to insert a VARCHAR value into the RepairLevel field which will cause the insert to fail.

Values which go into the RepairLevel field are along the lines of...

repair_rebuild
repair_fast

A couple of the other fields have also been created as INT and should be BIGINT.

Great document though.

Thanks


Sunday, January 27, 2013 - 6:24:58 AM - Imrane Read The Tip

Hi there

Works fine on v2012 with extra column --> [ObjectId] [int] NULL,

v2012

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

 

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

 


Wednesday, September 18, 2013 - 8:32:36 PM - Kurt Read The Tip

Create the table as follows to clean up errors when corruption is detected:

 

CREATE TABLE [dbo].[dbcc_history](
[Error] [int] NULL,
[Level] [int] NULL,
[State] [int] NULL,
[MessageText] [varchar](7000) NULL,
[RepairLevel] [nvarchar] (25) NULL,
[Status] [int] NULL,
[DbId] [int] NULL,
[Id] [int] NULL,
[IndId] [int] NULL,
[PartitionID] [bigint] NULL,
[AllocUnitID] [bigint] 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


Tuesday, November 26, 2013 - 10:44:52 AM - sqlep Read The Tip

as would apply with checktable? I want to save the results in a table checktable



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates

Signup for our newsletter


Comments
*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.