Our SQL 2016 AlwaysOn database failed over a week ago (round about the same time as we had some high load on the system) and the secondary never came back online. For some reason my alerts were not working and today I ran DBCC SQLPERF(logspace) and realised that we were at 95% log space used. I checked the secondary database and it was in the "Reverting / In recovery" state.
The first thing I did was make sure my log backups were running correctly and that there were no open transactions. I then checked the SQL log and realised there were hundreds of the following error
Time out occurred while waiting for buffer latch -- type 3
I struggled to find any definitive answer to what was causing this and eventually followed the advice of someone and decided to restart the secondary database.
This fixed the issue and after a while the database went from "Recovering" into "Synchronized". However, when I now run DBCC SQLPERF(logspace) it is stuck at around 5% (1.8GB) log space used and has been like that for the past few hours. I have checked and my log backups are running successfully and there are no open transactions. Even if I run it straight after a log backup it still sits at 5%.
Any suggestions on what might be causing the log file to stay at 5% used?
