Too many virtual log files can adversely affect the recovery time of the database.
Additionally, if you use Replication or Database Mirroring technologies in your environment, you may notice performance issues with these technologies.
The SQL Server Database Engine divides each physical log file internally into several virtual log files (VLFs). SQL Server 2008 R2 Service Pack 2 and later versions introduced a new message (9017) that is logged when a database starts (either because of the starting of an instance of SQL Server or because of the attaching or restoring of the database) and has more than 1,000 VLFs in SQL Server 2008 R2 or has more than 10,000 VLFS in SQL Server 2012.
Note In SQL Server 2012, although this message is logged when the database has 10,000 VLFs, the actual message that is reported in the error log incorrectly states "1000 VLF." Basically, the warning occurs after 10,000 VLFs. However, the message reports 1,000 VLFs. This issue will be corrected in a future release.
For more information about how the increased number of VLFs could lead to performance issues in replication or database mirroring configurations, see the "More Information" section.
- Reduce your transaction log by using DBCC SHRINKDB or by using SQL Server Management Studio.
- Increase the size of the transaction log file to a larger value to avoid frequent auto growths. For more information, see the following topic on the SQL Server Books Online website:
- Increase the FILEGROWTH parameter to a larger value than what is currently configured. This should be based on the activity of your database and how frequently your log file is growing.
Additionally, we recommend that you consider installing the following fixes, depending on the version of SQL Server that you are currently running:
FIX: Slow performance when you recover a database if there are many VLFs inside the transaction log in SQL Server 2005, SQL Server 2008, or SQL Server 2008 R2
FIX: The principal database is not recovered if the database has a large number of virtual log files in SQL Server 2005 or SQL Server 2008
FIX: Recovery takes longer than expected for a database in a SQL Server 2008 or SQL Server 2008 R2 environment
How to check the number of VLF segments in a databaseYou can find the number of VLF segments in a database by finding the difference between the earliest and the latest log sequence numbers (LSNs) of the transaction log backups for the database.
You can find the LSN of the transaction log backup by checking your SQL Server error log for a message that resembles the following:
Note In this message, the LSN of the transaction log is 1. (It is the first number before the first colon in "LSN: 1:5068:70.")
To do this, follow these steps:
- Find the LSN for the earliest transaction log backup for the database in your SQL Errorlog (for example, LSN: 1:5108:1).
- Find the latest LSN for the transaction log backup in the SQL Errorlog (for example, LSN:10,235: 5108: 1).
- The number of VLF segments is the difference between the latest LSN and the earliest LSN (In this case, it is 10,235-1 = 10,234).
The effect of lots of VLFs on replicationToo many log files can affect replication because the log reader process must scan every virtual log file for transactions that are marked for replication. You can see this behavior by tracing the performance of the sp_replcmds stored procedure. The log reader process uses the sp_replcmds stored procedure to scan the virtual log files and to read the transactions that are marked for replication. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
The effect of lots of VLFs on database mirroringToo many log files can also affect database mirroring. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
Article ID: 2882905 - Last Review: Sep 11, 2013 - Revision: 1