Transaction logs are one of the most critical parts of a database and should be managed as such.

Running out of disk space can cause application errors and even database corruption.

If your transaction log is continually growing there are a few things to check.

Check the Recovery Mode

First, it is important to understand the database recovery mode since it will impact what gets logged.

There are three recovery models available.

1. FULL recovery mode will log everything- every part of every operation. This is desirable for highly transactional systems where losing even the smallest amount of data can cause significant re-work and business cost.  In FULL recover mode you typically take a FULL backup. However, after that point the Transaction log will not automatically check point and make space available for reuse. In order to have the transaction log make space available for re-use you need to take a transaction log backup. Most DBAs who need this level of recovery typically perform transaction log backups every 15 or 30 minutes, depending on the business acceptable threshold for data loss caused by a disaster.
2. BULK_LOGGED recovery mode has the same transaction log reuse rules as the FULL recovery model except certain operations, such as index rebuilds and other things, are partially logged instead of being fully logged. Items that are partially log only log allocation changes instead of everything.
3. SIMPLE recovery mode has the same logging behavior as the BULK_LOGGED, however how the transaction log check points for re-using space is very different. In SIMPLE mode Transaction Log backups are not allowed and the space can be marked for re-use as soon as the next check point runs as long as nothing else is holding log records active.

Check the Auto Growth Settings

By default most DBAs will allow their database files (mdf / data files and ldf / log files) to auto grow and they monitor disk space usage over time to make sure they have enough resources to support the operations.

In some rare cases your Log file may be restricted to a certain size limit. You can remove the restriction to allow the log file to grow more.

About file sizing and auto growth:

Unless you have changed your defaults in the Model database Microsoft’s defaults for auto growth are not very good (Typically 1MB data file and 10% log file).

Auto growth is an expensive operation and you do not normally want it firing off constantly.

You should appropriately set your initial sizes to slightly larger than you are likely to need for your normal operation budgeting period. For example: if you review hardware resources annually for budgeting, and then set your auto growth to a reasonable size that should accommodate what you expect to need for the year based on normal operations. Then you can set the MB to auto grow to a reasonable setting in case your estimated needs were slightly off.

In order to handle disk space issues some people will continually shrink their log file. If your databases files are appropriately sized and you just had a struck transaction then this may be a solution. However, in most cases you have just started a chain of auto-growth processes that will run until all of the space you just made available is allocated for use once again, usually within a day or two.

Add More Space

There are a few ways to add space to a database for logging. You can see most of them on MDSN for Troubleshooting a Full Transaction Log

Managing your transaction logs is a primary facet of any Disaster Recovery plan. I will go more into that in a future blog post.

Do you have transaction log stories? Let’s discuss in the comments.