SQL Server 2008 - Shrinking the Transaction Log - Any way to automate?

Question

I went in and checked my Transaction log the other day and it was something crazy like 15GB. I ran the following code:

USE mydb
GO
BACKUP LOG mydb WITH TRUNCATE_ONLY
GO
DBCC SHRINKFILE(mydb_log,8)
GO

Which worked fine, shrank it down to 8MB...but the DB in question is a Log Shipping Publisher, and the log is already back up to some 500MB and growing quick.

Is there any way to automate this log shrinking, outside of creating a custom "Execute T-SQL Statement Task" Maintenance Plan Task, and hooking it on to my log backup task? If that's the best way then fine...but I was just thinking that SQL Server would have a better way of dealing with this. I thought it was supposed to shrink automatically whenever you took a log backup, but that's not happening (perhaps because of my log shipping, I don't know).

Here's my current backup plan:

• Full backups every night
• Transaction log backups once a day, late morning (maybe hook the Log shrinking onto this...doesn't need to be shrank every day though)

Or maybe I just run it once a week, after I run a full backup task? What do you all think?

Answer 1

If you file grows every night at 500 MB there is only one correct action: pre-grow the file to 500MB and leave it there. Shrinking the log file is damaging. Having the log file auto-grow is also damaging.

• you hit the file growth zero fill initialization during normal operations, reducing performance
• your log grows in small increments creating many virtual log files, resulting in poorer operational performance
• your log gets fragmented during shrinkage. While not as bad as a data file fragmentation, log file fragmentation still impact performance
• one day the daily growth of 500MB will run out of disk space and you'd wish the file was pre-grown

You don't have to take my word for it, you can read on some of the MVP blogs what they have to say about the practice of log and file shrinkage on a regular basis:

• Auto-shrink – turn it OFF!
• Oh, the horror! Please stop telling people they should shrink their log files!
• Why you want to be restrictive with shrink of database files
• Don't Touch that Shrink Button!
• Do not truncate your ldf files!

There are more, I just got tired of linking them.

Every time you shrink a log file, a fairy loses her wings.

Answer 2

I'd think more frequent transaction log backups.

Answer 3

I think what you suggest in your question is the right approach. That is, "hook the Log shrinking onto" your nightly backup/maintenance task process. The main thing is that you are regularly doing transaction log backups, which will allow the database to be shrunk when you do the shrink task. The key thing to keep in mind is that this is a two-step process: 1) backup your transaction log, which automatically "truncates" your log file; 2) run a shrink against your log file. "truncate" doesn't necessarily (or ever?) mean that the file will shrink...shrinking it is a separate step you must do.