SQLSimon.com

When a DML (Data Manipulation Language) statement (e.g. INSERT, UPDATE or DELETE) is executed, it is first entered into the transaction log file (*.LDF). Each entry has a Log Sequence Number (LSN) from zero to infinity which is never re-used. The LSN is then updated in the data pages of the database file (*.MDF and *.NDF). When the tranaction is committed, the results are "hardened" to the data pages. If the database is set to simple recovery mode, the entry in the transaction log is removed, so that the space can be re-used. However, if the database recovery mode is set to "bulk" or "full", then the entry is not removed until the next full database backup has been completed. This retention of the transactions allows for point in time recovery.

Problems with the transaction log arise when they grow out of control. This is referred to as a "runaway transaction log". You know when you've got a runaway transaction log when the following error is raised:-

Error: 9002, Severity: 17, State: 2
The transaction log for database '%.*ls' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

You may also notice the following:-

• The database transaction log completely fills up the disk.
• The database may be marked as "suspect"
• A very large transaction log file.
• Transactions may fail and may start to roll back.
• Transactions may take a long time to complete.
• Performance issues may occur.
• Blocking may occur.

The main reason for a large transaction log are that the database has not been backed up regularly and has been on the receiving end of large data extracts whilst in full backup recovery mode.

If the transaction log has not consumed the entire drive, you may wish to allow the transaction log to increase in size, or move other files off that drive.

If not, youll have to deal with the wreckage. First things first - backup the database so that you've got something to restore if it all goes pear-shaped.

Assuming the transaction log is ludicrously large, issue the shrink file command:-

DBCC SHRINKFILE (file_name, target_size)

This should have relieved at least some disk space. Next take another backup, and repeat the DBCC Shinkfile command. If this second attempt does not release sufficient disk space, then you should try to troubleshoot why the log is so full. This could be due to incomplete transactions.

Check the value of log_reuse_wait_desc in the sys.databases view and run DBCC OPENTRAN.

The next stage though is to issue:-

BACKUP LOG databasename WITH TRUNCATEONLY

This is a last resort and will break the transaction log chain making it impossible to do a point in time restore. So the next thing should be to do another backup, so that you can do a restore to this point and to be the starting point of new backup sets.

For production databases, you should still use the FULL backup recovery model. However, if restoring to a developement or support environment where large data imports may be tested, then consider the simple or bulk modes. But the best solution to keeping the transaction log file to a reasonable size is to take regular backups. A periodic shrinking of the file is inadvisable as it will reduce the efficiency of the log.