Database corrupted

Question

Database corrupted

Viewing 15 posts - 16 through 29 (of 29 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

David O (3/7/2009)
That said, I have successfully used the advice I gave. I think caveats were clear and appropriate. I stand by post.

I'm sure you have, it is a valid technique for certain forms of corruption. There are a few things to note though.

1) Rebuilding the log is a last resort. It's reserved for cases where nothing else works, where the log is so damaged that the only way to continue is to discard it and get SQL to build a new one. It is not and should never be the first thing tried on a corrupt database

2) There's a chance that detaching, deleting the log and attaching will worsen the situation, turning a corrupt but online and mostly usable database into a database that's attached but suspect (or recovery pending), or a detached database that will not reattach.

I have seen a case where, as a result of detaching and failing to attach, we lost the entire database (backups were corrupt as well) and had to reconstruct 600GB of 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

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

David O Hall of Fame Points: 3228 More actions · Answer 2

Paul

My thought process was the corruption might be limited to a section of the log file. By rebuilding, the hope was to force all the transactions to commit and abandon a bad file. This was only one possiblity and why I recommended trying it on a test system.

If this rebuild failed, then I would have seen no choice but to begin a restore on the production system. I assumed and should have made more clear that I thought time was essential if the system was online. Data was being added to a system and the records probably weren't recoverable. Getting the system offline, trying a rebuild, and failing that restoring was my strategy.

David

Paul Randal One Orange Chip Points: 29544 More actions · Answer 3

David O (3/7/2009)
Paul
My thought process was the corruption might be limited to a section of the log file. By rebuilding, the hope was to force all the transactions to commit and abandon a bad file. This was only one possiblity and why I recommended trying it on a test system.

I don't follow you here - there's no way to force a transaction to commit, except by issuing a COMMIT TRAN statement. Rebuilding the log just throws away the old log, removing the possibility of even rolling back the uncommitted transactions. In most cases, rebuilding a transaction log causes corruption because it leaves transactionally inconsistent data, and if you're unlucky, a structurally inconsistent database.

But you were very right to suggest this only a test system and not in production. In this case, rebuilding the log on the production system may have caused corruption where there was none caused by the damaged log file itself.

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

David O Hall of Fame Points: 3228 More actions · Answer 4

In my step 5 on the test system, starting the database with the files copied from prod (essential a cold backup and restore) should have caused a standard roll forward and back on any pending transactions. Basically, testing the usefullness of the log. If that database comes online in step 5 the database should be tranactionally consistent.

In my step 6 the old log is disgarded in favor of new a new file. If, both these steps work the database should be transactionally consistent with a clean log file.

If either step 5 or 6 fail on the test system, you know prod is corrupt and you restore from your last good backup. If they both succeed on test, you should be able to duplicate on prod and end up healthy and whole.

If I'm missing something, I'm ready to learn.

David

Paul Randal One Orange Chip Points: 29544 More actions · Answer 5

ok - so you really did mean the database state is ONLINE. In that case, why do anything else? You don't need to detach or build a new log file. But the procedure would be different on the production system.

The statement you made about rebuilding to force transactions to commit is just wrong, which is why I took issue with it. Rebuilding a log doesn't do that. Attaching a database and making recovery run will also not commit transactions - it will allow any uncommitted transactions to rollback, not commit. Your next explanation is correct - now I know that's what you meant, but that's not what you wrote in your previous message.

