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 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;
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.
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.
- SQL SERVER – Detect Virtual Log Files (VLF) in LDF
- SQL SERVER – Reduce the Virtual Log Files (VLFs) from LDF file
- How to Reduce High Virtual Log File (VLF) Count? – Interview Question of the Week #162
- How to Get VLF Count and Size in SQL Server? – Interview Question of the Week #161
- SQL SERVER – Cannot Shrink Log File 2 (SQLAuthorityDB_log) Because the Logical Log File Located at the End of the File is in Use
- SQL SERVER – Useful Queries – Why Huge Transaction Log (LDF) File? Fix Low Disk Free Space on the Drive Used by LDF Files
Reference: Pinal Dave (https://blog.sqlauthority.com)
5 Comments. Leave new
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
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?
Thanks,
Brian