Transaction log keeps growing and cannot be shrunk after taking the database out of availability group. when I check the log_reuse_wait_desc, it says “availability replica”. but the Database has been removed from AG. I am confused and not sure what to do at this point, I am on SQL 2019 CU4 Tried the usual – log backup –> shrink changed to simple and back does not work.
2
There are 2 answers
0
On
I removed database from AG and set it to Simple recovery. After this operation we faced issues with logs having the same symtomps as Bala was having (there were 2 logs for this database, please, do not comment this configuration :] ). After many tries (shrink - not functional due to availability_replica, backup not working, full recovery and then backup/shrink not working...), only restart of the SQL instance helped us. After start up logs were shrunk. If there is any solution without restating, please, share it with me. Thx.
I do not say this is the correct solution but I just describe case which really happened to me personaly. ;) Have nice day and less issues with your SQLs.
I suffered from the same problem recently when I noticed that some AG databases had AVAILABILITY_REPLICA in the log_reuse_wait_desc column but others didn't after an outage. I discovered that it was actually that data movement had been suspended for those databases. It had been suspended for too long to re-sync so I had to remove them from the AG, restore backups to the secondaries and add them back in to the AG. So the end result is the same as described above. In short, check for data suspension and if the suspension has been a short one then you may be able to resume data movement otherwise you are probably looking at the more time-consuming solution.