Pages

May 23, 2016

The transaction log for database '' is full due to 'AVAILABILITY_REPLICA'. [SQLSTATE 42000] (Error 9002). The step failed. SQL 2014


I have noticed that our SQL jobs are failing due to this error.


Message
Executed as user: ''. The transaction log for database '' is full due to 'AVAILABILITY_REPLICA'. [SQLSTATE 42000] (Error 9002).  The step failed.


Below is the SQL configuration :

SQL version is 2014
One Primary and one secondary replicas
Recovery mode is full
Transaction Log file is set to auto grow with limited size.

I have verified the transaction log backups and those were running fine. Not sure why the transaction log was full and this issue I see first time in my environment.

Below queries help to see the Alwayson status, also dashboard gives the same details.

select a.*, b.name
from sys.dm_hadr_database_replica_states a

LEFT JOIN sys.databases b ON a.database_id = b.database_id

Fix: As the always on is not in sync I have taken the database out of always on shrink the database files and add it back to always on. Used below syntax to shrink the databases.

I have explained removing and adding back databases in to Alwayson in another post.

USE AdventureWorks2012;  
GO  
-- Truncate the log by changing the database recovery model to SIMPLE.  
ALTER DATABASE AdventureWorks2012  
SET RECOVERY SIMPLE;  
GO  
-- Shrink the truncated log file to 1 MB.  
DBCC SHRINKFILE (AdventureWorks2012_Log, 1);  
GO  
-- Reset the database recovery model.  
ALTER DATABASE AdventureWorks2012  
SET RECOVERY FULL;  
GO  

References:
https://support.microsoft.com/en-us/kb/2922898
https://msdn.microsoft.com/en-us/library/ms189493.aspx 

2 comments:

Priya Kannan said...

This blog is having the general information. Got a creative work and this is very different one.We have to develop our creativity mind.This blog helps for this. Thank you for this blog. This is very interesting and useful. SQL Server Training in Chennai

Anonymous said...

Thank you for useful article.