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:

DBCC SHRINKFILE (N'WWI_Log' , 10)

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)

, , , , ,
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

2 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
    WHERE
    f.type = 1
    GROUP BY
    s.[name]
    ,f.[name]
    ORDER BY 2 DESC

    Reply

Leave a Reply

Menu