How to report a runaway

how to report a runaway

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

Source: www.sqlservercentral.com

Category: Bank

Similar articles: