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»»

Memory Corruptions, or Why You Need DBCC CHECKDB Expand / Collapse
Author
Message
Posted Thursday, November 8, 2012 10:37 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, October 27, 2014 11:15 AM
Points: 2,041, Visits: 1,671
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
Post #1382624
Posted Thursday, November 8, 2012 10:38 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, October 27, 2014 11:15 AM
Points: 2,041, Visits: 1,671
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
Post #1382625
Posted Thursday, November 8, 2012 10:40 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, October 27, 2014 11:15 AM
Points: 2,041, Visits: 1,671
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
Post #1382627
Posted Thursday, November 8, 2012 10:41 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, October 27, 2014 11:15 AM
Points: 2,041, Visits: 1,671
@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
Post #1382628
Posted Thursday, November 8, 2012 10:56 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:42 PM
Points: 35,768, Visits: 32,431
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1382638
Posted Thursday, November 8, 2012 11:17 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, December 14, 2014 4:46 AM
Points: 542, Visits: 2,143
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.
Post #1382652
Posted Thursday, November 8, 2012 11:27 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, October 27, 2014 11:15 AM
Points: 2,041, Visits: 1,671
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
Post #1382657
Posted Thursday, November 8, 2012 11:44 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 2:02 PM
Points: 24, Visits: 328
Thanks for clarifications.

Daniel



Post #1382665
Posted Thursday, November 8, 2012 12:27 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, December 14, 2014 4:46 AM
Points: 542, Visits: 2,143
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.
Post #1382680
Posted Thursday, November 8, 2012 2:24 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, December 8, 2014 12:51 PM
Points: 395, Visits: 1,611
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



Post #1382717
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse