Log_reuse_wait_desc = AVAILABILITY_REPLICA but there is no secondary replica

Chuck Hamilton 1 Reputation point
2023-10-11T20:27:31.6233333+00:00

SQL version = 2012 (yeah it's older than the hills but I still have to support it)

I have an AG (availability group) with only one replica - the primary (obviously). There is no secondary replica to be sending log data to, however 4 out of 19 database replicas are showing AVAILABILITY_REPLICA in the log_reuse_wait_desc column of sys.databases. How is that possible?

Why is there only one replica in the AG you ask? The secondary was a DR server in the DR data center which is in the process of being physically relocated to another region. Prior to the DR server being shut down, I removed the replica from the AG to prevent log files from growing out of control on the primary replica. The secondary will be down for over a week and there's not enough space on the primary to retain logs for that long. Apart from removing the AG entirely it was the only option.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,895 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Erland Sommarskog 102.3K Reputation points
    2023-10-11T21:51:18.11+00:00

    And apparently it was not a good option. I think you need to drop the AG entirely. This may a situation that AGs in SQL 2012 was not designed for. (Recall that SQL 2012 was the first version to sport AGs at all.)

    1 person found this answer helpful.