Request: An emPath user recently asked about how to reduce the size of the transaction log when using SQL Server. Specifically they ask:
Now that we are running emPath, on SQL Server 2005, I noticed the log file grows quite fast.
Would you suggest putting a limit on the file size? I realized with SQL Server 2005 the auto-shrink setting if off by default. Should this be set to on?
Suggestion: I recommend periodic transaction log backup and truncation. Following is some detail:
From review of the literature on the subject I think the proper thing to do is to periodically backup the transaction log file and truncate it during the backup. Naturally the backups should be saved. Following are a couple of helpful links on the subject:
Note that it is possible to create a batch job, using Transact-SQL, which can be scheduled to run regularly. If you are interested I would be pleased to contract to deliver such a job.
As for your question about the auto-shrink option. Best practice documentation indicates this should be set to off.
As for your question about limiting the file size. I recommend against limiting the file sizes. Instead I recommend periodic transaction log backup and truncation as described above.
I hope this information is helpful. Let me know if I can be of assistance