Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

A faster DBCC CHECKDB? Expand / Collapse
Author
Message
Posted Monday, August 22, 2011 10:02 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 9, 2014 7:53 AM
Points: 49, Visits: 850
Comments posted to this topic are about the item A faster DBCC CHECKDB?
Post #1163700
Posted Monday, August 22, 2011 11:02 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, October 27, 2014 11:15 AM
Points: 2,041, Visits: 1,671
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

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: 2 days ago @ 3:20 PM
Points: 18,064, Visits: 16,099
Thanks for the heads up and advice Paul.



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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1163713
Posted Tuesday, August 23, 2011 12:15 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 9, 2014 7:53 AM
Points: 49, Visits: 850
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

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 2:58 PM
Points: 4,655, Visits: 11,126
Nice method. Thanks for the good article.
And thanks to Paul Randal for the sage advice.


--
Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Post #1163742
Posted Tuesday, August 23, 2011 1:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 29, 2013 5:51 AM
Points: 4, Visits: 68
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

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 29, 2013 5:51 AM
Points: 4, Visits: 68
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
Posted Tuesday, August 23, 2011 3:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 25, 2013 6:09 AM
Points: 1, Visits: 15
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

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 1, 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

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 8:22 AM
Points: 14,205, Visits: 28,534
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 Query Performance Tuning
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1163870
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse