SQL SERVER – Query to List Active and Inactive VLF


Recently, I had a very interesting experience with one of my clients where we observed a large number of VLF files impacting negatively to their performance. We used the in-famous DBCC SHRINKFILE to reduce the VLFs. During the  Comprehensive Database Performance Health Check, I had also helped the client build a query to list active and inactive VLF. Let us see that here today.

If you wonder what is VLF, here is the definition of the VLF.

VLF stands for Virtual Log File. In SQL Server transaction log file is made up of one or more number of virtual log files. Too