24 July,2015 by Jack Vamvas
Question: I get the 9017 Informational Message when I start the SQL Server Instance
Database Mydatabase has more than 1000 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.
What does it mean and what steps can I take to fix?
Answer:The 9017 message appears because a threshold is met on the number of virtual log files for a database. Within a SQL Server Transaction Log file exist virtual log files (VLF) .
The amount and size of VLFs is dependant on the size that is added when there is a transaction log growth. During sql server tuning I see lots of databases set at the default initial size, which is normally to small for most Production systems and the default growth is at 10 %.
The size to set should be based on workload analysis and the amount of log backups. You can see how a very large write transaction that has no COMMIT will trigger regularly the log growth . Continious triggering of log growth will cause a large amount of VLF. Read more on SQL LOG FILE AUTOGROW performance troubleshooting
On some very large databases with large write activity jobs , I preallocate the transaction log – which has a positive impact on the job runtime. Read more on my experiences instant file initialization.SQL Server faster restores with instant file initialisation - SQL Server ...
As a general approach – aim for a smaller amount of large growths as opposed to many small growths. This will keep your VLF count lower. You’ll need to perform some analysis on the impact of log growth.
Increase the transaction log file as this will avoid as many auto growths.
Increase the FILEGROWTH parameter to a large value. The value is dependant on workload analysis
To fix the problem, follow these instructions
1. Commit the Transaction Log backup of the database
2. Use DBCC SHRINKFILE to shrink the Transaction Log file. Try and complete this work out of business hrs.
3.Expand the Transaction Log back to its target initial size
4.Set the Transaction Log growth size to be in units of 1 GB. This size will depend on your environment and activity. Capacity planning can yield very good results and can help decide on the Initial Size
Following the pattern above , and example of code would be :
--STEP 1 :backup the transaction log - disk or other storage device BACKUP LOG [my_database] TO DISK = N'M:\mydb.bak' GO --STEP 2 : Transaction Log Shrink USE My_Database GO DBCC SHRINKFILE('my_db_log_file',TRUNCATEONLY) --STEP 3: Alter the modify size - either preallocate or min size 1 GB- autogrowth 1 GB USE master GO ALTER DATABASE [my_database] MODIFY FILE ( NAME = N'my_database_log', SIZE = 1024000KB, FILEGROWTH = 1024000KB) GO
Read More on VLDB zeroing out, ACTIVE_TRANSACTION,Msg 5042