Cut to the chase - just tell me if there are any errors in my DBCC CHECKDB output file
SET NOCOUNT ON
/****CommentStart
Author: Kevin Foreman
Date: 15-01-2007
Description:
Uploads a specified DBCC CHECKDB output file
into a table and returns jobstep start and summary rows
Account executing muct have access to exec
extended procs and to the specified file
EDIT FOR SQL2000
replace temp table variable
with temp table
Input:
@file >> file name
Output:
summary data
Maintained:
CommentEnd****/
DECLARE @DBCC_CHECKDB_ERRORS table
--CREATE TABLE #DBCC_CHECKDB_ERRORS
( aLine nvarchar(2000) NULL,
Database_Name sysname NULL,
Allocation_Errors int NULL,
Consistency_Errors int NULL
)
DECLARE
@s nvarchar(2000) ,
@i int,
@file nvarchar(1000)
SELECT
@file = 'C:\Datadump\Empty\PRDDBA_DBCC_CheckDB_TLA.log'
SELECT
@s = 'type "' + @file + '"'
BEGIN
EXEC master.dbo.xp_fileexist @file,@i output
IF @i != 1
RAISERROR ('ERROR in detect input file',16,1)
END
BEGIN
INSERT @DBCC_CHECKDB_ERRORS (aLine)
-- INSERT #DBCC_CHECKDB_ERRORS (aLine)
EXEC @i = master.dbo.xp_cmdshell @s
IF @i != 0
RAISERROR ('ERROR in exec command shell',16,1)
END
UPDATE
@DBCC_CHECKDB_ERRORS
-- #DBCC_CHECKDB_ERRORS
SET
Database_Name = SUBSTRING( aLine, CHARINDEX('errors in database ',aLine)+20, CHARINDEX('''.',aLine) -(CHARINDEX('errors in database ',aLine)+20) ) ,
Allocation_Errors = SUBSTRING( aLine, 15, CHARINDEX('allocation errors',aLine)-15 ) ,
Consistency_Errors = SUBSTRING( aLine, CHARINDEX('allocation errors and ',aLine)+22, CHARINDEX('consistency errors',aLine)-(CHARINDEX('allocation errors and ',aLine)+22) )
WHERE
aLine LIKE '%CHECKDB FOUND%'
SELECT
Database_Name AS 'Databse_Name' ,
Allocation_Errors AS 'Allocation_Errors',
Consistency_Errors AS 'Consistency_Errors'
--,aLine AS 'Output_Text'
FROM
@DBCC_CHECKDB_ERRORS
WHERE
UPPER(aLine) Like '%CHECKDB FOUND%' -- what you want to see
--DROP TABLE #DBCC_CHECKDB_ERRORS
dumping ground for code that might be useful sometime, somewhere. Beware the formatting mangle of the blog post...
Sunday, March 4, 2007
Subscribe to:
Post Comments (Atom)
Blog Archive
-
▼
07
(13)
-
▼
Mar
(11)
- TSQL: Restore commands for al DBs
- TSQL: Generate sp_configure commands
- TSQL: Generate Make Directory (MD) commands
- TSQL: SQLServerAgent job history
- TSQL: Rowcount for all tables
- SQL-DMO: Script all DB objects, all DBs
- TSQL: SQLServerAgent job detail summary
- DBCC CHECKDB: Summarise output file
- TSQL: Get drive space
- TSQL: Get database space info
- SQL-DMO: SQLServerAgent Jobs to files
-
▼
Mar
(11)
No comments:
Post a Comment