Why does the transaction log grow?



By:

Like we’ve already discussed, the transaction log records all database modifications. If you have a busy system, this alone could cause the transaction log to grow. If Autogrow is not enabled, once the log file hits the specified maximum size, it will throw an error on every transaction that hits the database until the problem is fixed. I always recommend turning on Autogrow, but keep an eye on your transaction log file. Also, specifying a small autogrowth increment on your log file can reduce performance. The file growth increment on a log file should be sufficiently large enough to avoid frequent expansion. The default is 10 percent and this may be sufficient for you environment. I would start at 10 percent, but keep an eye on the log to see how often it grows and adjust accordingly. Using a fixed increment instead of percentages may work better for your environment. There really isn’t one setting for all databases, so you need to monitor log usage to figure out the best setting for each database.

To enable Autogrow in SSMS, right click the database and select properties. Under the Database properties, choose the Files tab and adjust Autogrowth / Maxsize:

To enable Autogrow in SSMS, right click the database and select properties.

Other factors that will cause the log file to grow may include the following:

  • Uncommitted transactions
  • Index Operations - CREATE INDEX, rebuild indexes, etc.
  • Un-replicated transactions
  • Long running transactions
  • Incorrect recovery model settings (we'll discuss this later)
  • Large transactions

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.





Tuesday, August 20, 2019 - 7:23:55 AM - kirtan pankajkumar khatsuriya Back To Top

May i know the difference between large trasaction and long running transaction?



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