large transaction log backups

Question

Post reply

large transaction log backups

torpkev

SSCarpal Tunnel

Points: 4819
More actions
December 1, 2008 at 7:17 am

#203640

Hi all,
I have a database that I do transaction log backups every 4 hours on, and nightly full backups. My transaction log's are normally in the <200k range, except on a Sunday between midnight and 4am, it rockets up to 70Gb. This would be fine, however, it fills my drive, and so my nightly backup fails.
The only thing different with Sunday between midnight and 4am that I can find is that I do:
Reorganize data and index pages (change free space per page percentage to 10%)
Check database integrity (include indexes)
Could either of these be causing the massive transaction log backup? If so, can anyone suggest a way to avoid the large transaction log backups?
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂

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

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

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

torpkev (12/1/2008)
Reorganize data and index pages (change free space per page percentage to 10%)

There's your culprit. Index rebuilds are fully logged and have nasty effects on a tran log.

You can try switching the database to bulk-logged recovery before running that and back to full afterwards (and take a tran log backup after going back to full)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

torpkev (12/1/2008)
Hi all,
I have a database that I do transaction log backups every 4 hours on, and nightly full backups. My transaction log's are normally in the <200k range, except on a Sunday between midnight and 4am, it rockets up to 70Gb. This would be fine, however, it fills my drive, and so my nightly backup fails.
The only thing different with Sunday between midnight and 4am that I can find is that I do:
Reorganize data and index pages (change free space per page percentage to 10%)
Check database integrity (include indexes)
Could either of these be causing the massive transaction log backup? If so, can anyone suggest a way to avoid the large transaction log backups?

There are two things you could do. The first, prior to your maintenance processes, change the recovery model from full to bulk-logged. When completed, change the recovery model back to full, then run a full backup. The second, schedule more frequent t-log backups during the maintenance window. Perhaps every hour, or more frequently if necessary.

torpkev SSCarpal Tunnel Points: 4819 More actions · Answer 3

Thanks a bunch.. that makes sense

--------------------------

I long for a job where my databases dont have any pesky users accessing them 🙂

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 4

Or just leave the log size larger. It's obviously needed for the operation and a larger log doesn't affect the way your system runs (other than needing disk space).

I'm not a fan of constantly trying to save a little space for the log when it will be reused again soon.