Issue
One of our customers has an availability group server with two nodes and four databases. He received a warning stating that the log file for one of the larger databases was full.
Customer failed to check AG sync status and removed the database from AG, assuming that this would fix the problem and allow the log file to be truncated. The log file was not truncating even after the database was removed from AG. He contacted our DBA team, requesting assistance because the system was in production and the log file drive was rapidly filling up.
Observations:
We reviewed the configuration and discovered that the LOG REUSE WAIT DESC’s status is ‘AVAILABILITY REPLICA’.
To help truncate the log file, the following measures were taken to try to modify the database status.
- 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
- To write dirty pages to storage and truncate the log file, run checkpoint. It didn’t work.
- We considered upgrading the database recovery model to Simple. However we couldn’t risk losing data because it was production.
Resolution
To correct the database status, we took the measures listed below.
- On the database, I checked for active connections.
- I requested authorization from the customer to take the database offline and then back online.
- To ensure that data is not compromised, a database log backup was taken.
- We restarted the database by pulling it offline and bringing it back online after receiving customer approval.
- We verified the LOG REUSE WAIT DESC when the database was restarted, and it had been modified to ‘NOTHING’.
Conclusion
The DR server in AG had a smaller log drive size than that of the prod server, according to our analysis. The AG was disconnected as a result, and the database was unable to transmit transactions to the secondary. As a result, the log file grew in size.
We suspect database metadata was not changed in system tables because the customer did not check AG status before removing the database from AG.
The problem was resolved after the database was restarted.
Hope this article helps you in troubleshooting similar issues!
Saurabh Srivastava's SQL Server Blog 