Overview
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.
Explanation
Size of the Transaction Log
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 VLFs, 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.
