Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 'AVAILBILITY_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.

Can anyone offer any suggestions OR tell me why, on one AG it does not appear to be growing or have the log_reuse_wait_desc of AVAILIBILITY_REPLICA but on the other it does?

Any tips out there? Thanks! Brent

share|improve this question
    
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 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 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 Nov 26 '13 at 17:45
add comment

migrated from stackoverflow.com Nov 25 '13 at 21:20

This question came from our site for professional and enthusiast programmers.

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.