SQL Server internally manages the Log file into multiple smaller chunks called Virtual Log Files or VLFs. A Virtual Log File is a smaller file inside Log file which contains the actual log records which are actively written inside them.

Here at Ibmi Media, as part of our Server Management Services, we regularly help our Customers to perform SQL related queries.

In this context, we shall look at how to find the total number of VLFs in SQL Server.

How to find the total number of virtual log files in SQL server ?

Now let's take a look at how our Support Experts find the total number of virtual log files in the SQL Server.

We are making use of the DMF(dynamic management functions) sys.dm_db_log_info which provides the VLF information of the transaction log files. However, this needs a database ID for input.

When we specify NULL or DEFAULT value, it will provide the VLF information of the current database.

How to Display the total number of virtual log files ?

Here is the script that we use to display the total number of virtual log files for all databases hosted on a SQL server instance:

SELECT name, count(d.database_id) as “Total_VLF_Count” from sys.databases sd
cross apply sys.dm_db_log_info(sd.database_id) d
group by name

As a result, this script will display the total number of VLFs in each database. If we increase the size of log file and also add another log file to the database then the VLF counts will increase.

How to Displaying filtered output ?

We can also filter the output like which database has what number of VLF counts. Also, we can display what all databases that have less or more number of VLF counts of a specific value.

For that, we run the below script to get the name of databases which has more than 10 virtual log files:

SELECT name, count(d.database_id) as “Total_VLF_Count” from sys.databases sd
cross apply sys.dm_db_log_info(sd.database_id) d
group by name
having count(d.database_id)>10

Similarly, we run the below script to check the database names that have less than 10 VLF:

SELECT name, count(d.database_id) as “Total_VLF_Count” from sys.databases sd
cross apply sys.dm_db_log_info(sd.database_id) d
group by name
having count(d.database_id)<10

How to view columns ?

We run the below script to view all the columns for a database:

SELECT * from sys.dm_db_log_info(6)
sys.dm_db_log_info output

[Need urgent assistance with SQL queries? – We'll help you. ]