SQL Server Database Transaction Log Full

Problem

Your SQL Server database returns an error that your transaction log is full when you try to execute some SQL statements. You check the hard drive free space, and it's full; or perhaps the transaction log has reached its maximum configured size. You check your database's recovery model, and it's set to "Full", even though restoring from a previous night's backup is acceptable recovery in the event of failure.

Solution

Unless you change the default log file settings or database recovery model, the transaction log is a ticking time bomb that grows until disk space is full, rendering your database unusable. This quick solution makes space immediately available, and the long-term solution automates the clean-up of the transaction log file.

The quick solution

The quick solution, after ensuring you have backed up your database as needed, is to execute the following statements in SQL Studio to (1) remove all inactive logs from your database's transaction log, then (2) shrink the log file down to a reasonable size.

1. Purge all inactive logs from a database:
   BACKUP LOG <YourDBName> WITH TRUNCATE_ONLY
   
   For example:
   
   BACKUP LOG XyzWebsite WITH TRUNCATE_ONLY


2. Shrink the database's log to a certain number of MB:
   DBCC SHRINKFILE(<YourDBLogFileName>, <NumMegabytes>)
   
   For example, shrink the log to 100 MB:
   
   DBCC SHRINKFILE(XyzWebsite_log, 100)

Do not shrink frequently, as physical fragmentation impacts database processing time; so after doing this, consider the long-term solution to prevent this from happening again.

The long-term solution

The long-term solution to automatically purge inactive logs is to change the database recovery mode from "Full" to "Simple". As long as restoring from a previous night's full database backup is acceptable recovery for you: in SQL Studio, right-click the database, select Properties ► Options, then change Recovery model to Simple.

For further reading, see the following MSDN references:

• Backup Under the Simple Recovery Model
  http://msdn.microsoft.com/en-us/library/ms191164.aspx
• BACKUP (Transact-SQL)
  http://msdn.microsoft.com/en-us/library/ms186865.aspx
• DBCC SHRINKFILE (Transact-SQL)
  http://msdn.microsoft.com/en-us/library/ms189493.aspx