November 23, 2011 at 2:58 am
Hi. I have a problem with transaction logs backup (SQL 2008 R2 Web edition). Full database backup completes fine, but transaction log backups started failing at some point with the following error:
Failed:(-1073548784) Executing the query "BACKUP LOG [myDB] TO DISK = N'D:\\\\myDB\\myDB_backu..." failed with the following error: "BACKUP detected corruption in the database log. Check the errorlog for more information.
BACKUP LOG is terminating abnormally.
10 percent processed.
20 percent processed.
30 percent processed.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
I've done DBCC CHECKDB and it completed without errors (also tried DBCC CHECKDB WITH ALL_ERRORMSGS,NO_INFOMSGS).
I've had this problem couple days ago and switching the DB from Full to Simple recovery mode and back helped for some time, but the problem reappeared.
I'm not a DBA and from what I've read, most online articles seem to deal with corruption in the database, not in the log.
Any hints on how to diagnose/remedy this further?
Thanks
November 23, 2011 at 3:16 am
I've had this problem couple days ago and switching the DB from Full to Simple recovery mode and back helped for some time, but the problem reappeared.
First, you shouldn't mess-up with Database Recovery Model. What's the recovery model now? What was the recovery model when you last backed up? Is there any recovery model switch in between?
November 23, 2011 at 4:18 am
1) Are you sure there is no physical(disk/network) problem in placing the log file?
2) Have you tried differential backup? Are you getting the same error?
Probable solution for your problem :-
Take full backup, change recovery model to simple, change it back to full. Take full backup and start log backup job.
----------
Ashish
November 23, 2011 at 4:20 am
That was an immediate switch, the recovery model is Full ever since the switch.
The switch was made based on the following artictle: http://sqlskills.com/BLOGS/PAUL/post/How-can-a-log-backup-fail-but-a-full-backup-succeed.aspx
I know it breaks backup chain, but it wasn't intact anyway since the log backups didn't work or?
November 23, 2011 at 4:49 am
Rambler (11/23/2011)
That was an immediate switch, the recovery model is Full ever since the switch.The switch was made based on the following artictle: http://sqlskills.com/BLOGS/PAUL/post/How-can-a-log-backup-fail-but-a-full-backup-succeed.aspx
I know it breaks backup chain, but it wasn't intact anyway since the log backups didn't work or?
So you are saying Paul is resposible for it... 😀 :hehe:
Did your FULL backup work?
How much data / transaction you will lose if you miss this log restore?
What all backups you have? Please provide more information.
November 23, 2011 at 5:10 am
crazy4sql:
re 1) I'm fairly sure as I've ran chkdsk on the drive without errors. The drive is logical partition on RAID1 array and there are other databases and their logs on the same drive and only this one DB (its log) is exhibiting this problem. The .mdf of this problematic DB is on the same partition.
re 2) Haven't tried differential backup - not sure how would that solve the problem? We need to be able to do transaction logs
dev:
No, I'm not saying Paul is responsible, I just referenced the article whose suggestion I've used 🙂
Full backup works fine (scheduled every night), trans. log. backup is scheduled to run hourly, so when it's not working at all, we might miss 24hrs of data, which is not acceptable. We keep backups several days back based on the free space on the storage.
November 23, 2011 at 5:18 am
Rambler (11/23/2011)
crazy4sql:re 1) I'm fairly sure as I've ran chkdsk on the drive without errors. The drive is logical partition on RAID1 array and there are other databases and their logs on the same drive and only this one DB (its log) is exhibiting this problem. The .mdf of this problematic DB is on the same partition.
re 2) Haven't tried differential backup - not sure how would that solve the problem? We need to be able to do transaction logs
dev:
No, I'm not saying Paul is responsible, I just referenced the article whose suggestion I've used 🙂
Full backup works fine (scheduled every night), trans. log. backup is scheduled to run hourly, so when it's not working at all, we might miss 24hrs of data, which is not acceptable. We keep backups several days back based on the free space on the storage.
It's the 1st Log Backup in the chain (after FULL backup)? :w00t:
Do you have any alternate backup plans like OS backups?
November 23, 2011 at 5:26 am
One more question. Are you doing this with Maintenance Task? If yes please refer following (SQL Server Agent permissions).
November 23, 2011 at 5:53 am
CheckDB doesn't check the log, so corruption in the log can still return a clean checkDB.
About the only way to remove corruption in the log is Paul's solution of switching recovery models (safe providing you know what you're doing and why and you take a full/diff backup after) or rebuilding the log (which is not a nice thing to do and should be reserved for those times when the above doesn't work and there's no restorable backup.
If it's happening again, I'd be looking long and hard at the disk subsystem, running diagnostics, checking logs and maybe even moving the DB to alternate storage. Repeated corruption's a sign there's something wrong in the hardware somewhere. It doesn't necessarily have to be the disks themselves, it could be anything in the IO stack (filter drivers, firmware, fibre switches if applicable, etc, etc)
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
November 23, 2011 at 6:00 am
Thanks GilaMonster, I'll try the recovery switch mode once again and see if it comes back again.
I'll do some additional HW diagnostics if it reveals something.
I was aware of the fact that checkdb will not show log corruption, I just wanted to make sure that there's no DB corruption. To make it clear for me, DB and log corruption are two totally different things, right?
November 23, 2011 at 6:03 am
Rambler (11/23/2011)
Thanks GilaMonster, I'll try the recovery switch mode once again and see if it comes back again.I'll do some additional HW diagnostics if it reveals something.
I was aware of the fact that checkdb will not show log corruption, I just wanted to make sure that there's no DB corruption. To make it clear for me, DB and log corruption are two totally different things, right?
Data <> log
November 23, 2011 at 6:05 am
Rambler (11/23/2011)
Thanks GilaMonster, I'll try the recovery switch mode once again and see if it comes back again.I'll do some additional HW diagnostics if it reveals something.
I was aware of the fact that checkdb will not show log corruption, I just wanted to make sure that there's no DB corruption. To make it clear for me, DB and log corruption are two totally different things, right?
That you should do anyway...
Sorry, if I am pushing it on you. Are you using maintenance tasks?
November 23, 2011 at 6:41 am
Rambler (11/23/2011)
Thanks GilaMonster, I'll try the recovery switch mode once again and see if it comes back again.I'll do some additional HW diagnostics if it reveals something.
If that were my DB, I would also seriously start looking to see if there was alternate storage for it, even temporarily.
I was aware of the fact that checkdb will not show log corruption, I just wanted to make sure that there's no DB corruption. To make it clear for me, DB and log corruption are two totally different things, right?
Log is a subset of database.
CheckDB will pick up data file corruptions (mdf, ndf) and may see log file corruptions in some cases
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
November 24, 2011 at 3:38 am
In first glance, it looks like a hardware fault. There should be some bad sectors in the disk you are using for the log file.
Have you tried to take the log backup from query?? Is there any error/warning related to the I/O in SQL Server error log/ event viewer ?
-Sujeet
November 24, 2011 at 8:02 am
Just a thought - if you have the window to kick the users out of the system for some time.
1) Perform a full backup...just to be safe
2) Set database to single user/restricted user
3) Switch database recovery to simple
4) Shrink the log file to as small a size as possible
5) Regrow the log in no more than 4GB or 8GB chunks to it's normal size
6) Switch recovery back to full
7) Perform a full backup
8) Start up your transaction log backups
9) Set database to multi-user
My thought here is that shrinking the file and growing it back out will more or less rebuild the file and should get around any bad sectors on the disk. As Gail stated, if this comes back again - you have a hardware issue and really need to move to an alternate solution.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply