1

I have been stuck with this issue for two days now and am not hoping someone here might know the answer.

I have multiple databases in one availability group, all of the databases follow the same backup plan. (Full Recovery, Full Backups & hourly Log backups).

One database, and only one, is currently refusing to truncate (reuse) it's log file. It has now grown slowly to 33GBs where the actual database itself is less than 512MB (so tiny).

I checked if there are any running transactions (there aren't) if the log backups run (they do) and everytime I manually do a log backup to check why the truncate isn't happening I get; AVAILABILITY_REPLICA as the reason.

Point is that when looking at the availability dashboard everything is green, there are no Log Queues, no Redo Queues, everything looks as if it is good to go.

As these are hosted on a managed environment where I cannot add/remove databases from the AG myself I've created a ticket to ask for this particular database to be removed from it and then added again. However I'm not sure if this;

a) will fix the problem b) will not just be a temp fix (truncate once, then slowly start growing again)

Does anyone here have any suggestions as to what to look at?

Extra info; SQL Server 2016, Running on Windows Server

Improve this question
2
  • when you say multiple databases I'm presuming one primary and multiple secondary nodes, is it the primary or one of the secondary nodes that you're having issues with? and which node are you taking backups from
    – Ste Bov
    Apr 12, 2018 at 7:32
  • Hello Ste, what I mean is this AG has 25 databases in it, and only this actual database has this problem. The cluster itself has three nodes. One primairy, a secondairy which is sync and a third secondary which is async. All three nodes naturally have the same problem as the log file is equally big on all three but it's the primairy I'd like to have truncate since that's the only node that you can write on.
    – F.B. ten Kate
    Apr 12, 2018 at 9:16

2 Answers 2

Reset to default
4

I would like to comment but couldn't due to the lack of reputation.

Is your secondary node being enabled read-only? And if so, did you check if there's any blockings on the secondary node?

Another thing that you could check is if there's any maintenance jobs running.

And... could you try to make everything asynchronous mode to see if it helps? Once log reduced, you may set it back to synchronous mode.

... I've created a ticket to ask for this particular database to be removed from it and then added again. However I'm not sure if this; a) will fix the problem

Last but not least if everything doesn't work, yes, you can remove that particular DB from the AG group. It should fix the issue. Until the log has reduced, you may add it back to the AG group. Removing DB from the AG group shouldn't cause to any impact as applications should be connecting using the Listener.

b) a temp fix (truncate once, then slowly start growing again)

Yes, it might occur again if we don't figure out the root cause to it. And you probably would have to repeat the whole procedure in removing/adding back to AG group, though it shouldn't cause to any production impact.

Improve this answer
6
  • Thanks for answering Yoshiaki. The comment restriction is there for a reason, but you've offered a lot of information here so I think this is actually OK as an answer. Feel free to edit if more info comes to light to make it a better answer!
    – LowlyDBA - John M
    Apr 13, 2018 at 2:51
  • "...applications should be connecting using the Listener." --verify this first :)
    – Kevin3NF
    Apr 13, 2018 at 3:42
  • Yes, obivously applications use the listener, otherwise failing over would suck ass. Yesterday we failed over to the other SYNC node to see if that helped, it didn't straight away but I'm going to look into it more detail this morning. As for the other nodes, there is only one node which can be read out of the three and has connections and this is the primairy.
    – F.B. ten Kate
    Apr 13, 2018 at 6:20
  • Just tried setting the second node to Async, did a log backup and no change sadly :-( Removing & adding the database back to the AG seems like the only thing left to do.
    – F.B. ten Kate
    Apr 13, 2018 at 6:29
  • I just remembered two more areas which you can check. Did you enable CDC or configure replication?
    – Yoshiaki
    Apr 13, 2018 at 8:06
1

I have the same problem on a few databases in an Availability Group (AG) in SQL Server 2016. I tried the following steps and it seems to truncate the Transaction Log file.

  1. Take a Transaction Log backup of the DB in Secondary Node of AG (I have set the Secondary Node as my primary backup Node)

  2. Execute Checkpoint on the DB in PrimaryNode of AG:

      USE <DB>
  GO
  CHECKPOINT;

  3. Take Transaction Log backup of the affected DB in Secondary Node of AG.

I am not sure why I had to do all these steps to truncate the Transaction Log file. I should have been able to just take TLog backup and that should have truncated the Transaction Log file.

Maybe there is a bug in SQL Server 2016. Please let me know if you find the real reason behind this issue.

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.