February 17, 2009 at 5:29 am
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?
February 17, 2009 at 5:45 am
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
Consultant - Straight Path Solutions
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
February 17, 2009 at 8:03 am
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
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
February 17, 2009 at 9:45 am
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.
February 17, 2009 at 10:12 am
Great article. Fantastic way of writing....
February 17, 2009 at 12:29 pm
Perhaps as a preventative measure, how often should I run check db?
On SQL 2000 I ran it once a week.
This is on a server configured with RAID 5, SQL 2005 SP 3.
February 17, 2009 at 12:56 pm
Sailor (2/17/2009)
Perhaps as a preventative measure, how often should I run check db?
There's no right answer for that. Run it often enough that if is does find corruption you can restore from backup without losing data.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 17, 2009 at 1:55 pm
Ninja's_RGR'us (2/17/2009)
That begs this question... assuming you have a corrupt backup of msdb, can you rebuild it then reimport the data back in?
You should, providing you can get the data out of the corrupt MSDB. I don't think the MSDB tables are classified system tables.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 17, 2009 at 3:07 pm
Even though I have not faced any corruption as of date, but now i am ready with at least some thought. Good one.
Ajay Prakash
February 17, 2009 at 6:23 pm
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.
Thanks, Gail.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 18, 2009 at 12:54 am
Another save point has been set 😎
I know (and have activate on all our db) PAGE_VERIFY at db level.
Is my perception correct that this would detect any issue as soon as it occurs ?
I still run dbcc checkdb on a regular basis.
(just like i run sp_updatestats every once in a while)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 18, 2009 at 1:11 am
Thank you Gail for such a Wonderful article 🙂
A quick question:
???If database is corrept and we are taking backup.... does backup too have corruption?
???If we are restoring the database with corrupted backup will it repair database automatically while restoring?
Hope you will be able to help with my question.
Cheers!!!
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
February 18, 2009 at 4:47 am
ALZDBA (2/18/2009)
I know (and have activate on all our db) PAGE_VERIFY at db level.Is my perception correct that this would detect any issue as soon as it occurs ?
Which page verify? Torn page is default in 2000, Checksum in 2005 and 2008.
Those do not guarantee that the issue will be picked up as soon as it happens. Imagine if the IO subsystem corrupts part of a table that's never read. How would SQL realise there's a problem?
If either of those is on and a page is damaged in any way(1), the next time the page is read from disk, you will immediately get an error 824 severity 23.
(1) Checksum will detect far more issues than torn page does. If you only have torn page on, there are several issues that will only be picked up when SQL cracks the page and reads the rows. There are other issues that won't be picked up at all.
I still run dbcc checkdb on a regular basis.
You need to. It's one of the few things that reads the entire database and checks all pages. The other being a full backup, if it's run with the Checksum option and the page verify is set to checksum.
(just like i run sp_updatestats every once in a while)
Shouldn't be necessary on SQL 2005 or higher. Most of the incorrect allocation metadata issues have been fixed there.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 18, 2009 at 4:53 am
free_mascot (2/18/2009)
???If database is corrept and we are taking backup.... does backup too have corruption?
Yes. This is why it's so important to find corruption early so that there are clean backups to restore from.
???If we are restoring the database with corrupted backup will it repair database automatically while restoring?
No, and quite frankly if it did it would be a very bad thing. Repair often loses data. How would you feel if a restore process could leave the database missing records?
Restore leaves the database just as it was at the time of the backup. If there was corruption in the DB when it was backed up either the restored database will also be corrupt or the restore process will fail.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 18, 2009 at 7:36 pm
This was absolutely superb. Thank you for all your research and information!
Viewing 15 posts - 16 through 30 (of 86 total)
You must be logged in to reply to this topic. Login to reply