MSSQLSERVER_9017
Applies to: 
SQL Server
Details
| Attribute | Value |
|---|---|
| Product Name | SQL Server |
| Event ID | 9017 |
| Event Source | MSSQLSERVER |
| Component | SQLEngine |
| Symbolic Name | LOG_MANY_VLFS |
| Message Text | Database %ls has more than %d 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. |
Explanation
During a database startup, SQL Server detects that a database has a large number of virtual log files (VLFs) and logs this error message. The situations where you can encounter the error are:
- When you start an instance of SQL Server
- Restore a database
- Attach a database
The 9017 informational message that is similar to this example 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, Database Mirroring, or AlwaysOn technologies in your environment, you may notice performance issues with these technologies.
The effect of many 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.
Cause
This problem occurs when you specify small values for the FILEGROWTH parameter for your transaction log file(s).
The SQL Server Database Engine internally divides each physical log file into several virtual log files (VLFs). SQL Server 2008 R2 Service Pack 2 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 and later versions.
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." The warning does occur after 10,000 VLFs. However, the message reports 1,000 VLFs. This issue is corrected in later releases.
User action
To resolve this problem, follow these steps:
You can view the VLF count and average size on your SQL Server by using this query. The result will help you identify which databases to focus on:
SELECT db.name, count(dbl.database_id) as Total_VLF_count, convert(decimal (10,2), avg(dbl.vlf_size_mb)) as Avg_VLF_Size_MB FROM sys.databases db CROSS APPLY sys.dm_db_log_info(db.database_id) dbl GROUP BY db.name ORDER BY Total_VLF_count DESCFor more information, see sys.dm_db_log_info.
Reduce your transaction log by using DBCC SHRINKDB/DBCC SHRINKFILE or by using SQL Server Management Studio.
Perform a one-time increase of the transaction log file size to a large value. This one-time increase is done to avoid frequent auto growths. For more information, see Manage the size of the transaction log file.
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, you can review the following fix articles, depending on the version of SQL Server that you're currently running:
Tip
To determine the optimal VLF distribution for the current transaction log size of all databases in a given instance, and the required growth increments to achieve the required size, see this script.