SQLServerWiki

“The Only Thing That Is Constant Is Change”

Alternate for DBCC CHECKDB if you database huge!

Posted by database-wiki on July 25, 2012

You could use below script, only when running a CHECKDB on a large DB is not a good option for you. I have noticed it takes around half the time of running a dbcc checkdb.

NOTE: IT first processes heaps and clustered indexes first and stops as soon as it get an error. Non-clust indexes could be rebuild and are not serious.

USE DATABASENAME

Go

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

— Ensure buffer pool is empty.

DBCC DROPCLEANBUFFERS

— Get details of all heaps, clustered indexes and non-clustered indexes to check.

SELECT

ss.name AS SchemaName

, st.name AS TableName

, s.name AS IndexName

, s.rowcnt AS ‘Row Count’

, s.indid

INTO #IndexDetails

FROM sys.sysindexes s

INNER JOIN sys.tables st ON st.[object_id] = s.[id]

INNER JOIN sys.schemas ss ON ss.[schema_id] = st.[schema_id]

WHERE s.id > 100 — Only user tables

AND s.rowcnt >= 1 — Ignore stats rows

ORDER BY s.indid, [Row Count] DESC — Do heaps and clustered first

DECLARE @CheckIndexesSQL NVARCHAR(MAX)

SET @CheckIndexesSQL = ”

— Build SQL to read each page in each index (including clustered index).

SELECT @CheckIndexesSQL = @CheckIndexesSQL + CHAR(10)

+ ‘SELECT COUNT_BIG(*) AS [TableName: ‘ + SchemaName + ‘.’

+ TableName + ‘. IndexName: ‘ + ISNULL(IndexName, ‘HEAP’)

+ ‘. IndexId: ‘ + CAST(indid AS VARCHAR(3)) + ‘] FROM ‘

+ QUOTENAME(SchemaName) + ‘.’ + QUOTENAME(TableName)

+ ‘ WITH (INDEX(‘ + CAST(indid AS VARCHAR(3)) + ‘));’

FROM #IndexDetails

— Debug.

DECLARE @StartOffset INT

DECLARE @Length INT

SET @StartOffset = 0

SET @Length = 4000

WHILE (@StartOffset < LEN(@CheckIndexesSQL))

BEGIN

PRINT SUBSTRING(@CheckIndexesSQL, @StartOffset, @Length)

SET @StartOffset = @StartOffset + @Length

END

PRINT SUBSTRING(@CheckIndexesSQL, @StartOffset, @Length)

— Do work.

EXECUTE sp_executesql @CheckIndexesSQL

 

 

— Tidy up.

DROP TABLE #IndexDetailsResources

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: