Overview
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 and use a lot of space.
Explanation
Autogrow Option
When the log file is created, an initial file size is given. If Autogrow is not enabled for the database, 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 your 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.
Enabling Autogrow
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:
- 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
