10

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?

Improve this question

4 Answers 4

Reset to default
1

Better late than never: We have similar case, same symptoms.

Check:

SELECT log_reuse_wait_desc  FROM sys.databases  where name = '..'

on all your AlwaysOn -secondary instances.

if log_reuse_wait_desc is REPLICATION on one of them, switch primary to instance, and disable replication on it. If there is no replication already, use sp_removedbreplication.

Improve this answer
8

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.

Improve this answer
2
  • Would it matter if the secondary is set to Async mode? If the secondary is OFF( waiting for be fixed), will the primary log keep growing? Thanks!
    – Michael
    Oct 22, 2018 at 16:04
  • 1
    As long as the secondary is still in the AG (whether it's turned on or off, sync or async) then the log data will continue to pile up. After all, when you power the secondary back on, it has to get its data from somewhere, right? That's why if it's broken for a period of time, you're usually better off removing it from the AG, and reinitializing it from backup.
    – Brent Ozar
    Oct 23, 2018 at 11:21
0

In our case, it looks like some kind of bug when log fills up on systems with cdc/replication and AlwaysON. This issue happened again and the solution was to enable and disable CDC again. The events were as below

  1. Transaction log of the published db full because of distributor db running out of space and log reader failing. log_reuse_wait_desc shows replication, which is accurate.
  2. Disabled Replication completely, log_reuse_wait_desc still replication as the db has CDC.
  3. Disabled CDC, log_reuse_wait_desc changed to AVAILABILITY_REPLICA, even thought AG was working and sending data across and no log send queue.
  4. Enabled and disabled CDC again on the db, log_reuse_wait_desc went back to nothing.
Improve this answer
0

I had the same issue
The Database was marked as synchronized on Primary, but NOT synchronized on Secondary
I right clicked on the secondary database and selected Resume Data Movement
I refreshed, and saw it was now marked as synchronized on Primary, synchronizing on Secondary
I was starting to worry as it hadn’t recovered after 20 minutes - And then it recovered!

I attribute this to the volume of data that had to be synchronized

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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