SQL Server – Unable to Truncate Transaction Log, log_reuse_wait_desc – AVAILABILITY_REPLICA

availability-groupssql serversql server 2014transaction-logtransactional-replication

This morning i was woken up by a transaction log full alert on one of our database. This server is an alwayson cluster and also a transactional replication subscriber.
I checked log_reuse_wait_desc and it showed logbackup. Someone had accidentally disabled the logbackup jobs 4 days earlier, I re-enabled the log backup job and the log got cleared. Since it was 4am I thought I will go to office later that morning and shirnk the log as it has grown to 400GB.

10AM- Im in office and I check the log usage before shrinking and it was around 16%. I was surprised and check the log_reuse_wait_desc, which showed replication. I was confused cause this was a replication subscriber. We then saw that the db was enabled for CDC and thought that might be the cause, so disabled CDC and now the log_reuse_wait_desc shows AVAILABILITY_REPLICA.

The log usage meanwhile still steadily growing and its at 17% now. I check the alwayson dashboard and check the sent and redo queue and both are virtually zero. I am not sure why the log reuse is showing as AVAILABILITY_REPLICA and unable to clear the log.

Any idea why this is happening?

Best Answer

If you do this:

SELECT * FROM sys.databases

And the log_reuse_wait_desc shows AVAILABILITY_REPLICA, that means SQL Server is waiting to send log data to one of your Always On Availability Group replicas. One of the replicas may be lagging behind due to a slow network, or it may be down altogether.

If you check the AG dashboard and it shows no queues, you may have been a victim of thread exhaustion. It's a known issue that the AG dashboard stops updating after worker thread exhaustion. You'll need to check the status on each replica directly rather than relying on the primary. Nick's note in that Connect item says that you can just alter a replica's properties to restart replication, but that doesn't always work (especially if you have hundreds of databases on a replica with a large amount of data that needs to be sent, and restarting replication can just cause the worker thread exhaustion again.)

If the last guy set up an AG replica and it's not supposed to exist anymore, then it's time to remove that AG and/or replica. Just be careful that apps aren't pointing to the listener name in order to connect to your SQL Server.

Related Solutions

Sql-server – get “The initial snapshot for publication is not yet available.”

With basic Transactional Replication there are a variety of agents.

Helpful MSDN article

There is the Snapshot Agent which is responsible for the initial snapshot of data and schema. This can be scheduled to run at regular intervals (I had mine scheduled for every 12 hours).

There is the Log Reader Agent, which reads the transaction logs and sends those which are marked for replication on to the distributor.

Finally, there is the Distribution Agent which is responsible for moving the snapshot(s) and the transactions from the distributor to the subscriber(s).

Your error is telling you that it cannot apply transactions that are marked for replication to the subscriber because the initial snapshot (and/or any of the subsequent snapshots) have not yet been applied. You need to troubleshoot why this snapshot is not being applied. There are many reasons why it could be happening.

Has the Distribution Agent lost permissions to the directory that contains the snapshot files?

Is there some blocking activity on the subscriber that is causing the snapshot to be applied slowly?

Have you overloaded the distributor and/or subscriber by pushing all articles from a large published database to all subscribers at the same time?

Sql-server – Unable to shrink log file due to transactional replication

In case the solution i gave in comment doesn't works try this :

As you said : Log_reuse_wait_desc on ServerB.DBB always shows 'REPLICATION'

This means Replication is causing the log not shrinking problem.

DBCC OPENTRAN gives you the traction that is blocking as output which in your case is following :

Replicated Transaction Information:
Oldest distributed LSN     : (0:0:0)
Oldest non-distributed LSN : (10417370:9406:1)

To see which database still needs to be replicated use :

DBCC loginfo

If you are sure everything is replicated to all subscribers run the following query on the each database where the replication is completed.I will repeat make sure everything is replicated before executing this query as it marks all replications as successful and your replication queue will be empty.

EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1

Back up the log file and then try shrinking it now it should free up the space.

Source: https://stackoverflow.com/questions/1238491/how-to-shrink-transaction-log-in-sql-server-database-in-replication

Best Answer

Related Solutions

Sql-server – get “The initial snapshot for publication is not yet available.”

Sql-server – Unable to shrink log file due to transactional replication

Related Question