This article guide, How to resolve,
Availability Group database stuck in Not Synchronizing or Database is not coming in restoring mode post removing it from AG group
Why this issue occurred?
Database size was 5+ TB of one of the database on SQL Server. Index jobs runs weekly.
Due to index maintenance job , log file of database increase
Log backup is scheduled every 15 min on blob storage which reduces log usage with every log backup.
But due to limitation of max size of blob storage(STORAGE DEVICE HAS REACHED ITS ALLOWED BLOCKS.) and increase in log file , log backup file size increase and stopped working. Log file started growing without log backup and got full and log backup was stopped due to sudden increase in size.
As log backup stopped , disk space also got full as log file size increase and due to that database in always on stopped synchronization.
Why Availability group database stopped synchronization?
On secondary node, mode was synchronous commit. Because of that data was not primary server databases also went in to inaccessible mode.
Reason was , in synchronous mode when data committed on secondary then only it get committed on primary database. There was no space which kept secondary node unsynchronized.
How to resolve Availability Group database stuck in Not Synchronizing?
As database synchronization stopped and due to synchronous commit mode primary database went in to in accessible mode. Application goes down.
Status show ,Log file full due to Availability Replica
select name ,log_reuse_wait_desc from sys.databasesTo resolve this,
Need to remove databases from AG and re add it. For that need to take log backup and then shrink database log file.
But when we remove databases from ag we were able to remove it successfully from node where AG mode is asynchronous mode and database state change to restoring but on synchronous commit node database state does not change and state remain non synchronizing.
To put database in restoring mode manually run below command on database.
USE testdb
go
SHUTDOWN WITH NOWAITPost execution of this command , database come back in restoring mode and able to shrink log file. Also able to add database again in to HA.