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
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:
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:
This will quickly “empty out” the log file, but it will still be the same size, externally.
Step 6, Setup a SQL Agent job that periodically (depending on your workload) backs up the Transaction Log