Shrink Log file in Always ON DB

Question

We have Sql Server 2016 and set up Always ON with 2nd replica (Primary and Secondary) Currently looks like Log file filling up, getting disk pace full. I tried to resume the secondary database which was in Not Synchronizing/Suspect status after adding disk space and worked fine for 1 day and again it's starting to fill up the space and we are out of disk space. We have Primary is still Synchronized. We are running log backup every 15 minutes on Primary. I tried to Shrink the Log file but getting following error: Cannot shrink log file 2 (SQL1_log) because the logical log file located at the end of the file is in use.

1. When I ran SELECT name, log_reuse_wait_desc FROM sys.databases

Other Databases showing log_reuse_wait_desc = "Nothing" except my Primary DB showing " AVAILABILITY_REPLICA"

2. DBCC LOGINFO Shows lot of almost (1000+) VLF files with Status = 2

3. DBCC SQLPERF(LOGSPACE)

4. When I am running OPEN TRAN ==> Nothing getting any Blocking/Open Session ID so I can't Kill anything

Thank you in Advance for your help!

Answer 1

At first make sure that you have enough space for log on secondary (should be the same like on primary)

After that you can run below query and take a look on last column 'replication_behind_minuts' it should be near 0 and also check redo_queue_size this don't need to be 0 but should be low number.

select 
   rcs.replica_server_name as [Replica], 
   ars.role_desc as [Role],
   db_name(rc.database_id) as Dbname,
   rc.synchronization_state_desc as [Sync_state],
   rc.synchronization_health_desc as [Health_State],
   rc.last_sent_time,
   rc.last_received_time,
   rc.last_hardened_time,
   rc.last_redone_time,
   rc.last_commit_time,
   rc.redo_queue_size,
   DATEDIFF(minute,rc.last_commit_time,rc.last_hardened_time) as Replication_behind_minuts
from sys.dm_hadr_database_replica_states as rc
join sys.dm_hadr_availability_replica_cluster_states as rcs on rcs.replica_id = rc.replica_id
join sys.dm_hadr_availability_replica_states as ars on ars.replica_id = rc.replica_id
order by rc.redo_queue_size desc 

After the numbers are ok check the

SELECT name, log_reuse_wait_desc FROM sys.databases

It should show 'nothing' or 'log_backup' and all should be good. If you still see 'AVAILABILITY_REPLICA' it means that SQL still applying logs to the log file (probable replica is still behind) You need to make sure that you have enough space to get all logs from primary.