Bobcares

WeSupport

Call Us! 1-800-383-5193
Call Us! 1-800-383-5193
Call Us! 1-800-383-5193

Need Help?

Emergency Response Time custom

Our experts have had an average response time of 11.98 minutes in June 2021 to fix urgent issues.

We will keep your servers stable, secure and fast at all times for one fixed price.

How to find total number of Virtual Log Files in SQL Server

by | Jan 22, 2021

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.

virtual log files in sql server

 

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.

PREVENT YOUR SERVER FROM CRASHING!

Never again lose customers to poor server speed! Let us help you.

Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.

GET STARTED

var google_conversion_label = "owonCMyG5nEQ0aD71QM";

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

server management

Spend time on your business, not on your servers.

TALK TO US

Or click here to learn more.

server management

Spend time on your business, not on your servers.

TALK TO US

Or click here to learn more.

Categories:

Tags: