November 13, 2012 at 4:30 pm
I've got a sql 2000 DB with a .mdf size just under 3GB. the log file is about 2GB at this time. Most of the hourly/daily log backups are very small, between 2.5MB down to as small as 140K when there's little to no activity. However, just after my data backup maintenance plan runs the translog backup file is closer to 2.5GB. This plan runs sunday evenings only and backs up the DB and also runs the 'Reorganize data...' step under Optimizations.
I also run a translog plan daily that backs up the Trans log only, once each business hour.
Occasionally this server runs a bit low on disk space, so I'd like to know if there's any adjustment I could make to either maintenance plan that might result in a smaller translog backup after the Reorg Data operation.
Thanks for any feedback!
November 13, 2012 at 6:12 pm
Sounds like you are rebuilding indexes in your maintenance plan. Index rebuilds are a fully logged operation. Even if you switched to bulk logged recovery model the t-log backup would be large even though the t-log itself would be smaller.
November 13, 2012 at 6:54 pm
Lynn Pettis (11/13/2012)
Sounds like you are rebuilding indexes in your maintenance plan. Index rebuilds are a fully logged operation. Even if you switched to bulk logged recovery model the t-log backup would be large even though the t-log itself would be smaller.
Thanks, Lynn. Yes, I've got 'Reorganize data and index pages' checked in the plan that runs weekly. Sounds like there's no way to reduce the size of this log backup when it's created just after the reindex. I guess I could move/save it off to another drive after it gets created (to free up the drive space).
Thanks again.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply