May 8, 2012 at 1:28 pm
I have a database with a 6GB transaction log, which at the time I attempted a tlog backup, was about 50% full. Due to an oversight, the tlog backup job had been left disabled. It had been about 8 hours since the last full backup. When we kicked off a transaction log backup we were surprised that the backup file started growing much larger than the .ldf. It had grown to over 100GB when we decided best to kill that and wait till after the next full backup to reenable the log backups. At no time since the last full had the recovery model been changed- it was full the entire time.
I'm having trouble understanding how this can happen... under what circumstances (other than bulk-logged which I understand and is not what happened here) can the log backup be bigger than the log?
Thanks in advance for your thoughts.
May 8, 2012 at 1:34 pm
Full backups (and differential backups) do not truncate the transaction log. The only way to manage the size of the transaction log is by running t-log backups. I have no explaination for why a 6GB t-log would take over 100GB of space in a log backup file.
May 8, 2012 at 1:36 pm
Were any large tables truncated during the time the t-log backups were disabled?
May 8, 2012 at 1:50 pm
Probably not, but the example does help me understand how this could happen. Possibly several smaller tables truncated repeatedly, though I would be surprised if it amounted to 100GB worth.
What about blob objects- does SQL Server handle these differently in with regards to the transaction log?
May 8, 2012 at 2:01 pm
Not sure. I thought the deallocations of the blob pages might be stored in the log, but I can't find a definitive reference. How large is the LDF. Could there possibly be 100GB of deletions?
If you need to get out quickly, can you take a full backup now? If you can, I'd switch to simple recovery mode, checkpoint, switch back to full, and then take a full backup.
May 8, 2012 at 2:07 pm
Just a guess, but are you using native SQL backups? Or is some 3rd party software running the backups?
Some 3rd party software will detect if a backup outside of it has been taken. Since this breaks the log chain, the backup software kicks off a full backup to reestablish the log chain. This might be why you are seeing 100GB backup. It could be a full backup.
May 8, 2012 at 2:15 pm
Using litespeed, but the problem which caused us to disable the tlog backups, was litespeed related, so when we picked it up, we went with a native backup of the transaction log. So, the full was a litespeed backup, tried to follow it with a native backup.. .is that a no go?
The .ldf is 6GB... transaction log backup was 100GB when we killed it and only about 40% complete.
May 8, 2012 at 2:19 pm
in the interest of full disclosure heres how it all went down:
Friday: change to simple mode, then back to full. Kicked off full litespeed backups. Litespeed transaction log backups started before full finsihed and litespeed had a minor stroke. it does not handle this error properly and spawns more and more attempts to backup... we ended up restarting the service and disabling transaction log backup job.
Saturday - Monday: forgot to reenable tlog backups. Daily full backups only (litespeed)
Tuesday: realize our mistake. Take a diff backup of all DBs (native). no problem.
Take a log backup(native) of some dbs where tlog is small, no problem
Take a log backup(native) of a db where the log 6GB in size, 3GB used.... generated over 100GB of transaction log backup before killing it.
May 8, 2012 at 2:23 pm
Steve Jones - SSC Editor (5/8/2012)
Not sure. I thought the deallocations of the blob pages might be stored in the log, but I can't find a definitive reference. How large is the LDF. Could there possibly be 100GB of deletions?If you need to get out quickly, can you take a full backup now? If you can, I'd switch to simple recovery mode, checkpoint, switch back to full, and then take a full backup.
Hi steve- cant really take a full right now. We'll take periodic diffs until the full runs tonight as scheduled, then start with our tlog backups after that. really was more interested in how this can occur.
May 8, 2012 at 5:56 pm
NJ-DBA (5/8/2012)
Steve Jones - SSC Editor (5/8/2012)
Not sure. I thought the deallocations of the blob pages might be stored in the log, but I can't find a definitive reference. How large is the LDF. Could there possibly be 100GB of deletions?If you need to get out quickly, can you take a full backup now? If you can, I'd switch to simple recovery mode, checkpoint, switch back to full, and then take a full backup.
Hi steve- cant really take a full right now. We'll take periodic diffs until the full runs tonight as scheduled, then start with our tlog backups after that. really was more interested in how this can occur.
actually, dont even need to take a full... can change to simple recovery, back to full the do a diff, then start tlog backups, as described by Mr. Randall here: http://www.sqlskills.com/BLOGS/PAUL/category/BackupRestore.aspx
May 9, 2012 at 8:44 am
NJ-DBA (5/8/2012)
Wow, very nice reference. I missed that somehow. I was under the impression a full was always needed, but good to know a diff works.
Here's the actual post for those looking for it: http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-%282030%29-restarting-a-log-backup-chain-requires-a-full-backup.aspx. I had to dig through the page a bit to find it.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply