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


Memory Corruptions, or Why You Need DBCC CHECKDB


Memory Corruptions, or Why You Need DBCC CHECKDB

Author
Message
Paul Randal
Paul Randal
SSCrazy Eights
SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)

Group: General Forum Members
Points: 8122 Visits: 1719
Michael Meierruth (11/8/2012)
After reading your recent 'Importance of Validating Backups' and now this article on DBCC CHECKSUM, I'm very curious to know how much database corruption is really encountered in the real world. I deal with a few client production databases and in the course of 10 years or so I have never run into this corruption issue - thank heaven.
So to all the SQL Server Central community out there, to whom has this ever really happend!?


Hundreds to thousands of times every week across the world, in the tens of millions of SQL Server databases out there. Just look at the corruption forums here. I get random emails from people at least 5 times a week asking for help with interpreting DBCC CHECKDB results, and I've been directly involved (at Microsoft, on forums, with clients, emails etc) with several thousand cases of corruption over the last 12 years.

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
Paul Randal
Paul Randal
SSCrazy Eights
SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)

Group: General Forum Members
Points: 8122 Visits: 1719
dharmendra.keshari (11/8/2012)
Hi Paul,

Thank you so much for writing on CheckSum. it is really awesome blog.

Could you please provide any link which gives us information about the all the activities which happen internally when we perform backup with option "Perform checksum before writing to media" ?

Thanks!
Dharmendra Keshari
Dharmendra.Keshari@gmail.com


Thanks!

There isn't one that goes into internal details. The pertinent facts are that the checksums of allocated pages in the database are validated before being written to the backup - that's all we care about.

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
Paul Randal
Paul Randal
SSCrazy Eights
SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)

Group: General Forum Members
Points: 8122 Visits: 1719
Hey Daniel - no, your thinking isn't right. Remember that DBCC CHECKDB isn't a checksum-checker, it happens to check checksums as part of what it does, but it goes a lot deeper and so will discover the corruption even if the checksum isn't incorrect (because of a timing issue). 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
Paul Randal
Paul Randal
SSCrazy Eights
SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)

Group: General Forum Members
Points: 8122 Visits: 1719
@Jim - exactly!

@Adam - indeed, using WITH TABELRESULTS is the way to go.

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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)

Group: General Forum Members
Points: 213769 Visits: 41977
As usual, a great article, Paul.

Do you have a link to a follow up somewhere that tells you what to do when corruption is found using these methods?

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Michael Meierruth
Michael Meierruth
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2324 Visits: 2516
Adam Seniuk (11/8/2012)
Michael Meierruth (11/8/2012)
Adam,
There is something not right with your 'exec' statment with all those quotes and parenthesis. Can't quite grasp what it is.
But I definitely like this 'with TABLERESULTS' clause. This definitely makes it all a lot easier.
Thanks.


oops, I must have copied it from a script that is running dynamic queries already. just swap out the '' to ' and it should work.

Superb! It works like a charm! And it's definitely better than my awful hack.
Paul Randal
Paul Randal
SSCrazy Eights
SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)

Group: General Forum Members
Points: 8122 Visits: 1719
Hey Jeff - nothing explicit, but that's going to be the topic of my next article for SSC that I'll be writing this month.

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
dtipser
dtipser
SSC-Enthusiastic
SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)

Group: General Forum Members
Points: 140 Visits: 433
Thanks for clarifications.

Daniel



Michael Meierruth
Michael Meierruth
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2324 Visits: 2516
Paul Randal (11/8/2012)
Michael Meierruth (11/8/2012)
After reading your recent 'Importance of Validating Backups' and now this article on DBCC CHECKSUM, I'm very curious to know how much database corruption is really encountered in the real world. I deal with a few client production databases and in the course of 10 years or so I have never run into this corruption issue - thank heaven.
So to all the SQL Server Central community out there, to whom has this ever really happend!?


Hundreds to thousands of times every week across the world, in the tens of millions of SQL Server databases out there. Just look at the corruption forums here. I get random emails from people at least 5 times a week asking for help with interpreting DBCC CHECKDB results, and I've been directly involved (at Microsoft, on forums, with clients, emails etc) with several thousand cases of corruption over the last 12 years.

Thanks

So in those several thousand cases what do you see as the main cause of these corruptions. It's ovbiously something bad at the hardware level. But what I'm looking for is things like 'having bought cheap hardware', 'not changing hardware often enough', 'bad physical environments', 'plain bad luck' i.e. 'philosophical' things like that.
MWise
MWise
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1048 Visits: 1804
Michael Meierruth (11/8/2012)

So in those several thousand cases what do you see as the main cause of these corruptions. It's ovbiously something bad at the hardware level. But what I'm looking for is things like 'having bought cheap hardware', 'not changing hardware often enough', 'bad physical environments', 'plain bad luck' i.e. 'philosophical' things like that.


I've had it happen due to a harddrive controller crapping out intermittently. And then once with a bad memory chip. These were solid machines that weren't cheap or old. Philosophically, you plan for the worst and hope for the best. Or something like that :-)

Look at several companies with server hardware in Lower Manhattan - they are blaming their host company for flooded hardware, saying the host company shouldn't have servers in basements. But that wouldn't have been a problem if they'd prepared for the worst and ensured that they had good offsite backups or mirrored co-location facilities outside of the disaster area. DR and BCP should not be overlooked anymore or an afterthought.

MWise



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