In AG, the secondary database went into suspect state

Chaitanya Kiran 801 Reputation points
2021-10-06T14:31:28.607+00:00

Good Morning,

In AG, the secondary database went into suspect state. I know that I need to set HADR OFF on secondary replica and remove it from AG. What should I do after this? Please help me with the fix.

SQL Server | Other
{count} votes

2 answers

Sort by: Most helpful
  1. Ronen Ariely 15,206 Reputation points
    2021-10-06T21:54:32.81+00:00

    Please check if the following tutorial solve your needs:

    https://learn.microsoft.com/en-us/troubleshoot/sql/availability-groups/alwayson-availability-databases-recovery-pending-suspect#resolution-when-the-database-is-in-the-secondary-role

    Update! Actually, BEFORE you follow the instruction and remove the replica from the availability group, first try to find out the root cause so that you can take steps to mitigate it from happening again! This is important.

    Note: for the second step in the tutorial after you remove it from the AG and need to fix it, check this post:

    https://www.sqlskills.com/blogs/paul/creating-detaching-re-attaching-and-fixing-a-suspect-database/

    0 comments No comments

  2. AmeliaGu-MSFT 14,006 Reputation points Microsoft External Staff
    2021-10-07T02:47:59.72+00:00

    Hi ChaitanyaKiran-2787,

    I know that I need to set HADR OFF on secondary replica and remove it from AG. What should I do after this?

    You can restore the database from a good backup with NORECOVERY, and then add the replica back into the availability group. Please check Add a secondary replica to an Always On Availability Group.
    Best Regards,
    Amelia


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.