ProblemYour 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.
SolutionUnless 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 solutionThe 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.
- Purge all inactive logs from a database:BACKUP LOG <YourDBName> WITH TRUNCATE_ONLY
For example:BACKUP LOG XyzWebsite WITH TRUNCATE_ONLY
- 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)
The long-term solutionThe 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
- BACKUP (Transact-SQL)
- DBCC SHRINKFILE (Transact-SQL)