Issue: One of our customers have a AG server with 2 nodes and 4 databases added to availability group. He got an alert saying that log file is full for one of the bigger database.
Customer failed to check AG sync status and removed the database from AG, thinking it would resolve the error and allow truncation of log file. Even after database removal from AG log file was not truncating. He reached out to our DBA team asking for resolution as it was a production server and log file disk was getting full rapidly.
Observations: We checked the configuration and found that status for LOG_REUSE_WAIT_DESC’ is ‘AVAILABILITY_REPLICA’ .
Below steps were done to try and change the database status to help truncate the log file
- Checked database that are part of AG by running below query. This database did not show up.
select db_name(database_id) as [Database],is_primary_replica,
synchronization_state_desc,database_state_desc,is_suspended,suspend_reason_desc,
recovery_lsn,truncation_lsn,last_sent_lsn,last_sent_time,last_received_lsn,last_received_time,last_hardened_lsn,log_send_queue_size,log_send_rate,redo_queue_size,
redo_rate,end_of_log_lsn,last_commit_lsn,last_commit_time,secondary_lag_seconds from sys.dm_hadr_database_replica_states- Run checkpoint to write dirty pages to disk and truncate the log file . It didn’t work.
- We thought of updating the recovery model for database. Since it was production we could not risk losing data.
Resolution
We performed below steps to fix the database status
- Verified active connections on database
- Asked permission from customer to take database offline\online.
- Took database log backup to ensure data is not lost.
- Upon approval from customer we restarted the database by taking it offline and brining it back online.
- Upon Database restart we checked the LOG_REUSE_WAIT_DESC and it was changed to ‘NOTHING’
Conclusion
Our investigation revealed that DR server in AG had smaller log disk size than prod. This caused AG disconnection and database was unable to send transaction to secondary. This resulted in log file growth.
Since customer did not check AG status before removing the database from AG we believe that database metadata was not updated in system tables.
once database was restarted it fixed the parameters.
Hope this article helps you in troubleshooting similar issues!
Happy learning!!