March 6, 2009 at 11:12 am
Hi,
My log backup has failed and I find that the database has corrupted message as below.
Plz advice what are the immediate steps I need to perform
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.
March 6, 2009 at 12:32 pm
I ran dbbc checkdb, it did not give any errors. So Iam in safe orI need to check any other important items.please advise me..
March 6, 2009 at 1:43 pm
March 6, 2009 at 2:26 pm
However the article does not over the case of the corrupt log, which I think is what we have here.
I'm gonna leave this one for Paul Randal, I know he'll see it sooner or later, because I'm not sure how to deal with log corruption.
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 6, 2009 at 2:43 pm
Thanks Gail,
is the log corruption in the database is different from databse corruption? I ran DBCC CHECKDB and did not give me any errors.After the DBCC CHECKDB, I took the Full backup and it is fine But when Iam trying to take the log backup it is giving me the same error that the log of the database is corrupted. Then I chenged the log backup path and took the backup, it is fine now and I changed back the log path to before as it was and took the log backup then there is no errors.
So from above, does that mean that database is good? or still is it has corruption? what are the other things I need to do inorder to make the database log and database has no corruption..
Please advice me....
March 6, 2009 at 2:58 pm
Is the database still operational? If the log is corrupt I would suspect the database would come up as corrupt and\or be unable to process.
I would try this:
1. On a test server create a database with same database name and file names
2. Stop the service of the on the server havng problems.
3. Stop the service on the test system
4. Copy the database files to the test server overwriting the database created in step 1.
5. Start the service on the test server.
6. If the database comes on line, detach it.
7. Delete the log file
8. Re-attaching it should generate a new log file.
9. IF this works, Run checkdb if it comes up clean you can run steps 5 through 9 on your problem server.
Beware you, should have a backup ready to restore. If the log is corrupt the database might not come back online with the restart and you might not be able to generate a log as described about.
I do have the steps to force a log create (done in 2000) but that means being willing to accept a tranactionally inconsistent database. Restoring to your last good backup would be a much better choice. The one time I battled a corrupt log I didn't have a backup and it was dev so I could accept a transactionally inconsistent DB.
Good Luck.
David
March 6, 2009 at 4:31 pm
The database online and its running normally, I mean not in suspect mode. Is this the sign of good database?
March 7, 2009 at 12:51 am
David O (3/6/2009)
6. If the database comes on line, detach it.7. Delete the log file
Sorry for been blunt, but that is very, very bad advice when it comes to suspected or actual database corruption. It's very possible for a corrupt database that's been detached to not attach again and by deleting the log file you may lose uncommitted transactions, if you can ever get the DB up again that is.
I know you said on a test system, I just want to clarify that detach/attach is Not A Good Thing. Even restarting the service may send a database that's corrupt but still available into a suspect state.
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 7, 2009 at 9:53 am
Yes, to what Gail said about bad advice - don't ever detach a broken database.
Back to the original problem - the log was corrupt, but in an interesting way. The full backup succeeded because it only needed to backup enough of the transaction log to make the restored full backup transactionally consistent. However the log backup has to backup *all* transaction log since the last log backup, which in this case was more than for the full backup. So although it looks strange that the full backup succeeds, that's expected behavior.
Same thing with running a DBCC CHECKDB - the database snapshot that DBCC CHECKDB creates under the covers only requires the transaction log necessary to make the snapshot transactionally consistent - and in this case it didn't need to go back as far as the corrupt section of log.
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
In between 2 and 3 you might want to manually shrink the log file and then grow it again, just in case the log file includes some bad disk sectors.
Perform some root cause analysis of the corruption to see why it occured - check the I/O subsystem for errors, check firmware levels etc etc
Hope this helps
PS I'll turn this into a blog post
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
March 7, 2009 at 10:04 am
Thanks Paul
Paul Randal (3/7/2009)
PS I'll turn this into a blog post
Awesome. I look forward to it.
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 7, 2009 at 10:13 am
Great Paul,
I as waiting on your reply too. I follow your blogs regularly about Corruption but did not find anything similar to this.
So, as what I understand is that the last portion of the log is corrupt, isn't it? database does not want to back up the corrupted log?
But the OP did say that he was able to take log backup successfully after changing the log backup path? How is that possible if the log itself is corrupt?
PS Would love to see some deep facts about this in your blog 🙂
thanks!!
March 7, 2009 at 10:23 am
Here's the post, with links to deeper explanations in each case How can a log backup fail but a full backup succeed?
Be sure to vote in the backup verification poll I link to at the end.
Cheers
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
March 7, 2009 at 10:24 am
GilaMonster (3/7/2009)
Thanks PaulAwesome. I look forward to it.
Awesome Gail two more for you to reach10,000 :w00t: good one, why don't you reply to make it 9999 and then one more for 10,000 woo hoo.......:)
March 7, 2009 at 11:01 am
Obviously, if your choice is to follow Paul Randall's advice or mine, that is officially a no-brainer.:D
That said, I have successfully used the advice I gave. I think caveats were clear and appropriate. I stand by post.
Paul and Gail, as always thanks for your contributions to the Sql Server tech community.
David
March 7, 2009 at 11:15 am
David - your post is more or less ok - you're not advocating detaching the database from the prod system. However, on the test system, if the database comes online and then you detach it, you may or may not be able to attach it again without a log file. That only works if there are no active transactions in the log, as my blog post shows. If the database comes up in the ONLINE state, then there's no need to rebuild the transaction log at all - so not sure what you mean by those steps.
Hope this explains more what I meant.
Cheers
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
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply