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:
- Why Should You Always Turn Off Database Auto Shrink Property?
- Understand the Best Value for Database File Autogrowth Setting
- What is Checkpoint Process in SQL Server?
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
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
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.
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);
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.
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
)
Check and verify the newly added log file by running sp_helpdb command.
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.
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
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
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)
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.
I hope you like this article. Please follow us on our facebook page and on Twitter handle to get latest updates.
Read More:
- SQL Server Architecture Interview Questions & Answers
- Manage Transaction Log File during Data Load
- How to Improve Bulk Data Load Performance?
- Fix SQL Server Error 1105: Could Not Allocate Space for Object in Database because the Filegroup is Full
- How to Fix SQL Error 1005: A Comprehensive Guide - April 9, 2023
- How to Fix SQL Server Error 207 – Invalid Column Name - April 9, 2023
- How to Fix SQL Error 1045: Resolving Access Denied Issues - April 8, 2023
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
Hi Singh, this is pretty article with interesting and clear way exposed, keep doing it, thanks.