SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


A faster DBCC CHECKDB?


A faster DBCC CHECKDB?

Author
Message
ianstirk
ianstirk
SSC Veteran
SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)

Group: General Forum Members
Points: 220 Visits: 1037
Comments posted to this topic are about the item A faster DBCC CHECKDB?
Paul Randal
Paul Randal
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4755 Visits: 1717
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
SQLRNNR
SQLRNNR
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41313 Visits: 18565
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

ianstirk
ianstirk
SSC Veteran
SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)

Group: General Forum Members
Points: 220 Visits: 1037
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
Gianluca Sartori
Gianluca Sartori
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12966 Visits: 13351
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
stenperers
stenperers
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 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
stenperers
stenperers
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 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
simon.hughes-697036
simon.hughes-697036
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 16
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


Erik Bitemo
Erik Bitemo
SSC Veteran
SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)

Group: General Forum Members
Points: 242 Visits: 173
Hi,

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

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 Smile
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
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)

Group: General Forum Members
Points: 56651 Visits: 32799
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search