SQL Server – Availability Group log_reuse_wait = Replication

availability-groupssql serversql server 2014transaction-log

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?

Best Answer

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.

Related Solutions

Sql-server – Cross Database transactions with Availability Groups

The article isn't well worded but here's what I think it's saying

Beginning scenario - DatabaseA is in an AG and is currently primary on Server1 and can run on Server2. DatabaseB can only run on Server1

T1 - Transaction begins T2 - Row inserted into DatabaseA (log not yet sent to Server2 though) T3 - Row inserted into DatabaseB T4 - Transaction commit called T5 - DatabaseB commits transaction (since the two DBs commit individually) T6 - DatabaseA fails over before log is sent

Since the log was never sent DatabaseA wouldn't have the row but DatabaseB would. If synchronous mode is being used that can't happen because the commit can't be called until the log from the row is sent. However, it may be possible for the commit on DatabaseB to happen and DatabaseA to fail over before the commit happens there. This may lead to a rollback in DatabaseA instead of a commit. I can't say with certainly that you can get into the same situation with synchronous mode but if you can that's how it would likely happen.

SQL Server – How to Reset [log_reuse_wait] After Log Backup

You have a couple actual questions here, so let's address each one individually.

To answer the question of why the log_reuse_wait won't update, this field (along with other metadata within SQL Server) will only update after certain events occur. With this field, it will update after a database checkpoint occurs. To force this after taking a log backup, do the following:

BACKUP LOG [foo] TO DISK=N'X:\Backups\foo_20141008120000.trn';
CHECKPOINT;

The checkpoint will occur and the field in sys.databases will update.

For why your log file won't shrink, this is a MUCH larger question. You should read this question/answer by Mike Walsh on why your log file is growing and how best to manage it. I also provide some more detail in this answer.

Regarding VLFs, I would strongly recommend you not worry about that right now. Yes, you can see a performance impact over having to many VLFs (and understanding what a VLF is and how it works is fundamental for understanding the log file), but managing that should only be addressed when you're regularly managing your log with backups and it's not growing out of control.

Finally, as for the size of your log, this is a very open ended question. It very much depends on how much activity your database sees along with the scope of that activity. Chances are that you will shrink your log file, only to see it grow again to accommodate your regular database activity. Before considering shrinking your log file, you need to understand what's making it grow and whether or not you're running regular scheduled log backups against it.

Related Question