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

Article translations Article translations
Article ID: 2882905 - View products that this article applies to.
Expand all | Collapse all

On This Page

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.

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.

Resolution

To resolve this problem, follow these steps:
  1. Reduce your transaction log by using DBCC SHRINKDB or by using SQL Server Management Studio.
  2. 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
  3. 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:


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:
  1. Find the LSN for the earliest transaction log backup for the database in your SQL Errorlog (for example, LSN: 1:5108:1).
  2. Find the latest LSN for the transaction log backup in the SQL Errorlog (for example, LSN:10,235: 5108: 1).
  3. 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

References

For more information, see the following topics on the Microsoft Developer Network (MSDN) website:

Shrinking the Transaction Log

Factors That Can Delay Log Truncation

Transaction Log Truncation

Transaction Log Logical Architecture

Transaction Log Physical Architecture


Properties

Article ID: 2882905 - Last Review: September 11, 2013 - Revision: 1.0
Applies to
  • Microsoft SQL Server 2012 Developer
  • Microsoft SQL Server 2012 Enterprise
  • Microsoft SQL Server 2012 Standard
  • Microsoft SQL Server 2008 R2 Datacenter
  • Microsoft SQL Server 2008 R2 Developer
  • Microsoft SQL Server 2008 R2 Enterprise
  • Microsoft SQL Server 2008 R2 Standard
  • Microsoft SQL Server 2008 Developer
  • Microsoft SQL Server 2008 Enterprise
  • Microsoft SQL Server 2008 Standard
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Standard Edition
Keywords: 
kbexpertiseinter kbprb kbsurveynew KB2882905

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com