By: Brady Upton
This is a question I’ve seen come up more and more throughout my years as a DBA. How large should the transaction log be? Well….it depends.
For normal day to day operations, I would recommend starting at 25% of the data file size. So if your data file is 20GB, make the log 5GB. Keep an eye on the log to see if it continuously grows beyond 5GB. Like we talked about in the VLF section, every time your log grows you add more VLF’s which can cause performance problems. 25% is the recommended starting point for normal operations, however, if you’re doing index rebuilds every night or if you’re using high availability options such as replication or mirroring, you’ll need enough log space to hold onto the extra transactions that may occur.
25% is just a recommended value to start with. Realistically you should use a test server to examine how much log space is used at average and peak times and adjust your production server leaving a little extra space to spare.
Last Update: 3/25/2014
