Tag: Error 9002

No comments

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

  By ,

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...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter
3 Comments

SQL Server – The Transaction Log for Database is Full Due to Replication (Error 9002 Severity 17 State 6)

  By ,

We have a 3 node AAG (below is our setup). Our transaction log file has a growth limit of​​ 60GB. I found out that this error occurred because the CDC failed. I could not start CDC and the transaction log is not truncating.

 

 

I checked the log_reuse_wait_desc and it indicated REPLICATION which means that there are committed transactions that haven’t yet been scanned by the transaction replication Log Reader Agent job for the purpose of sending them to the replication distributor or harvesting them for​​ Change Data Capture​​ (which uses the same Agent job as transaction replication). The job could have been disabled, could be broken, or could have had its SQL Agent schedule changed.

 

SELECT​​ name,​​ recovery_model_desc,​​ log_reuse_wait_desc

FROM​​ sys.databases

WHERE​​ name​​ =​​ 'DB_Name'

 

 

 

I also ran the command DBCC OPENTRAN and saw Replicated Transaction​​ Information.

 

 

 

To resolve the issue, I did the following steps.

  • Create a new transaction log file (it is a good thing that our currently t-log file has a growth limit else it could have occupied all the disk space.)

  • I started CDC and it ran​​ successfully.

 

But the t-log was still not truncating. I checked the log_reuse_wait_desc again and it indicated “AVAILABILITY REPLICA” which means that the database mirroring partnership has some latency in it and there are log records on the mirroring principal that haven’t yet been sent to the mirroring mirror (called the send queue). This can happen if​​ the mirror is configured for asynchronous operation, where transactions can commit on the principal before their log records have been sent to the mirror.​​ It can also happen in synchronous mode, if the mirror becomes disconnected or the mirroring session is suspended. The amount of log in the send queue can be equated to the expected amount of data (or work) loss in the event of a crash of the principal.

 

I​​ waited for few minutes and I checked the log_reuse_wait_desc and now I see “NOTHING” which means that SQL Server thinks there is no problem with log truncation.

 

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter