How large should the transaction log be?



By:

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




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download





get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools