April 5, 2015 at 9:00 pm
Hi Guys
I have written a script to do dbcc checkdb agaianst all my database. Interesting part is:
1. If the database is corrupted, my script is skipping the required check and no data is being captured to the table "dbcc_history"
2. The data is also not being inserted to the final table "dbcc_check_history"
3. IF I enable "@PHYSICAL_ONLY" or "@TABLOCK" , occasionally does that work.
Any suggestion please?
USE <database>
GO
ALTER PROC [DBO].[USP_CHECKDBINTEGRITY]
(@DATABASE_NAME SYSNAME=NULL,
@PHYSICAL_ONLY BIT = 0,
--DETECT TORN PAGES, CHECKSUM FAILURES, AND COMMON HARDWARE FAILURES THAT CAN COMPROMISE A USER'S DATA.
--IF PHYSICAL_ONLY IS SPECIFIED, COLUMN-INTEGRITY CHECKS ARE NOT PERFORMED.
@TABLOCK BIT = 0)
--TABLOCK WILL CAUSE DBCC CHECKDB TO RUN FASTER ON A DATABASE UNDER HEAVY LOAD, BUT DECREASES THE CONCURRENCY.
AS
BEGIN
BEGIN try
SET nocount ON
DECLARE @COUNT INT,
@DB INT;
-- PRINT @DATABASE_NAME;
PRINT 'Loop1'
IF NOT EXISTS (SELECT *
FROM sys.objects
WHERE object_id = Object_id(N'[DBO].[DBCC_HISTORY]')
AND type IN ( N'U' ))
BEGIN
RAISERROR (
'THE TABLE [DBCC_HISTORY] DOES NOT EXIST IN THE DATABASE',
15,10
)
RETURN;
END;
PRINT 'Loop2'
SELECT @COUNT = Count(*)
FROM sys.tables AS t
INNER JOIN sys.columns C
ON t.object_id = c.object_id
WHERE t.NAME LIKE '%DBCC_HISTORY%';
IF NOT EXISTS (SELECT *
FROM sys.objects
WHERE object_id = Object_id(N'[DBO].[DBCC_HISTORY]')
AND type IN ( N'U' ))
BEGIN
RAISERROR (
'THE TABLE [DBCC_HISTORY] DOES NOT EXIST IN THE DATABASE',
15,10
)
RETURN;
END;
PRINT 'Loop3'
IF @COUNT <> 24
BEGIN
RAISERROR(
'THE TABLE [DBCC_HISTORY] DOES NOT HAVE CORRECT NUMBER OF COLUMNS ',20 ,-1)
WITH log
RETURN
END;
IF NOT EXISTS (SELECT *
FROM sys.objects
WHERE object_id =
Object_id(N'[DBO].[DBCC_CHECK_HISTORY]')
AND type IN ( N'U' ))
BEGIN
RAISERROR (
'THE TABLE [DBCC_CHECK_HISTORY] DOES NOT EXIST IN THE DATABASE'
,
15,10)
RETURN
END;
PRINT 'Loop4'
SELECT @COUNT = Count(*)
FROM sys.tables AS t
INNER JOIN sys.columns C
ON t.object_id = c.object_id
WHERE t.NAME LIKE '%DBCC_CHECK_HISTORY%';
IF @COUNT <> 12
BEGIN
RAISERROR(
'THE TABLE [DBCC_CHECK_HISTORY] DOES NOT HAVE CORRECT NUMBER OF COLUMNS ',
20
,-1) WITH log
RETURN
END;
PRINT 'Loop5'
IF @DATABASE_NAME IS NULL -- RUN AGAINST ALL DATABASES
BEGIN
PRINT 'Loop6A'
DECLARE database_cursor_name CURSOR FOR
SELECT NAME
FROM sys.databases DB
WHERE NAME NOT IN ( 'MODEL', 'TEMPDB' )
--CHECK ALL DATABASES EXCLUDING MODEL AND TEMPDB
AND db.state_desc = 'ONLINE'
AND source_database_id IS NULL
-- REAL DBS ONLY (NO SNAPSHOTS)
AND is_read_only = 0;
PRINT 'Loop7'
OPEN database_cursor_name
FETCH next FROM database_cursor_name INTO @DATABASE_NAME
IF @@FETCH_STATUS <> 0
PRINT ' <<None>>'
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @sql VARCHAR(max)
SET @sql = 'dbcc checkdb(''' + @DATABASE_NAME
+ ''') with tableresults'
IF @PHYSICAL_ONLY = 1
SET @sql = @sql + ', PHYSICAL_ONLY '
IF @tablock = 1
SET @sql = @sql + ', TABLOCK '
PRINT 'Loop8'
SELECT @sql
PRINT 'loop 12 - Entering records to Staging Table table *** dbcc_history'
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)
--SELECT @sql
EXEC (@sql)
PRINT @DATABASE_NAME
FETCH next FROM database_cursor_name INTO @DATABASE_NAME
END
CLOSE database_cursor_name
DEALLOCATE database_cursor_name
PRINT 'loop 12 - Entering records to Final table *** dbcc_check_history'
INSERT INTO DBCC_CHECK_HISTORY
([dbname],
[objname],
[type_desc],
[indexname],
[allocationtype],
[error],
[level],
[state],
[status],
[repairlevel],
[messagetext],
[timestamp])
SELECT Db_name(dbid) AS dbname,
obj.NAME AS objname,
obj.type_desc AS objtype,
idx.NAME AS indexname,
alu.type_desc AS allocationtype,
cdb.error,
cdb.level,
cdb.state,
cdb.status,
cdb.repairlevel,
messagetext,
cdb.timestamp
FROM dbcc_history AS cdb
LEFT JOIN sys.objects AS obj
ON cdb.objectid = obj.object_id
LEFT JOIN sys.indexes AS idx
ON cdb.objectid = idx.object_id
AND cdb.indexid = idx.index_id
LEFT JOIN sys.allocation_units AS alu
ON cdb.allocunitid = alu.allocation_unit_id
ORDER BY Db_name(dbid),
objtype,
objname,
indexname;
END
ELSE IF ( @DATABASE_NAME IS NOT NULL )
-- RUN AGAINST A SPECIFIED DATABASE (IE: USP_CHECKDBINTEGRITY 'DB NAME HERE' )
BEGIN
DECLARE @NUMBER INT, @number1 INT;
PRINT @NUMBER
SET @NUMBER = (SELECT Count(*)
FROM sys.sysdatabases
WHERE NAME = @DATABASE_NAME)
--PRINT @NUMBER;
IF ( @NUMBER = 0 )
BEGIN
PRINT 'WRONG DATABASE NAME PASSED'
RAISERROR('NO DATABASE MATCHES THE NAME SPECIFIED.',10,1)
RETURN
END
IF @number1= (Select count(*) from sys.databases where name = '@DATABASE_NAME' and state_desc = 'Online' and source_database_id IS NULL and is_read_only = 0)
BEGIN
PRINT '** Entered Database is NOT Online or READ ONLY. ***'
RAISERROR ( 'THE TABLE [DBCC_HISTORY] DOES NOT EXIST IN THE DATABASE', 15,10 )
RETURN;
END;
--END
--ELSE
--BEGIN
PRINT 'Loop13000'
DECLARE @sql1 NVARCHAR(4000)
--SET @sql1 = 'DBCC CHECKDB('+ @database_name +') WITH TABLERESULTS'
PRINT @database_name
SET @sql1 = 'dbcc checkdb(''' + @DATABASE_NAME
+ ''') with tableresults'
IF @PHYSICAL_ONLY = 1
SET @sql1 = @sql1 + ', PHYSICAL_ONLY '
IF @tablock = 1
SET @sql1 = @sql1 + ', TABLOCK '
PRINT 'loop 14'
PRINT 'loop 15 - Entering records to Staging Table table *** dbcc_history'
SELECT @SQL1
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 (@sql1)
PRINT 'loop 16'
PRINT @DATABASE_NAME
PRINT 'loop 17 - Entering records to Final table *** dbcc_check_history'
INSERT INTO DBCC_CHECK_HISTORY
([dbname],
[objname],
[type_desc],
[indexname],
[allocationtype],
[error],
[level],
[state],
[status],
[repairlevel],
[messagetext],
[timestamp])
SELECT Db_name(dbid) AS dbname,
obj.NAME AS objname,
obj.type_desc AS objtype,
idx.NAME AS indexname,
alu.type_desc AS allocationtype,
cdb.error,
cdb.level,
cdb.state,
cdb.status,
cdb.repairlevel,
messagetext,
cdb.timestamp
FROM dbcc_history AS cdb
LEFT JOIN sys.objects AS obj
ON cdb.objectid = obj.object_id
LEFT JOIN sys.indexes AS idx
ON cdb.objectid = idx.object_id
AND cdb.indexid = idx.index_id
LEFT JOIN sys.allocation_units AS alu
ON cdb.allocunitid = alu.allocation_unit_id
ORDER BY Db_name(dbid),
objtype,
objname,
indexname;
END
END TRY
BEGIN catch
DECLARE @ERRORNUMBER INT;
DECLARE @ERRORSEVERITY INT;
DECLARE @ERRORSTATE INT;
DECLARE @ERRORLINE INT;
--DECLARE @ERRORPROCEDURE INT;
DECLARE @ERRORMESSAGE NVARCHAR(4000);
SELECT @ERRORNUMBER = Error_number(),
@ERRORSEVERITY = Error_severity(),
@ERRORSTATE = Error_state(),
@ERRORLINE = Error_line()
--@ERRORPROCEDURE = ERROR_PROCEDURE();
-- BUILD THE MESSAGE STRING THAT WILL CONTAIN ORIGINAL
-- ERROR INFORMATION.
SELECT @ERRORMESSAGE =
N'ERROR %D, LEVEL %D, STATE %D, PROCEDURE %S, LINE %D, '
+ 'MESSAGE: ' + Error_message();
END catch
END
*************** TABLE SCRIPTS *****************
CREATE TABLE [dbo].[dbcc_check_history](
[DBName] [sysname] NULL,
[ObjName] [sysname] NULL,
[type_desc] [nvarchar](120) NULL,
[IndexName] [sysname] NULL,
[AllocationType] [nvarchar](120) NULL,
[Error] [int] NULL,
[Level] [int] NULL,
[State] [int] NULL,
[Status] [int] NULL,
[RepairLevel] [int] NULL,
[MessageText] [varchar](7000) NULL,
[TimeStamp] [datetime] NULL CONSTRAINT [DF_dbcc_history_TimeStamp] DEFAULT (getdate())
) ON [PRIMARY]
go
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_TimeStamp1] DEFAULT (getdate())
) ON [PRIMARY]
Thanks.
April 6, 2015 at 2:22 am
Quick suggestion, why not use Ola Hallengren's SQL Server Integrity Check rather than roll your own?
😎
April 6, 2015 at 6:42 am
Thanks.
But I planned to execute the dbcc checkdb with my own customized way only.
Thanks.
April 7, 2015 at 2:56 am
Seconding the recommendation to use Ola's script. He's done all the hard work, his stuff works, it works well, it's easy to use.
Couple things about your code:
TABLOCK WILL CAUSE DBCC CHECKDB TO RUN FASTER ON A DATABASE UNDER HEAVY LOAD, BUT DECREASES THE CONCURRENCY.
The Tablock option will fail on a DB under heavy load because it can't get the exclusive table locks it needs. Tablock is an option for when there's a problem creating the database snapshot that CheckDB normally uses, it's definitely not something I'd recommend using normally.
Also, you don't want to be running CheckDB during times of heavy load because of the load it puts on the server. Run it during the quietest time you have.
You really want to use the NO_INFOMSGS option all the time. There's no point in wading through thousands of rows of 'There are X rows in Y pages in table Z' that CheckDB puts out when not run with that option.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply