SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

How to Diagnose and Correct a “Runaway” Transaction Log

This shows you how to diagnose and correct a "runaway" transaction log.  This is a very common issue that I often see with customers and on the MSDN forums. You will see the error message below when this happens:

"The transaction log for database 'ngservices' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases"

This means that the transaction log is full and you are completely out of disk space on the drive where the log file lives (or you have autogrow turned off for the log file). This likely happened because your database is in Full recovery model, and the transaction log has not been backed up.  The database is read-only until this is fixed. 

You can query sys.databases to find the recovery model and log reuse description for each database on a SQL Server instance

-- Get recovery model and log reuse wait description for each database on the SQL instance 
 SELECT [name], recovery_model_desc, log_reuse_wait_desc  
 FROM sys.databases;

You will see results like this with the query above:

Database         Recovery     Log Reuse Wait Desc
master             SIMPLE          NOTHING
tempdb           SIMPLE          NOTHING
model             FULL              NOTHING
msdb              SIMPLE          NOTHING
ngservices       FULL              LOG_BACKUP


This shows how to determine how large and how full your various data and log files are:

-- Individual File Size query
SELECT name AS [File Name] , file_id, physical_name AS [Physical Name], 
size/128 AS [Total Size in MB],size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 
AS [Available Space In MB]
FROM sys.database_files;

Once you have confirmed that your log is full and the Log Reuse wait description is LOG_BACKUP, here is the emergency fix:

Step 1, change the recovery model to Simple, like this:

USE [master] 

This will quickly “empty out” the log file, but it will still be the same size, externally.

Step 2, Shrink the log file to free up some disk space, like this:

USE [ngservices]

Step 3, Grow the log file back to a reasonable size (which depends on your workload)

USE [master]
ALTER DATABASE [ngservices] MODIFY FILE ( NAME = N'ngservices_log', SIZE = 204800KB , 
FILEGROWTH = 1048576KB )

Step 4, Change the database back to Full recovery model, like this:

USE [master]

Step 5, Take a Full database backup.

Step 6, Setup a SQL Agent job that periodically (depending on your workload) backs up the Transaction Log

Technorati Tags:


Posted by tkman26 on 27 April 2011

Excellent overview of this.

I encountered the Log file full error and spent 2 hours searching for a good description of how to resolve.  This was the best.

During this investigation I can see around 15 other log files that are also full.  

I will need to investigate what are good solutions for these and other errors.

Thanks for that write up.

Posted by Glenn Berry on 28 April 2011

Glad to help out a little.

Posted by Sergey Ermolenko on 22 June 2011

Excellent explanation!

One small note:

TRUNCATEONLY is applicable only to data files.


<a href="technet.microsoft.com/.../ms189493.aspx">Microsoft TechNet


Posted by Repriser-991084 on 28 October 2013

If you can find space to add a log file would be a better solution. You don't lose any Transaction logs.

Posted by Repriser-991084 on 28 October 2013
Posted by bwinchester on 6 April 2016

I realized this is an old article but I did pretty much exactly what you described above and it seemed to solve my problem for a day.  Even after shrinking my log file and setting autogrowth to 10%, my log file was 67GB the next day.  What would cause this?

Leave a Comment

Please register or log in to leave a comment.