Error: Transaction Log File is full at the primary Replica of the Always on Availability Group.

SQL server issue and solution 

 Error Summary:  Assume we receive 9002 error message when trying to execute a new query on one of the Always on Availability Group Databases.

 Reason for the error: 

1. Transaction Log file is pending with Log backup operation. 
2. The Secondary Replica of Availability group is not synchronized with Primary Replica due to the connectivity issue.  
3. The oldest page of the database is older than the checkpoint LSN. 

Resolution: The following are the steps to resolve these errors:

1.First, we check what is blocking the transaction Log File and prevents it from being truncated: 

Select name, log_reuse_wait_desc   from sys.databases 

   We see that the Transaction Log file is pending with Log backup operation which is to be truncated. 

Take the Transaction Log backup of that database.

 

2.Check again what is blocking the Transaction Log of that database: 

Select name, log_reuse_wait_desc   from sys.databases 

If it is waiting for Availability_Replica which means the Transaction Logs are waiting to be written to the Secondary Replica but not able to send these Transaction Logs to the Secondary Replica due to an issue in always on Availability Group site. 

  We check in Always on Availability Group Dashboard by right clicking on the availability group name and select Show Dashboard option From the Dashboard, we see that the secondary replica is not synchronized with the primary replica due to connectivity issue  

Check the secondary Replica and make sure the SQL Server Service is up and running at the Secondary Replica side. 

Refresh the Always On Availability Group Dashboard again and see that the Always on Availability group site is healthy again. 

3. Check if the Transaction Log File is still blocked by any operation:

Select name, log_reuse_wait_desc   from sys.databases 

If it shows pending OLDEST_PAGE, indicating that the oldest page of the database is older than the checkpoint LSN  

This issue can be fixed easily by taking another Transaction Log Backup and hence we can see that the transactional log file is now blocked by nothing.