From time to time we have received questions about the database transaction log growing too large unexpectedly.  In some cases several gigabytes overnight!   The aim of this short article is to help you to understand the probable cause of this situation and offer some easy solutions.

The Transaction Log
Every SQL Server database has at least two files associated with it:

1) the data file that contains the actual backed up data
2) the transaction log file.

The transaction log is a basic component of a database management system. All changes to the data in are recorded in the transaction log.  By referencing the log the changes can be tracked and restored in the event of a crash.  

Together with the data file it is then possible to restore a database up to the last transaction rather than only to the last backup.

The Cause of Unexpected Transaction Log Growth
The most common cause of the unexpected growth of the transaction log is because the database is set up for Full Recovery.   This means that the transaction log will be allowed to grow until the next backup of the database.   If no action is taken the transaction log may grow, fill the disk, and crash the system.   Unfortunately, full recovery mode is the default mode used by SQL Server.

Solutions Transaction File Overgrowth
Here are a few steps you can take to prevent your transaction log from becoming extremely large:

1)  You can set the size of the transaction log to a large value to avoid automatic expansion.
2)  Change the recovery model to simple.   This is probably the best solution for keeping the transaction log file from growing.   With simple recovery the transaction log should never become overly large since the changes in the log space will be reclaimed when performing a checkpoint and not only during a backup.   The obvious drawback is that transactions since the last back up will not be stored.   In the event of a crash the database can only be restored to the last back up. 

Recovering From Transaction Log Overgrowth
If you have an overgrown log file you must shrink the log in order to recover from such a situation.   You can do this by backing up the log file which will remove the inactive transactions and reduce its size.   After reducing the size of the log file you may want to move the database into simple mode.  

A further suggestion would be to do a complete backup and restore of the database which will allow you to start the log file off from a clean slate.   You can set it to simple mode upon restoration.   If you have an active site, you will have to stop the site in IIS and its App pool.   This will allow you to take the database offline in order to back it up and finally delete it.   Before deleting the database be sure to make an extra copy of the database as SQL Server may delete the database and any backups.  

Below is a description of the 3 main back up models:

The Full recovery Model
This model creates a complete backup of the database as well as an incremental back up of all the changes that have occurred since the last full back up. The best thing about this model is that it's possible to recover the database to a particular point in time. Selecting this mode would require you to have enough space available for the transaction log to store all the transactions that occur between each backup because the space in the transaction log is only reclaimed when a backup of the transaction log is made.

The Bulk-Logged recovery Model
Bulk-logged recovery is quiet similar to the Full Recovery model but the Bulk copy operations are only minimally logged in this recovery Model thereby giving you better performance, i.e. the SQL Server only logs the minimum necessary to recover the data if a backup is needed and due to this sole reason if a bulk copy operation occurs, point-in-time recovery is not possible.

The Simple recovery Model
The simple model as the name suggests, is the easiest one to manage. It allows only full back ups and the drawback in this case is that there's no way you can back up only the changes made since the last back up. But considering the fact that the transaction log would hardly become full because of the transactions occurring between the full backups, this model would be beneficial. When using this Recovery Model, the space in the log is reclaimed whenever the database performs a checkpoint.

Recommendations:

1) Use Full Recovery Mode if you have specific requirements requiring detailed logging and disaster recovery for your ecommerce implementation.  If you choose this mode, try to ensure that you keep the log files and data files on separate storage mechanisms or partitioned on separate drives on your storage area network.   Also, it would be advisable to follow the best practices described by Microsoft for SQL server running in this configuration.   Lastly, a word of caution – make sure that you have a recovery process planned and tested before you go into your live production environment with this configuration.  

 

2) Use Simple Mode if you do not have the specific requirements or needs as outlined above.  Many companies may be fine going with the Simple Mode option as long as you make sure that databases are backed up on a daily basis and a history of at least several days of backups are maintained.  In the worst case scenario, assuming your data is not mission critical, you can roll back to the previous day’s database backup even with Simple Mode.

For more information please see:  "How to stop the transaction log of a SQL Server database from growing unexpectedly"

Additionally:  "Transaction Log Guidelines"