How to Detect Virtual Log Files in SQL Server Transaction Log File
Aug 17, 2012
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 Transactional 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 Transactional Log file was 700 MB.
Query to Identify Virtual Log Files in Transaction Log File
In the below snippet you could see that BPO database had 196 Virtual Log Files. The 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 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 Transactional Log file has resulted in Transactional Log Files getting fragmented. To change Auto Growth settings for Data and Transactional log file refer the following article "How to Change SQL Server Database Auto Growth Settings".
This scenario can result 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.
Performance Improvement Tips: - Configuring Database Instant File Initialization Feature of SQL Server to improve the time taken to grow data and log files.
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.
Read the following article which explains "How to Fix Virtual Log Files in SQL Server Transactional Log file"
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 Transactional Log file" article.
Continue Free Learning...
Geeks who read this article also read…
- How to Attach Database Without a Transaction Log File in SQL Server
- Troubleshooting SYSPOLICY_PURGE_HISTORY Job Failure in Stand Alone Instance SQL Server 2008
- Troubleshooting OLE DB Provider Microsoft.ACE.OLEDB.12.0 is not registered Error
- Troubleshooting SQL Server blocked access to procedure sp_send_dbmail
- Performance Dashboard Reports in SQL Server 2012
- Tips to Avoid Account Lockout Issues
- Encrypt Database Backups in SQL Server Using MEDIAPASSWORD Option
- Using SP_SERVER_DIAGNOSTICS Stored Procedure Quickly Gather Diagnostic Data and Health Information in SQL Server 2012
- New Features in Microsoft SQL Server 2012 for Database Administrators
- New Features in SQL Server 2012 for Database Developers
- Why an SQL Server Database from a higher version cannot be restored onto a lower version?
- How to identify if the database was upgraded from a previous version of SQL Server
- Installing SQL Server 2008 R2 on Windows Server 2008 R2
- Using Transparent Data Encryption Feature of SQL Server 2008
- New Date and Time Data Types in SQL Server 2008
- Date and Time Functions in SQL Server 2008
- Configuring Database Instant File Initialization Feature of SQL Server
- Changing the default location of SQL Server Data and Log files
- Changing Default SQL Server Backup Folder in SQL Server 2008
- How to repair a Suspect Database in SQL Server
- Steps to Rebuild System Databases in SQL Server
- How to Get Exclusive Access to SQL Server Database
- Read More SQL Server Articles…