log backup very large after full backup

Question

log backup very large after full backup

Rambler

Ten Centuries

Points: 1301
More actions
February 7, 2013 at 9:13 am

#273926

Hi. Using SQL 2008 R2 Web edition, we're doing daily full backup of all databases (at night), followed by log backup every hour.
The problem is that for some databases, the first log backup after the full is very large, sometimes even larger than full backup.
Example: Current total DB size is ~45GB (autogrowth on, currently 30MB free, MDB 12.5GB, LDF 32.5GB), full backup has always approximately the same size of ~13GB (which is to be expected), the first subsequent log backup (in past 4 days) ranged from 4 to 31GB. Full backup occurs at 3:30, first log backup at 5:00. All subsequent log backups have "normal" size of 1-2GB.
Example2: total DB size ~350MB (autogrowth on, currently 30MB free, MDB 350MB, LDF 720MB), first log backup has ~700MB, subsequent log backups 300kB-30MB.
Why is the first log backup so large and is there a way to make it smaller? I don't believe there's so much activity to make up for 30GB in 1.5hr window between full and first log.
The maintenance plan has no other maintenance jobs such as index defrag, shrink or whatever.
Thanks in advance

Viewing 5 posts - 1 through 4 (of 4 total)

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

Richard Fryar Hall of Fame Points: 3457 More actions · Answer 1

Do you allow log backups to continue overnight, or is there a big gap since the last one when you run the 5am job? The full backup does not break the log chain so the 5am log backup will contain all transactions since the previous log backup.

This will include index rebuilds, and any user activity during that time.

Check Your SQL Servers Quickly and Easily
www.sqlcopilot.com

Steve Jones - SSC Editor SSC Guru Points: 736210 More actions · Answer 2

The log backup has nothing to do with the full backup. It's all activity since the last log backup.

What activity has taken place since the last log backup?

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

Rambler (2/7/2013)
Hi. Using SQL 2008 R2 Web edition, we're doing daily full backup of all databases (at night), followed by log backup every hour.
The problem is that for some databases, the first log backup after the full is very large, sometimes even larger than full backup.

Done some maintenance like index rebuild since the previous log backup? That 'first' log backup is nothing of the sort, it's just one in the log chain and it will have all log records since the previous log backup in 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

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

Rambler Ten Centuries Points: 1301 More actions · Answer 4

Hi guys, thanks for the responses.

I found the culprit, it's embarrassing that I haven't noticed this before - actually the backup drive was running out of space and log backups would fail every day from early afternoon (older backups are being deleted every night). So that's why the log backup after full was so big, because the previous happened sometime in the afternoon and there was a ~10hr gap.

I guess for other databases there was so little activity, that it actually didn't cause a log backup growth.