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] 
GO 
ALTER DATABASE [ngservices] SET RECOVERY SIMPLE WITH NO_WAIT;
GO

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]
GO
DBCC SHRINKFILE (N'ngservices_log' , 0, TRUNCATEONLY)
GO

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

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

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

USE [master]
GO
ALTER DATABASE [ngservices] SET RECOVERY FULL WITH NO_WAIT;
GO

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: Runaway Transaction Log