June 7, 2021 at 7:57 pm
Evening All,
I've removed some databases from Availability Groups as they're no longer required. However I cannot shrink the logs as the wait type is 'availability_replica' as below:
log_reuse_wait_desc
AVAILABILITY_REPLICA
I've set the DB into simple recovery and still no joy. I've also taken a full backup which has made no difference.
I am not sure what to do next - I don't want to reconfigure Always On as its a 5TB database.
SQL Server 2017 Enterprise
Edit: Setting the DB offline and online again cleared that wait type from the log_reuse_wait_desc though the physical file sitll wont shrink, in simple recovery with dbcc shrinkfile, nor in full recovery after a full backup and log backup.
Cheers All,
Alex
June 8, 2021 at 8:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
June 8, 2021 at 8:30 pm
So I've (sort of) solved this. I'll post my steps in the hopes it saves someone else some expletives.
DBCC Loginfo led me to the status of each VLF being stuck at 2, and its last waitype was that of 'Availability_Replica'
Switching the recovery model to simple (and back) had no effect on the status of the VLF's so last ditch was to set the DB offline and back online, which immediately cleared the status of those VLF's and the wait type mentioned above was now 'nothing'
Then the log shrunk instantly with dbcc shrinkfile(2, 10000) [eg]
Cheers All
June 16, 2021 at 9:21 am
remove replica also from AG then it will get shrink
June 16, 2021 at 10:58 am
What if you cannot remove the replica from the group?
If you have multiple databases in the group then removing the replica will affect them all, not just the database which has been removed
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply