jonathanforster (1/12/2013)
1) I've already run the first 80GB tlog backup and was under the impression that every subsequent tlog backup would be the same size until I used a SHRINK on the tlog? I can't efficiently test this because I would run out of space if indeed the second tlog backup was the same size.
No. A log backup will be the size of the log records backed up, not the size of the log file. Just as a database backup is the size of the data in the database, not the size of the database files
2) My idea before was that after a SHRINK I could control the size of the tlog with regular tlog backups and just leave the growth increment to the default (10%).
10% is a poor choice. It leads to lots of tiny grow operations when the log is small and huge, time-consuming log growths when the log is large. Set your growth increment to a fixed size
You said that I shouldn't let it grow 'dynamically' by this do you mean that I shouldn't SHRINK it to a really small size and let it grow by itself? This would create a performance issue?
No, don't do that. Shrink the log to the size needed for normal operations + some head room (yes, you may need to do some monitoring to tell that value), set a sensible increment value (fixed MB, not %)
3) The advised procedure on SHRINKING the tlog after the initial 80GB tlog backup includes a CHECKPOINT. I understand this writes the 'dirty' pages from memory onto the disk. What's the benefit of doing this before the SHRINK?
In full recovery model, none. In simple that would truncate the log (mark as reusable) potentially allowing shrink to a smaller size
Are there any negative impacts of running the CHECKPOINT (I'm guessing it doesn't break the backup log chain)?
Seeing as SQL runs automatic checkpoints on a regular basis during normal operations, no negative impacts.
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