A faster DBCC CHECKDB?

Click here to monitor SSC

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>

SQLServerCentral is supported by Red Gate Software Ltd.

Log in :: Register :: Not logged in

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>

Recent Posts

Popular Topics

Home

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Ian Stirk » A faster DBCC CHECKDB?

Add to briefcase

23 posts, Page 1 of 3

A faster DBCC CHECKDB?

Expand / Collapse

Author

Message

Posted Monday, August 22, 2011 10:02 PM

SSC Rookie

Group: General Forum Members
Last Login: Monday, May 20, 2013 6:22 AM
Points: 47, Visits: 762

Comments posted to this topic are about the item A faster DBCC CHECKDB?

Post #1163700

Posted Monday, August 22, 2011 11:02 PM

SSCommitted

Group: General Forum Members
Last Login: Yesterday @ 4:00 PM
Points: 1,910, Visits: 1,602

Nice - but really only the equivalent of BACKUP DATABASE ... WITH CHECKSUM.

I would strongly advise not using this for the same reasons I strongly advise not using BACKUP DATABASE ... WITH CHECKSUM as an alternative to DBCC CHECKDB - it will not detect errors introduced by memory problems, SQL Server bugs, or IO subsystem corruptions of pages using torn-page detection.

Bottom line - you can't avoid running DBCC CHECKDB - don't fall into the trap of running something faster. Offload your checks to a non-production server but continue running DBCC CHECKDB - it's the only thing that will find all corruptions.

Thanks

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

Post #1163711

Posted Monday, August 22, 2011 11:09 PM

SSCoach

Group: General Forum Members
Last Login: Yesterday @ 10:25 AM
Points: 18,754, Visits: 12,337

Thanks for the heads up and advice Paul.

Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server 2008

SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp

Post #1163713

Posted Tuesday, August 23, 2011 12:15 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, May 20, 2013 6:22 AM
Points: 47, Visits: 762

Thanks Paul.

Agreed that this is not an optimal solution. But if I want to know where an error is, and quickly, this method does seem fastest for most IO related errors (it stops immediately there is an error!).

Sometimes, for whatever reasons, there isn’t time or space to backup to another server (although, again agreed, this is the preferred method).

As stated, this is NOT a substitute for DBCC CHECKDB, but a quick ah-hoc method of finding the most common errors very quickly.

Thanks
Ian

Post #1163729

Posted Tuesday, August 23, 2011 1:05 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 8:17 AM
Points: 4,804, Visits: 8,074

Nice method. Thanks for the good article.
And thanks to Paul Randal for the sage advice.

Get your two-cent-answer quickly
The Spaghetti DBA

Post #1163742

Posted Tuesday, August 23, 2011 1:24 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, November 29, 2012 2:40 AM
Points: 4, Visits: 61

Nice.
However when i try i get:
Msg 103, Level 15, State 4, Line 117
The identifier that starts with 'TableName: Land.ATL_Tag_TagVidTrafikplats_RapportSomSkaGoras. IndexName: PK_ATL_Tag_TagVidTrafikplats_RapportSomSkaGoras. IndexI' is too long. Maximum length is 128.

The identifier that includes full table name and index name gets too long. Need to do some minor change and put names somewhere else.
/Sten

Post #1163749

Posted Tuesday, August 23, 2011 2:15 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, November 29, 2012 2:40 AM
Points: 4, Visits: 61

After the following modification to the Dynamic SELECT it works with long tablenames and indexnames.

-- Build SQL to read each page in each index (including clustered index).
SELECT @CheckIndexesSQL = @CheckIndexesSQL + CHAR(10)
+ 'SELECT COUNT_BIG(*) AS [Rows], ''TableName: ' + SchemaName + '.'
+ TableName + ''' AS [Tablename], ''IndexName: ' + ISNULL(IndexName, 'HEAP')
+ '. IndexId: ' + CAST(indid AS VARCHAR(3)) + ''' AS [Index] FROM '
+ QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName)
+ ' WITH (INDEX(' + CAST(indid AS VARCHAR(3)) + '));'
FROM #IndexDetails

Post #1163769

simon.hughes-697036

Posted Tuesday, August 23, 2011 3:41 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 9:20 AM
Points: 1, Visits: 13

I've just used this to make it work with large table names and index names, as the max number of characters is 128 for an identifier.

-- Build SQL to read each page in each index (including clustered index).
SELECT  @CheckIndexesSQL = @CheckIndexesSQL + CHAR(10) + 'SELECT COUNT_BIG(*) AS [' + LEFT(SchemaName + '.' + TableName + '.'
        + ISNULL(IndexName, 'HEAP') + '. IxId:' + CAST(indid AS VARCHAR(3)), 128) + '] FROM ' + QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName)
        + ' WITH (INDEX(' + CAST(indid AS VARCHAR(3)) + '));'
FROM    #IndexDetails

Post #1163808

Posted Tuesday, August 23, 2011 4:37 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 01, 2013 2:14 AM
Points: 122, Visits: 173

Hi,

Reading through the indexes is a smart move! I might steal the idea :)

If I may have some improvement suggestions:
1. Instead of index(1), use select * simply, orelse LOB pages are not checked.
2. I'd prefer checking the system tables as well.
3. Watch out for filtered indexes - orelse QP will kill the script, not the IO error :)
4. I'd pick a different title. As Paul highlighted, DBCC CHECKDB has no real alternative. So the title is catchy but probably a "Detect Disk Corruptions Superfast" or similar would manage expectations better.

Cheers,
Erik

-- Erik http://blog.rollback.hu

Post #1163840

Posted Tuesday, August 23, 2011 5:38 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 3:41 AM
Points: 13,383, Visits: 25,189

Excellent code. Nice article. Well explained.

But I have to say, articles like this really do make me nervous. It seems like a very healthy percentage of our fellow database professionals are missing a few fundamentals. Suggesting a way to avoid something as important as DBCC CHECKDB seems rife with potential for disaster. I'd just hate to hear "I was following your advice and now we've lost our production system."

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software

Post #1163870

« Prev Topic | Next Topic »

Add to briefcase

23 posts, Page 1 of 3

Permissions

Expand / Collapse