Yes, this is pedantic, but the meanings of what you wrote were quite different. I guess what I should say is that when you're posting on forums like this (and please continue to do so!) you need to be *ultra-precise* in instructions and explanations, and use the correct terminology, to make sure that someone following them knows exactly what you're suggesting (and people don't argue with you :))

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

Mani-584606 SSChampion Points: 11788 More actions · Answer 6

Thanks for all your help..

Actually Our SAN had problems and all the production server got shutdown.When we restarted the servers , our scheduled log backup failed and then I checked the error log and find that that database log corrupted. I ran dbcc checkdb, which resulted in no errors and again I took the lg backup, it failed with same message.Now I chaged the logbackup path and took the logbackup, everything fine.

So i did not do anything afterthat.The database and application is running fine.

thanks

Mani

maechismo_8514 SSCoach Points: 17510 More actions · Answer 7

Mani (3/7/2009)
Thanks for all your help..
Now I chaged the logbackup path and took the logbackup, everything fine.
So i did not do anything afterthat.The database and application is running fine.
thanks
Mani

Paul,

My question still remains the same. As per OP, he was able to backup the log successfully. How would he be able to do that if the log backup is corrupted? he changed the log backup path, I am thinking he should not be able to do it as log itself is corrupted no matter even if he changed the backup path, the database still should not be able to back up the corrupted log.

thanks for your time.

Paul Randal One Orange Chip Points: 29544 More actions · Answer 8

Mani - what do you mean you changed the path and it worked? Can you explain in more detail? That doesn't make any sense.

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

Mani-584606 SSChampion Points: 11788 More actions · Answer 9

hi,

After the SAN outage, we restrated the server.As soon as the server is restarted, the scheduled log backup job was ran and resulted with the below error:

Backup detected log corruption in database ABC. Context is FirstSector. LogFile: 2 'F:\SQLLOGS\XYZ\ABC_Log.ldf' VLF SeqNo: x502e VLFBase: x2ce40000 LogBlockOffset: x2d0a9000 SectorStatus: 2 LogBlock.StartLsn.SeqNo: x4ee3 LogBlock.StartLsn.Blk: x1348 Size: x200 PrevSize: x400
2009-03-06 10:00:02.61 Backup Error: 3041, Severity: 16, State: 1.
2009-03-06 10:00:02.61 Backup BACKUP failed to complete the command BACKUP LOG ABC Check the backup application log for detailed messages.
2009-03-06 10:00:03.61 Backup Error: 3041, Severity: 16, State: 1.

Step1:I ran the dbcc checkdb->no errors

Step2:ran the scheduled log backup job and got the same above error message.Here log backup path of database is K:\logBackupStep3:I changed the log backup path to k:\NewLogBackup\.This time the log backup job was succesfully ran.(this trick is suggested by a senior DBA.I do not know the reason behind this but it worked)

Step4:I changed the log backup path back to k:\LogBackup and ran the log backup and it was successfull.No corruption error.

thanks

David O Hall of Fame Points: 3228 More actions · Answer 10

Mani

You have to test your ability to restore this database up to your last log backup. I've very suspicious about this "resolution" to your problem.

David

Mani-584606 SSChampion Points: 11788 More actions · Answer 11

I followed the Paul's Recommendation as below:

My recommendation in this case is as follows, with the understanding that your log backup chain will be broken (it's broken anyway because part of your log is corrupt):
1) stop all user activity in the database
2) switch to SIMPLE recovery model (this forcefully breaks the log backup chain, and removes the need for SQL Server to have a log backup of the damaged log)
3) switch back to FULL recovery model
4) take a full database backup, thus establishing a new log backup chain
5) start taking log backups again

Now please clarify me that my database is out of corruption or not?

Thanks for great help.

Mani

Paul Randal One Orange Chip Points: 29544 More actions · Answer 12

If you can run a DBCC CHECKDB and log backups successfully then I say that it looks like the db is clean.

Make sure you do some root cause analysis to figure out what corrupted the log in the first place.

Changing the path that you backed up to was a red herring - I think it likely that one of the failing log backups moved the low-water-mark LSN past the damaged portion of the log. A weird case though.

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

maechismo_8514 SSCoach Points: 17510 More actions · Answer 13

Paul Randal (3/8/2009)
If you can run a DBCC CHECKDB and log backups successfully then I say that it looks like the db is clean.
Make sure you do some root cause analysis to figure out what corrupted the log in the first place.
Changing the path that you backed up to was a red herring - I think it likely that one of the failing log backups moved the low-water-mark LSN past the damaged portion of the log. A weird case though.
Thanks

Thanks for the clarification Paul:). I take it this is going into your blog as well, looking forward to it.

David O Hall of Fame Points: 3228 More actions · Answer 14

Mani

Were you able to restore this database after your log issue?

David

Mani-584606 SSChampion Points: 11788 More actions · Answer 15

Yes, Iam able to restore the database.I copied the the fullbackup of the database to a test server and did the restore and its worked.