By: Brady Upton
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:
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
