We can make use of DMF sys.dm_db_log_info to find the number of virtual log files in SQL Server.

Here at Bobcares, we have seen several such SQL related queries as part of our Server Management Services for web hosts and online service providers.

Today we’ll take a 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 Engineers 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.

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.

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

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 any assistance with SQL queries? – We’ll help you]

Conclusion

In short, the DMF sys.dm_db_log_info specifically looks at virtual log files or VLFs for which it needs a database ID for input. Today, we saw how our Support Engineers find the total number of VLFs.