SQL Server Articles, SQL Server Tips, SQL Server Tutorials, SQL Server Tuning, SQL Server DBA, SQL Server Basics, Training, etc - MyTechMantra.com

How to Detect Virtual Log Files in SQL Server Transaction Log File

This article demonstrates how to Detect Virtual Log Files in SQL Server Transaction Log File. The steps mentioned in this article are applicable on SQL Server 2005 and higher versions. To know more about how to fix Virtual Log File in SQL Server Transaction Log File refer the following article “How to Fix Virtual Log Files in SQL Server Transaction Log file”.

How to Identify Virtual Log Files in SQL Server Transaction Log File

Using DBCC LOGINFO command a DBA can easily identify the number of Virtual Log Files (VLF) which are presently available within a Transaction Log file of SQL Server database.

For this demo, the size of the sample database is 2 GB and the size of the Transaction Log file was 700 MB.

T-SQL Query to Identify Virtual Log Files in Transaction Log File

Use BPO
GO

DBCC LOGINFO
GO
Deduct Virtual Log Files in SQL Server Transaction Log File
Deduct Virtual Log Files in SQL Server Transaction Log File

In the above snippet you could see that BPO database had 196 Virtual Log Files.

The primary reason why this database had so many Virtual Log Files was that initial the log file growth was kept very small i.e. 1MB initial size and allow it to grow 10% every time whenever there is a need.

This was a wrong Autogrowth setting had resulted in frequent log file growth and this ended up creating too many small Virtual Log Files of small size. However, this wrong Auto Growth setting for Transaction Log file has resulted in Transaction Log Files getting fragmented.

To change Auto Growth settings for Data and Transaction log file refer the following article “How to Change SQL Server Database Auto Growth Settings“.

Best Practice

I would recommend you to read Why Model Database Default Settings Customization is Important for SQL Server?

Wrong Autogrowth setting for a Transaction Log file results in slow recovery of the database when the database is started as it will have to recover a very large volume of work. Moreover, the performance of the database will be affected when the query runs which results in Transaction log file growth. For more information on this refer the following Microsoft KB.

It is not a good practice to have such a large number of Virtual Log Files in a database. Ideally a database should have Virtual Log File in a range of 25 – 50. Smaller Autogrowth settings will impact Performance of the database during recovery and well as bring in timeout issues when the log file growth takes longer time.

In a transaction log, having an excessive number of Virtual Log Files (VLFs) can result in slowing down the database recovery process of a database during the database restore event and during startup. The primary reason which results in the creation of an excessive number of Virtual Log Files (VLFs) is a transaction log is when Autogrowth setting is set to be very small in MB. For more information, read Impact of a Large number of Virtual Log Files in SQL Server Transaction Log File

Read the following article which explains “How to Fix Virtual Log Files in SQL Server Transaction Log file

Conclusion

In this article you have seen how you can identify the number of virtual log files in SQL Server Transaction Log file. To fix this issue refer the following “How to Fix Virtual Log Files in SQL Server Transaction Log file” article.

Ashish Mehta

Ashish Kumar Mehta is a database manager, trainer and technical author. He has more than a decade of IT experience in database administration, performance tuning, database development and technical training on Microsoft SQL Server from SQL Server 2000 to SQL Server 2014. Ashish has authored more than 325 technical articles on SQL Server across leading SQL Server technology portals. Over the last few years, he has also developed and delivered many successful projects in database infrastructure; data warehouse and business intelligence; database migration; and upgrade projects for companies such as Hewlett-Packard, Microsoft, Cognizant and Centrica PLC, UK. He holds an engineering degree in computer science and industry standard certifications from Microsoft including MCITP Database Administrator 2005/2008, MCDBA SQL Server 2000 and MCTS .NET Framework 2.0 Web Applications.

Newsletter Signup! Join 15,000+ Professionals




Be Social! Like & Follow Us

Follow us

Don't be shy, get in touch. We love meeting interesting people and making new friends.

Advertisement