We have an AlwaysOn Setup where in we have 3 replicas in all. Primary Replica and the one of the Secondary Replicas are in the same datacenter while the second secondary replica is in a different datacenter and works as our DR.

A couple of days back some Devs rebuilt a few super large indexes on one of our primary database on the primary replica which resulted in a log send queue of about 175 GB for that particular database for the secondary DR replica.

Since the AGs deploy flow control, the movement of log from the primary to the secondary DR replica was extremely slow and it took more than 2 days for the log send queue to come down to around 20 GB. All this while i had to continue to increase the size of the transaction log on the affected primary database to ensure that the database continues to accept transactions. Currently the transaction log size for the affected primary database is at 300 GB approx. My concern is that while i am checking DBCC sqlperf(logspace) to check the utilization of transaction log, it shows that the log is just about 5% used, however it doesn't let me shrink the log. In all there are 372 VLFs for this particular database out of which 16 VLFs show “Status” as 0 which indicates that these VLFs are not active. Rest all the VLFs show “Status” marked as 2, which essentially means that these VLFs have not been inactivated and are not ready for truncation. I understand that till the time the DR replica does not send back an acknowledgement to the Primary replica, it will hold on to the logs, however i fail to understand if the logs are being held onto and if the VLFs are being shown as active, then why the DBCC SQLPerf(Logspace) shows the log utilization as merely 5%.

Please find attached relevant snapshots.

I would appreciate if someone could throw some clarity on this. I will be glad to provide any further information required on this issue.

enter image description here

put on hold as off-topic by Philᵀᴹ, hot2use, LowlyDBA, Michael Green, Mr.Brownstone 18 hours ago

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – hot2use, LowlyDBA, Michael Green, Mr.Brownstone
If this question can be reworded to fit the rules in the help center, please edit the question.

  • I had a similar situation happen recently (someone accidentally rebuilt all the indexes through an SSIS package). You have 356 active VLF files out of 372. Which is probably why your log won't shrink. Try running it immediately after a tlog backup when the most/all of the the files are empty. – Jacob H Aug 10 at 11:00
  • Sure Jacob! I have tried that, the log backups are scheduled once every 15 minutes and i did try shrinking the log a couple of times soon after the log backup but that didnt help. The log_reuse_wait_desc shows as Availability_Replica though. – Ritesh Chawla Aug 10 at 11:07
  • Oh right! You're in an AG. Fortunately, haven't had this happen in our AG yet. This thread may help: dba.stackexchange.com/questions/73850/… – Jacob H Aug 10 at 11:13
  • I have gone through the given link but it doesn't really say how to do the steps recommended there. It suggests to move used pages to start of the transaction log, before you shrink it. T-log is written sequentially and I am not sure if we can do this. I am trying to find more on this. I want to understand why would SQL server show the log usage as 5% and then have all the VLFs marked as active. Thanks for pointing me to that link Jacob. – Ritesh Chawla Aug 10 at 11:47
  • It does mention that all replicas have to be sync. I do not know if that is true, but if you have one async that may be the issue. I don't want to make a recommendation for you because I haven't had to do this, but maybe that is the problem. You tried taking a few tlog backups in a row first? – Jacob H Aug 10 at 12:01