March 27, 2018 at 11:38 am
I looked around couldn't find a good way to optimize/troubleshoot. I have a database in AG, i was rebuilding index on primary and it failed saying ran out of space . I see log_reuse_wait_desc is AVAILABILITY_REPLICA . Looks like it is waiting on other side to commit transactions. How do i troubleshoot or make it better? I did everything i shouldn't be doing ( cleared cache, restarted sql, shrunk file with different options ) still wasn't able to reduce used space.
March 27, 2018 at 1:08 pm
Does the secondary have enough space available for the transaction log to grow? I have run into issue where the secondary had 400GB drive - primary also had a 400GB drive but the secondary also had other databases that were using up space.
When the transaction log on the primary grew - the secondary could not grow and the system stopped processing any data.
The other thing to look at is tempdb - is there enough available space in tempdb on the secondary? Is someone running a transaction that has filled tempdb - which will prevent the redo queue from processing on the secondary as it needs space available in tempdb for the version store.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 27, 2018 at 2:08 pm
curious_sqldba - Tuesday, March 27, 2018 11:38 AMI looked around couldn't find a good way to optimize/troubleshoot. I have a database in AG, i was rebuilding index on primary and it failed saying ran out of space . I see log_reuse_wait_desc is AVAILABILITY_REPLICA . Looks like it is waiting on other side to commit transactions. How do i troubleshoot or make it better? I did everything i shouldn't be doing ( cleared cache, restarted sql, shrunk file with different options ) still wasn't able to reduce used space.
Definitely check the space available as Jeffrey has mentioned. If space is fine (I'm guessing it's not), you can get a good idea of what is going on with the logs for the primary and replicas using performance monitor. This article explains the counters for monitoring this:
SQL Server, Database Replica
Sue
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply