Database has more than 1000 virtual log files which is excessive – informational message 9017

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

VLDB and Zeroing out - SQL Server DBA

The transaction log for database is full due to ACTIVE_TRANSACTION

Msg 5042 - The file cannot be removed because it is not empty and dbcc shrinkfile emptyfile