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.