How to Get Total Number of Virtual Log Files inside SQL Server Database Log Files?

Today, i am going to discuss about virtual log files in SQL Server and query to find number of virtual log files present in transaction log files.

Related Articles:

Virtual Log File

As we know Transaction Log Files internally divided in to small virtual log files. Virtual Log Files also referred as VLF/VLFs. The size or number of virtual log files cannot be configured by administrators but it can be affected by number of reasons.

One of the reason is creating or extending log files. The size of the virtual files after a log file has been extended is the sum of the size of the existing log and the size of the new file increment. Here I will show you how VLFs will be increase when we add a new log file or increase the size of transaction log file.

Having large no. of virtual log files are not a good sign and can affect the performance of backup, restore and database startups. Large numbers means close to thousand or more than thousand vlfs. You will not see any visible impact if you have approx. few hundred vlfs.  I would suggest you to read Transaction Log File Architecture to understand more about this.

We can reduce the number of virtual log files by either Backup the Transaction Log of the database or by shrinking the Transaction Log as much as possible using DBCC SHRINKFILE.  Make sure to do this exercise during a maintenance window.

How to Find Total Number of Virtual Log Files?

Now, I will explain how to get total number of virtual log files (VLF/VLFs) for each database on your SQL Server instance. Microsoft has introduced new DMF to monitor, alert, and avert potential transaction log issues in SQL Server 2017. Earlier we were using DBCC LOGINFO to get such information but now we can get in depth information to deal with transaction log queries with the help of this DMF.

The new DMF is sys.dm_db_log_info that will returns VLF information of the transaction log files.  If we will specify NULL or DEFAULT value, it will return VLF information of the current database. The built-in function DB_ID can also be specified to get details of a particular database. The sys.dm_db_log_info DMF replaces the DBCC LOGINFO statement from earlier versions.

Below script will show you total no. 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

total no of vlfs for all db

We can see the total no. of VLFs in each database. Now we can create a new database and check the VLF count. I have created a new database “Techyaz” and see the database file details.

Create database Techyaz
GO
sp_helpdb techyaz

Create db and check files

Now again check the total no. of VLFs by running same command which I have executed in step 1. We can see total no. of vlfs are 4 for techyaz database.

total vlf count post techyaz db creation

Now I will increase the size of the log file and see the impact of vlf count. Run below command to increase the size of log file.

USE master;
GO
ALTER DATABASE Techyaz
MODIFY FILE
(NAME = techyaz_log,
SIZE = 15MB);

Increase the file size by 15 MB

Now again see the no. of vlfs for each database and here we can see, total no. of vlfs have been increase to 8 now.

total vlfs post increasing the size of log file

Similarly we can notice the impact of vlf if we add another log file on this database. Run below command to add a log file to the database Techyaz. I kept the size of this log file is 5MB.

ALTER DATABASE Techyaz
ADD LOG FILE 
(
    NAME = techyaz_log3,
    FILENAME = '/var/opt/mssql/data/Techyaz_log3.ldf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
)

Add Log File to techyaz db

Check and verify the newly added log file by running sp_helpdb command.

check and verify newly added files

Check again the total vlf count for this database. Here we can see the total no of vlfs are now 12 in this database. So it is proved that vlf counts are increased if we increase the size of log file as well as we add another log file to the database.

Total VLF post adding log file

This DMF is also useful in getting filtered output like which database have a specified no of vlf counts and what all databases that have less or more no of vlf counts of a specified value.

Run below command to get the name of databases which has more than 10 virtual log files. You can change the 10 value to 100 or 1000 or 10000. It is up to your requirement.

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

Databases that have more VLFs than specified value

We can see only one database has more than 10 vlfs that is techyaz which we just created. Similarly we can check about the db names which has less than 10 VLFs by running same command, just change the condition of greater than to less than.

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

DBs having less than 10 VLFs

Below is the view of all columns we can see of this DMF. This is very useful DMF and we can get all information as given below. I ran this command for database Techyaz. We can get the db id of this database from step 2 that is 6. We can also see one row for each VLF in the output as this database has 12 VLF so there are 12 rows here along with the details of each VLF.

SELECT * from sys.dm_db_log_info(6)

sys.dm_db_log_info output

We can focus on two columns i.e. vlf_active which is 1 for first vlf and 0 for all that means it indicates whether VLF is in use or not. 0 means vlf is not in use and 1 means vlf is active so we can see only one vlf is active for this database.

Another is vlf_status, it indicate status of the VLF. Possible values include

0 – vlf is inactive
1 – vlf is initialized but unused
2 – vlf is active.

As we know this DMV is the replacement of DBCC LOGINFO so we can compare the output of both. Now run DBCC LOGINFO as see the details for database Techyaz.

DBCC LOGINFO output

I hope you like this article. Please follow us on our facebook page and on Twitter handle to get latest updates.

Read More:

 

Follow me:

Manvendra Deo Singh

I am working as a Technical Architect in one of the top IT consulting firm. I have expertise on all versions of SQL Server since SQL Server 2000. I have lead multiple SQL Server projects like consolidation, upgrades, migrations, HA & DR. I love to share my knowledge. You can contact me on my social accounts for any consulting work.
Manvendra Deo Singh
Follow me:

You may also like...

1 Response

  1. Altaf Hussain says:

    Mr Singh, I have been going through your articles, i must say i learned a lot and appreciate your details analysis and research. Keep doing it

Leave a Reply

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