Post reply

Always-On AG Cluster: Suspect database on secondary node, backup failing

  • October 16, 2019 at 12:39 pm

    #3689234

    Setup:

    Three node AO AG cluster, Node1 and Node2 synchrounous in DC1, and Node3 ansynchronous in DC2.

    Hi guys, we had an incident some time ago, the root cause seems to be that because of some disk issues, one db went offline/suspect on the Async node in DC2, this caused a t-log backup to fail, and the disk went full after a while.

    My question is; should the fact that an async db in a DR DC really cause the backup to fail?

    Shouldnt SQL server just skip syncing to this db as long as it is suspect?

    Thanks.

  • October 16, 2019 at 12:52 pm

    #3689239

    Were your t-log backups on the primary or the secondary? I'm guessing secondary?

  • October 16, 2019 at 1:02 pm

    #3689251

    Hi.

    They were running on the primary.

  • October 16, 2019 at 1:38 pm

    #3689274

    I think it really doesn't matter where you are running your tlog backups (either Primary or Secondary).

    Have you checked the error log? Did you try to run the log backup manually? Did you run into any issues if/when you did run the log backup manually? What's the status of the DB in DC2?

    "He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]

  • October 16, 2019 at 6:37 pm

    #3689391

    Even with a secondary setup in asynchronous mode - transactions need to be hardened on that secondary before they can be marked for reuse on the primary.  They can (and will) be backed up during the transaction log backups but they will not be marked for reuse until they have been hardened/committed on the secondary.

    Because of this - with that database on the secondary offline - SQL Server was not able to reuse the space in the transaction log and started growing the transaction log on the primary (regardless of log backups).  Of course - once the transaction log on the primary filled the drive you ran into issues on the primary database.

     

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply