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.
Brady has been in the IT industry for 10+ years. He has worked in administrative roles using MSSQL 2000 to 2012 as well as Sharepoint 2007 and 2010. He currently serves as a Database Administrator in Nashville, TN. You can view his blog @ http://www.sqlbrady.com.
- MSSQLTips Awards: Trendsetter (25+ tips) – 2013
My log in is on emergency, not normal. My security sign is to small.