A 9017 informational message is logged when you start an instance of SQL Server or restore or attach a database

View products that this article applies to.

Symptoms

When you start an instance of Microsoft SQL Server or restore or attach a database, a 9017 informational message that resembles the following is logged in the SQL Server error log:

Database dbName has more than n virtual log files which is excessive. Too many virtual log files can cause long startup and backup times. Consider shrinking the log and using a different growth increment to reduce the number of virtual log files.

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.

↑ Back to the top

Cause

This problem occurs when you specify small values for the FILEGROWTH parameter for your log file.

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.

↑ Back to the top

Resolution

To resolve this problem, follow these steps:

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:

http://msdn.microsoft.com/en-us/library/ms365418.aspx#AddOrEnlarge
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: It takes a long time to restore a database in SQL Server 2008 R2, SQL Server 2008, or SQL Server 2012

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

↑ Back to the top

More Information

How to check the number of VLF segments in a database

You 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:

{Log was backed up. Database: mydbname, creation_date_(time): date(time), first LSN: 1:5068:70, last LSN: 1:5108:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'C:\folder\logbackup1.trn'}). This is an informational message only. No user action is required.

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 replication

Too 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:

949523 The latency of a transactional replication is high in SQL Server 2005 when the value of the "Initial Size" property and the value of the Autogrowth property are small

The effect of lots of VLFs on database mirroring

Too 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:

2455009 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

↑ Back to the top