Share via

SQL Server: Transaction Log is Full

  • Article

 

Overview

If the transaction log is full, users cannot update the databases and you cannot fully restore the database in the event of a system failure.  You must clear the transaction log either by performing a full database backup and saving the data, or by truncating the transaction log.

Recovery Model

The error says that there is no space available in the drive where Transaction Log file is kept. This is very common issue in SQL server if you do not have property backup policy or recovery model set to the database. If you are not taking TL backup regularly, you must change the recovery model to simple so that the TL size will not increase drastically. You may check this link.

For more information: http://www.karaszi.com/SQLServer/info_dont_shrink.asp

Additional Resources

Here are some more articles that will help you:

  1. Truncating the Transaction Log: http://msdn.microsoft.com/en-us/library/aa174538(SQL.80).aspx
  2. How to truncate transaction log file via sql statement: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3591435&SiteID=1
  3. How to backup and truncate all log files in a database: http://blogs.msdn.com/mab/archive/2008/01/26/how-to-backup-and-truncate-all-log-files-in-a-database.aspx
  4. Truncate and shrink the transaction log: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=66239

Reference

This content originated from Madhu and Dannal in this Forum thread.
  

See Also