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

Help, my database is corrupt. Now what? Expand / Collapse
Author
Message
Posted Monday, February 16, 2009 3:29 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 10:06 PM
Points: 35,978, Visits: 30,269
Awesome article, Gail. Wish I had it when MSDB went sour on one of my Dev servers especially since my predecessors saw no need to backup a Dev Server. How much of this applies to the system's databases (not including TempDB, of course)? And, pardon my ignorance on the subject... I'm more of an Application DBA than a System's DBA.

--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."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(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 #658115
Posted Monday, February 16, 2009 3:57 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, April 07, 2014 1:54 AM
Points: 363, Visits: 355
At the risk of being redundant, excellent article, Gail. It should be compulsory "calm down and think" reading for any DBA facing an apparent catastrophe. Bookmarked and link mailed to collegues.

Stephen



Post #658131
Posted Monday, February 16, 2009 4:13 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, October 22, 2013 5:44 PM
Points: 368, Visits: 714
SDM (2/16/2009)
At the risk of being redundant, excellent article, Gail. It should be compulsory "calm down and think" reading for any DBA facing an apparent catastrophe. ..
Stephen

Same from me - there should be a prize for you, and not just for this article. This is so helpful to all accidental DBA's ... it's not funny!
Post #658136
Posted Monday, February 16, 2009 9:50 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, March 05, 2014 8:28 PM
Points: 379, Visits: 265
Great article. Thanks for taking the time to create it. An excellent resource for sure.

Mike

How to Post Performance Problems

How to Post Best Practices
Post #658188
Posted Tuesday, February 17, 2009 12:08 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:30 PM
Points: 41,531, Visits: 34,448
Jeff Moden (2/16/2009)
How much of this applies to the system's databases (not including TempDB, of course)?


MSDB can be repaired, I believe (haven't tried it). Master I would recommend restore or rebuild, though I have seen someone repair it successfully. Model, since it should have nothing in it, you should be able to copy from another server.

Depends what the corruption is. If it's just in the nonclusters, then repair's probably safe even in the system databases. If it's in the clustered index then my recommendation would be restore or rebuild (especially with master)

Master and msdb can be completely recreated, by the rebuild master function of the installer (master) or by script (msdb). It won't get any data back, but it will get the DB usable and free of corruption.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #658237
Posted Tuesday, February 17, 2009 5:29 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, January 16, 2014 12:03 PM
Points: 21,376, Visits: 9,594
GilaMonster (2/17/2009)
Jeff Moden (2/16/2009)
How much of this applies to the system's databases (not including TempDB, of course)?


MSDB can be repaired, I believe (haven't tried it). Master I would recommend restore or rebuild, though I have seen someone repair it successfully. Model, since it should have nothing in it, you should be able to copy from another server.

Depends what the corruption is. If it's just in the nonclusters, then repair's probably safe even in the system databases. If it's in the clustered index then my recommendation would be restore or rebuild (especially with master)

Master and msdb can be completely recreated, by the rebuild master function of the installer (master) or by script (msdb). It won't get any data back, but it will get the DB usable and free of corruption.



That begs this question... assuming you have a corrupt backup of msdb, can you rebuild it then reimport the data back in?
Post #658397
Posted Tuesday, February 17, 2009 5:45 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 5:31 AM
Points: 10,910, Visits: 12,548
GilaMonster (2/17/2009)
Jeff Moden (2/16/2009)
How much of this applies to the system's databases (not including TempDB, of course)?


MSDB can be repaired, I believe (haven't tried it). Master I would recommend restore or rebuild, though I have seen someone repair it successfully. Model, since it should have nothing in it, you should be able to copy from another server.

Depends what the corruption is. If it's just in the nonclusters, then repair's probably safe even in the system databases. If it's in the clustered index then my recommendation would be restore or rebuild (especially with master)

Master and msdb can be completely recreated, by the rebuild master function of the installer (master) or by script (msdb). It won't get any data back, but it will get the DB usable and free of corruption.


I had to deal with a suspect msdb (SQL 2000) with no backups, so I had to recreate it. This was one of those departmental installs of SQL Server that I was not involved with until it crashed. There were some good articles on MSDN on how to do it.




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #658406
Posted Tuesday, February 17, 2009 8:03 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, April 18, 2014 12:38 PM
Points: 2,290, Visits: 2,545
Thanks for the article. As you said, I hope I will never need it - though just last week I saw the first, trivial, error that I repaired with the DBCC UPDATEUSAGE command, before you had posted your article. That was scary enough that I read your entire article when I saw the title, and I immediately added it to my briefcase as soon as I was done.

This is a great service to the community. Thanks again!

webrunner


-------------------
"Operator! Give me the number for 911!" - Homer Simpson

"A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Post #658541
Posted Tuesday, February 17, 2009 9:45 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, April 04, 2014 5:19 PM
Points: 265, Visits: 2,538
This article was well written. I appreciate the time you took to lay it all out in a logical manner and to explain the different scenarios. Most helpful. Thanks.
Post #658628
Posted Tuesday, February 17, 2009 10:12 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, March 20, 2014 7:32 AM
Points: 5,193, Visits: 1,368
Great article. Fantastic way of writing....




Post #658650
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse