March 23, 2011 at 8:45 am
Hi All,
One of our databases got corrupted. It is throwing following error:
SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x59f6e189; actual: 0x59fae189). It occurred during a read of page (1:40) in database ID 22 at offset 0x00000000050000 in file 'd:\data\ms\sample.mdf'
I ran dbcc checkdb it failed by throwing above error again.
I tried dbcc checkdb(databasename,repair_allow_data_oss), but this failed throwing above error.
So I thought of restoring the backups which we had but to my surprise the last incremental backup has been corruped.when I tried to restore the last incremental backup on the full backup it failed to restore throwing the same above error. I tried to restore it with continue_after_error otpion but it also failed with above error.
Regualarly we will check the validity of the backups ith restore verifyonly command but this incremental bakcup which has been corrupted is showing as a valid backup when we ran restore verifyonly.
Please suggest me on how to move forward in this scenario
In future how to check the validity of the backups?
Thanks,
Vamsy
March 23, 2011 at 8:47 am
Restore VerifyOnly checks the header and metadata of the backup and not much else. It's perfectly possible for restore verifyonly to succeed and restore to fail. The only way to be sure a backup is restorable is to restore it.
I suppose you have no clean backup now?
Please run the following and post the full and complete output.
DBCC CheckDB(<Database name>) WITH No_INFOMSGS, ALL_ERRORMSGS
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
March 23, 2011 at 8:51 am
Thanks for your reply Gail
DBCC CheckDB(<Database name>) WITH No_INFOMSGS, ALL_ERRORMSGS
I ran this checkdb command but it failed throwing the error which I had sent before.
Thanks,
Vamsy
March 23, 2011 at 8:53 am
Not repairable, not good.
Do you have a full backup that's restorable? Do you have log backups from that point up until present?
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
March 23, 2011 at 9:04 am
Gail,
Our database is in simple recovery model so no log backups. I have full backup taken on saturday and one incremental backup(valid backup) which is on sunday.
But I am able to take back of this corruted database surprisingly.I took a backup of this corrupted database and tried to restore this as a new database but it is failing with the same error. I tried with restore with continue_after_error also but it also failed throwing same error.
We ran checkdb on saturday at that time database is good. Database is corrupted on sunday evening.Incremental Backups on tuesday and wednesday are corrupted.
We cannot run checkdb daily to check consistancy.Please suggest me on how to handle this issue in future.
Thanks,
Vamsy
March 23, 2011 at 9:11 am
Sure you can back up the corrupt DB. Totally useless because the corruption will be included in the backup, but you can do it.
You have two options here.
1) Rename the corrupt database. Restore the full + sunday diff as a new DB. Try to sync in missing data.
2) Script all objects, export all data, recreate the DB.
I would recommend option 1. Get something like RedGate's SQL data compare to do the data sync.
Going forward, at minimum start taking your backups with the checksum option. Had you being doing that, the tuesday diff would have failed and you would have known then that there was a problem.
If you can, test all backups by restoring them on a secondary server. If you can do that, you can then checkDB the restored copy and offload that work from the production server.
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
March 23, 2011 at 9:46 am
I would tend to second Gail's advice, go with a second db and see what you can get.
There's a difference between the page on disk being unreadable and causing a hard error and a page being corrupted on disk with the wrong information that SQL Server expects. If it's the latter, the backup runs fine because it reads and writes the page to a backup without interpreting it. It just sends the bits back up.
That is why you need to run DBCC regularly. IF you can't run it daily, then you need to run it weekly at a minimum. If you can't afford the resources on your primary server, restore a backup to a secondary machine and run it there. Or get a product like Virtual Restore from Red Gate (I work for Red Gate) if space is an issue and restore it on a workstation, but you need to run dbcc checks.
March 23, 2011 at 10:41 pm
I'm surprised nobody has mentioned the most important thing. You need to identify the cause of the corruption. It not only corrupted your database, but your backup as well, and it may start getting worse. (Not point in fixing everything if it just gets corrupted again tomorrow.)
Of course identifying the cause can be difficult, it could be just about any piece in the chain: CPU, Memory, Motherboard, Disk/RAID controller, cables, drives, drivers, third party software/filters, etc...
The first step would be to make sure all of your good backups are safely copied to another location before anything can happen to them.
March 24, 2011 at 1:23 am
Gail,
So if I turn on the checksum option while backing up databases.Will sqlserver intimate me about this error before?
I opted your suggestion. Restored full backup and incremental which is good.After that I have moved tables,storedprocedures,triggers etc to the new database from the corrutped database.
I have other databases in the same drive on which the corrupted database files reside. Will these database get affected because of this corrupted database files?
I have ran checkdb on other databases and they ran fine with out errors.
Is disk got corrupted on which corrupted database is residing?
Please suggest me on how to find out the culprit..
Thanks,
Vamsy
March 24, 2011 at 1:57 am
vamshikrishnaeee (3/24/2011)
So if I turn on the checksum option while backing up databases.Will sqlserver intimate me about this error before?
If you backup with checksum, SQL checks the checksums as to does the backup. A bad checksum will fail the backup. It's not a replacement for CheckDB, but it is good to know that the backup is clean.
Is disk got corrupted on which corrupted database is residing?
Please suggest me on how to find out the culprit..
Maybe not the disk itself, but something in the IO subsystem is very likely the culprit. Check logs, make sure that firmware is up to date.
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
March 24, 2011 at 4:36 am
vamshikrishnaeee (3/23/2011)
Regularly we will check the validity of the backups ith restore verifyonly command but this incremental bakcup which has been corrupted is showing as a valid backup when we ran restore verifyonly.
Using the CHECKSUM option during the backup would have caused SQL Server to check the page checksums during the backup, in addition to generating a checksum for the entire backup. In this case, you would have caught the corrupted page if you had used the CHECKSUM option in your backups. However, using the CHECKSUM option uses a bit more CPU resources, so you may need to take that into consideration.
vamshikrishnaeee (3/23/2011)
In future how to check the validity of the backups?
Restore them. RESTORE VERIFYONLY isn't 100% reliable, even with the CHECKSUM option. See here for details.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
March 24, 2011 at 7:06 am
Can I use page restore from full bakcup in case if am in full recovery model to recover that page which is corrupted?
Thanks,
Vamsy
March 24, 2011 at 7:10 am
vamshikrishnaeee (3/24/2011)
Can I use page restore from full bakcup in case if am in full recovery model to recover that page which is corrupted?
Depends on what page gets corrupt. Most, yes, providing you have a full chain of log backups.
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
March 25, 2011 at 5:10 am
Thanks for your suggestions Gail and Ray
Small question:
I have moved the data from the corrupted database to the new database. Will the new database get corrupted because of importing data from the corrupted database because might be some of data which I have been imported,is from the corrupted page?
Thanks,
Vamsy
March 25, 2011 at 5:42 am
SQL Server checks the page checksums when it reads the pages, so if you were able to copy the data from the corrupted database without errors, then it's unlikely that you copied anything from the corrupted page.
Having said that, you might want to check the contents of that page just to find out what exactly it contains e.g.
DBCC PAGE (22, 1, 40, 3)
See here for an example of using DBCC PAGE.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply