Inserting DBCC CHECKDB Results Into A Table

Hello again and welcome back!

I thought today’s posting to my blog should cover how to get the results of DBCC CHECKDB into a table.  Unfortunately, Microsoft does not allow you easily per se to insert the results into a table so that you might be able to generate administrative reports off of.  Well in my case that is exactly why I developed this process… to generate administrative reports off of.

Now what I did as you will see in the T-SQL scripts below, I created a table based upon the result set of the DBCC CHECKDB execution.  Once I had the column names and data types I went ahead and created the table.  The next step was to write a stored procedure that would iterate through all databases on the server, run the DBCC CHECKDB statement and record the output into the table I just created.  So after much testing and a little taste of success I give you the scripts below.  I call the stored procedure in a SQL Server Agent job and schedule the job to run in the early hours of the morning and ’off peak’ hours.  So in the morning I can review the results of DBCC CHECKDB from the previous night.  Now you can modify this process to just append the data or just truncate it like I did.  I hope you find this code helpful!… Let us know!…

Here is the table script (this script is for the SQL Server 2005 CHECKDB output)

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CheckDBResult](
 [ServerName] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 [Error] [int] NULL,
 [Level] [int] NULL,
 [State] [int] NULL,
 [MessageText] [varchar](7000) COLLATE SQL_Latin1_General_CP1_CI_AS 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,
 [insert_date] [datetime] NOT NULL CONSTRAINT [DF_CheckDBResult_insert_date]  DEFAULT (getdate())
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

Here is the CREATE PROCEDURE script that utilizes the CHECKDB 2005 output and here is the T-SQL syntax to execute the procedure: EXEC mDBMaintenance ‘Lumiere’ <- server name to run the script against

EXEC Admin_GetCheckDBResults ’server name’

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Admin_GetCheckDBResults]
 @ServerName VARCHAR(100)
 
AS
DECLARE @Database SYSNAME
 
– Remove Previous Weeks Results
TRUNCATE TABLE DBA_Admin.dbo.CheckDBResult
 
DECLARE cDatabases CURSOR FOR
SELECT [name] from master..sysdatabases AS sdb
WHERE sdb.[name] NOT IN (‘tempdb’)
OPEN cDatabases
FETCH FROM cdatabases INTO @Database
WHILE ( @@FETCH_STATUS = 0)
BEGIN
 SET NOCOUNT ON
 
 INSERT INTO DBA_Admin.dbo.CheckDBResult(
  Error,
  [Level],
  [State],
  MessageText,
  RepairLevel,
  [Status],
  [DbId],
  Id,
  IndId,
  PartitionId,              — specific to SQL Server 2005, remove for SQL Server 2000 results
  AllocUnitId,             — specific to SQL Server 2005, remove for SQL Server 2000 results
  [File],
  Page,
  Slot,
  RefFile,
  RefPage,
  RefSlot,
  Allocation
  )
 EXEC (‘DBCC CHECKDB(”’ + @Database + ”’) WITH TABLERESULTS’)
 
FETCH FROM cdatabases INTO @Database
END
CLOSE cDatabases
DEALLOCATE cDatabases
 
– Remove all details except CHECKDB information
DELETE
FROM DBA_Admin.dbo.CheckDBResult
WHERE MessageText NOT LIKE ‘CHECKDB%’
 
– Update server Name
UPDATE DBA_Admin.dbo.CheckDBResult
SET ServerName = @ServerName
 
– Select Statment To Return Rows
SELECT ServerName,
 MessageText,
 Error,
 [Level],
 [State],
 RepairLevel,
 [Status],
 [DbId],
 Id,
 IndId,
 PartitionId,          – specific to SQL Server 2005, remove for SQL Server 2000 results
 AllocUnitId,         – specific to SQL Server 2005, remove for SQL Server 2000 results
 [File],
 Page,
 Slot,
 RefFile,
 RefPage,
 RefSlot,
 Allocation,
 Insert_Date
FROM DBA_Admin.dbo.CheckDBResult
GO

Now as you can see I only want to see the last line in the DBCC CHECKDB command… however, you can modify it keep all the detail from the execution.  I hope this code helps and let us know how you are using it!