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)
