We have a SQL 2014 Availability Group with 3 replicas of our primary database - 2 in our main data center and 1 in our DR center.
We did a large index rebuild on the primary which obviously needs to replicate to the secondaries. I believe this replication has now finished. When I run:
select redo_queue_size, log_send_queue_size from sys.dm_hadr_database_replica_states where database_id = 28
redo_queue_size |log_send_queue_size 0 | 0 0 | 0 NULL | NULL
Which suggests to me that the replication has finished. However, when I run
select log_reuse_wait, log_reuse_wait_desc from sys.databases where database_id = 28
log_reuse_wait | log_reuse_wait_desc 6 | REPLICATION
I found this query:
select * from sys.dm_hadr_database_replica_cluster_states where database_name = DB_NAME()
which shows the state of the replication which again suggests that everything is replicated, but it can't be truncated for some reason:
is_failover_ready | is_pending_secondary_suspend | recovery_lsn | truncation_lsn 1 | 0 | 4294967295429496729500001 | 793156000000001600001 1 | 0 | 4294967295429496729500001 | 793156000000001600001 1 | 0 | 4294967295429496729500001 | 793156000000001600001
The Transaction Log backup jobs are running and backing up as expected, but the % of the log file used continues to grow due to normal traffic. How do I get SQL to reuse the transaction log file?