4

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

I get:

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

I get:

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?

  • 1
    Check sys.dm_hadr_database_replica_cluster_states, more information here – Shawn Melton Sep 14 '15 at 5:24
3

It looks like the CDC Capture job had stopped running. Restarting that (which took a while) then cleared out the log and allowed it to rewrite.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.