4

Due to an unforeseen hardware error, I had to take our secondary replicas down for maintenance. This was really due to a storage issue and, it was decided at a higher level, to purchase a new storage system for the secondary instance. This is several weeks out from delivery and installation at this point.

This was on a system that also uses FCI with 2 active instances.

I was able to successfully remove the secondary replicas from both Availability Groups and I thought I had done this correctly (they no longer show up in the UI or when I look at the AG Dashboard. However, one one of the AG's, all of my log files are growing. When I query the sys.databases and look at the log_reuse_wait_desc for that instance it says 'AVAILABILITY_REPLICA'

I'd rather not remove the AG as the listener is actively used and would require reconfiguration of numerous web.configs and other configuration entries. But, I also am slightly worried about the log file growth.

Why, on one AG does it not appear to be growing or have the log_reuse_wait_desc of AVAILABILITY_REPLICA but on the other it does?

András Váczi
  • 31,278
  • 13
  • 101
  • 147
Brent Bain
  • 41
  • 1
  • 2
  • the log_reuse_wait_desc of AVAILIBILITY_REPLICA means that SQL Server is holding on to log data until the replica comes backup. This happens when you do not remove the replica properly. Have a look at sys.dm_hadr_database_replica_states to see whats going on. You can remove the secondary database from Availablity group using ALTER DATABASE MyDb2 SET HADR OFF – Kin Shah Nov 25 '13 at 22:42
  • Thanks -- so am I correct in thinking I can run the Availability Group with only the one active server until I get the other storage back online and then go through the synchronization steps again?

    I also looked in sys.dm_hadr_database_replicat_states as well as sys.dm_hadr_availability_group_states and everything looks like it's only singular.

    Last question -- does setting HADR off remove the db from the AG?

     – Brent Bain Nov 26 '13 at 16:33
  • does setting HADR off remove the db from the AG? Yes it removes the db from Availablity group. Also, you might be interested to look at my answer on SQL Server 2012 AlwaysOn: need to automatically add databases by script- T-sql or PowerShell – Kin Shah Nov 26 '13 at 16:37
  • So is there a way to keep a single (primary) replica server active in the AG for the databases without removing them but keeping the log file from growing or being stuck in 'AVAILABILITY_REPLICA'? (Quite the impressive script but I'm not quite sure how it helps me in my situation). – Brent Bain Nov 26 '13 at 17:43
  • So is there a way to keep a single (primary) replica server active in the AG for the databases without removing them but keeping the log file from growing or being stuck in 'AVAILABILITY_REPLICA'? Nope. Either you pause (the log file keeps growing) or you remove it from AG. Regarding the script, it will help you when you want to add a new database - was just an FYI for future use. – Kin Shah Nov 26 '13 at 17:45

2 Answers2

1

I think the question has a wrong assumption.

So is there a way to keep a single (primary) replica server active in the AG for the databases without removing them but keeping the log file from growing or being stuck in 'AVAILABILITY_REPLICA'?

...

I'd rather not remove the AG as the listener is actively used and would require reconfiguration of numerous web.configs and other configuration entries. But, I also am slightly worried about the log file growth.

If you have an AG running on a single node, you can remove the database from the AG completely AND still connect to the database via the listener (as long as the clustering part is online either forced or using Windows 2012+ with dynamic quorum).

For a normal connection without any application intent the listener just functions as a dumb alias for the server. It doesn't check something like whether the database you're connecting to is part of its AG or not.

Cody Konior
  • 3,508
  • 12
  • 22
0

Work around would be to remove the DB from AG and then clear the log and add it back to AG

Hareesh Gottipati
  • 1