Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
campbellwarren
Starting Member
3 Posts |
 Posted - 2009-11-30 : 14:32:12
|
| SQL 2005 SP3.A single disk in my SAN array failed on a staging server.I have a job that runs every few hours and captures metrics that are used by me. The job uses sp_MSforeachtable and foreachdb...When the job ran after this failure it gave:Error: 211, Severity: 23, State: 51.Possible schema corruption. Run DBCC CHECKCATALOG.DBCC CHECKCATALOG returns no errors. as a shot in the dark, I tried:EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"GOEXEC sp_updatestatsresult was:Msg 211, Level 23, State 51, Procedure sp_MSforeach_worker, Line 31Possible schema corruption. Run DBCC CHECKCATALOG.Msg 0, Level 20, State 0, Line 0A severe error occurred on the current command. The results, if any, should be discardedSince this is staging, I can easily restore the db from a full backup with little consequence. If this were production, however, things would be less simple. Questions:1.) do you agree restore is appropriate next step or is there another approach?2.) If this were production, and this happened serveral hours ago, data might be lost since that time... I do have tlog backups so point in time recovery is possible... but still any data inserted into db after the restore woudl have to be "recreated"... i.e. record by record comparisons between restored database and "corrupt" db? |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2009-11-30 : 14:59:21
|
Restore would be the best solution if there is corruption. If you have transaction log backups, why would there be lost data? Take a tail-log backup then restore the latest full backup, all the transaction logs, ending with the tail-log backup that you took just before starting the restore. There should be 0 data loss in that situation. There will be downtime, but there shouldn't be data lossDoes this return any errors.DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS --Gail ShawSQL Server MVP |
 |
|
|
campbellwarren
Starting Member
3 Posts |
Posted - 2009-12-02 : 09:39:11
|
| DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS returned 30some consistency errors. I no longer have the specific messages, but did restore from full backup allowing some data loss (which is ok since this is just staging enviroment).As for dataloss, let's take the following scenario:full backup happened at 1pm, tlog backups hourly.At 2:05pm corruption occursAt 4:05pm a tail-log backup is taken.If I point in time recovery using transaction logs, do I recover up to 2:04pm and lose data from 2:05- 4:05pm. If I recover subsequent tlogs, do I no risk recreating corruption? |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2009-12-03 : 04:37:21
|
| No, you recover right up to 4:05 with the tail log backup. Corruption is, in the vast majority of cases, an IO problem. SQL doesn't corrupt it's own files (except in rare circumstances when there are nasty bugs) Most of the time the IO subsystem somehow mangles the data file directly, hence the record of the changes that the corruption caused won't be written into the transaction log.Remember a log backup is just backing up the records of changes in the transaction log. If the data file's been directly changed by an IO error, that change will be included in full (definitely) and diff (maybe) backups because they read the data file, but not by log backups because they just read the transaction log. Now if it's the log that's corrupted, it's a different story. If that happens, likely case is that the transaction log backup itself fails.There may be really rare cases, involving bulk-logged recovery, when the corrupt pages can get into the log backup, but those should be really, really, really rare and will only happen unnoticed if the DB does not have page verification CheckSum on (usually an upgraded DB)--Gail ShawSQL Server MVP |
|
|
|
campbellwarren
Starting Member
3 Posts |
Posted - 2009-12-03 : 11:12:06
|
| Completely makes sense... thanks for valueable insight. Last question about this scenario:Full backupcorruptionFull backuptlog backuptail-log backupIf a full backup occurs following corruption, can I still recover to the most recent point in time or will corruption be contained in the most recent full? |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2009-12-04 : 02:41:56
|
| In that case, you'd have to use the first of the full backups as full backups will contain the corruption. They're backups of everything in the database. If nothing's broken the log chain between the two full backups (switch to simple, backup log with truncate) and you have all the log backups, then you can use the first full backup and apply all of the log backups from then right up to the tail-log and be completely up to date.This, by the way, is why it's often recommended to run CheckDB before taking full backups, so that you don't end up in a situation where there's a backup that contains corruption, and also so that you're running CheckDB regularly--Gail ShawSQL Server MVP |
|
|
|
|
|
|
|
|
"Possible schema corruption" after single disk fa