2

I have 2 SQL Server 2008 Standard Edition instances that are mirrored. Both instances are running Service Pack 1.

I run Full backups daily and transaction log backups every half hour (through maintenance plans).

My physical DB file is ~22GB. My physical log file has grown to ~41GB.

The log seems to grow the most when running a weekly INDEX_REORGANIZE. Last week it grew by 15GB. The previous week by 10GB.

My full backups are now ~50GB in size. Shouldn't that be around ~22GB? I feel that the transaction log backups are not properly clearing out space from the log file.

What is the safest way to shrink the transaction log file? I was looking at running dbcc shrinkfile after a transaction log backup.

  • BTW: the script I use to reorg the DB is here: ola.hallengren.com/… – xweb Dec 22 '15 at 20:53
  • 2
    If your log is growing that much consistently, you'll want to leave it, otherwise all you're doing is saving space until the next growth event. At that point, SQL will just grow the log (again) and you'll have increased wait times while SQL grows the file (this adds time to your process). As for your backup file - are you appending to the existing file or are you creating a new file every time? – 8bit Dec 22 '15 at 21:12
  • I create a new file every time. – xweb Dec 22 '15 at 21:18
  • 2
    You can use WITH COMPRESSION in Standard as well in 2008 R2 and up, so that might be enough motivation to get onto something more modern than SQL Server 2008. – Aaron Bertrand Dec 22 '15 at 22:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.