Search

Monday, November 8, 2010

Inserting DBCC CHECKDB Results Into A Table

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!

No comments:

Post a Comment