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.

SQL SERVER - Query to List Active and Inactive VLF InactiveVLF0-800x211

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 many virtual log files can cause transaction log backup to slow down as well as the database restore process. There have been incidents where any modification to the database (Insert/Update/Delete) has been slowed down.

Here is the script when you run will display active and inactive VLF in your database.

SELECT [name] AS 'Database Name',
COUNT(li.database_id) AS 'VLF Count',
SUM(li.vlf_size_mb) AS 'VLF Size (MB)',
SUM(CAST(li.vlf_active AS INT)) AS 'Active VLF',
SUM(li.vlf_active*li.vlf_size_mb) AS 'Active VLF Size (MB)',
COUNT(li.database_id)-SUM(CAST(li.vlf_active AS INT)) AS 'Inactive VLF',
SUM(li.vlf_size_mb)-SUM(li.vlf_active*li.vlf_size_mb) AS 'Inactive VLF Size (MB)'
FROM sys.databases s
CROSS APPLY sys.dm_db_log_info(s.database_id) li
GROUP BY [name]
ORDER BY COUNT(li.database_id) DESC;

SQL SERVER - Query to List Active and Inactive VLF InactiveVLF1

If you see a lot of inactive VLF and a high number of inactive VLF, you can easily shrink the log file using the following command. For example, if you want to shrink the WideWorldImporters database, you can run the following query:


Upon running the query, you can run the first query one more time and see that most of the inactive VLF of the database has been removed.

SQL SERVER - Query to List Active and Inactive VLF InactiveVLF2

Well, there are a lot many things we can discuss log file and this is just the beginning of the same. Here are a few additional blog posts which are related to this topic. Meanwhile, you can always connect me via twitter.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Shrinking Database, SQL Log, SQL Scripts, SQL Server, SQL Server DBCC, Transaction Log
Previous Post
SQL SERVER – Reducing TempDB Recompilation with Fixed Plan
Next Post
SQL SERVER – Scheduler_ID with Large Number in dm_os_schedulers

Related Posts

5 Comments. Leave new

  • Kacper Ksieski
    March 14, 2020 10:23 am

    I wrote a view using your query. I enriched it with a field to auto create the DBCC command. The user can copy paste and run the string for any desired db.

    CREATE VIEW v_virtual_log_files AS
    SELECT TOP 7777777
    s.[name] AS ‘Database Name’
    ,COUNT(li.database_id) AS ‘VLF Count’
    ,SUM(li.vlf_size_mb) AS ‘VLF Size (MB)’
    ,SUM(CAST(li.vlf_active AS INT)) AS ‘Active VLF’
    ,SUM(li.vlf_active * li.vlf_size_mb) AS ‘Active VLF Size (MB)’
    ,COUNT(li.database_id) – SUM(CAST(li.vlf_active AS INT)) AS ‘Inactive VLF’
    ,SUM(li.vlf_size_mb) – SUM(li.vlf_active*li.vlf_size_mb) AS ‘Inactive VLF Size (MB)’
    ,’DBCC SHRINKFILE (N’ + ”” + f.[name] + ”” + ‘ , 10)’ AS ‘DBCC Shrink Command’
    FROM sys.databases s
    CROSS APPLY sys.dm_db_log_info(s.database_id) li
    JOIN sys.master_files f
    ON s.database_id = f.database_id
    f.type = 1

  • Fattah Mortazavi
    September 15, 2020 7:41 pm

    Hi Pinal, I appreciate so much your usefull hints. They are really helpful. I’m trying for a while without success to find out all user on database level, from howm the CONNECT is revoked (I don’t mean idisabled server logins). Maybe you have a hint.
    Thank you
    Fattah Mortazavi

  • For peoples information, only works with SQL Server 2016 SP 2 and later, that DMV is not available in earlier versions.

  • This blog always has a lot of good info for me, thank you.
    I have a SQLExpress 2017 db with Filestream and Memory Optimized Tables. Log is always 99% used and grows daily by 16MB although full backup done daily and log backups done hourly and only few updates/inserts. Tried your scripts but log keeps growing. db is 550MB, log now 1700MB. Right after log backup Active VLF=133(1684MB), Inactive VLF=1(16MB). Any idea what is happening?


Leave a Reply