SQL Server – Troubleshoot a Transaction Log Full Transaction Error (Msg 9002)

I encountered the error below.

Msg 9002, Level 17, State 2, Line 1
The transaction log for database ‘DBName’ is full due to ‘LOG_BACKUP’.

The following are the corrective actions that can be done.

• Take a log backup (The #1 cause of full transaction log is the lack of backups)
• Kill a long-running transaction
• Alter database mirroring, availability groups, or replication to allow the transaction log to clear
• Either manually grow the existing log file(s) or add another log file (temporarily)
• As a last resort, switch to SIMPLE recovery model

Execute the script below to know what is the reason why the log cannot be cleared.

SELECT [log_reuse_wait_desc]
	FROM [master].[sys].[databases]
	WHERE [name] = N'DB_Name';
GO

Some examples of why the log is not clearing are LOG_BACKUP, DATABASE_MIRRORING, NOTHING.

You may check the full list in Books Online at http://bit.ly/PoX2xe

Knowledge worth sharing...

Linkedin

Facebook

Google

Twitter