How to fix Error 9017 on Windows Server 2025 / Windows 11

by

Troubleshooting Guide: SQL Server Error 9017 – Excessive Virtual Log Files (VLFs)

Why This Error Happens

This error occurs when the transaction log file has grown or auto-grown in small, frequent increments over time, causing SQL Server to create an excessive number of Virtual Log Files (VLFs) internally. Each growth event splits the log into additional VLFs, and when growth settings use a small fixed size (e.g., 1 MB) or percentage-based auto-growth on a large log, the VLF count can balloon into the thousands. A high VLF count degrades performance because SQL Server must scan and manage each VLF header during startup, recovery, and backup/restore operations.

Step-by-Step Resolution Guide

Step 1: Identify the Current VLF Count

  • Open SQL Server Management Studio (SSMS) and connect to the affected instance.
  • Run DBCC LOGINFO('YourDatabaseName'); and count the returned rows, or use SELECT * FROM sys.dm_db_log_info(DB_ID('YourDatabaseName')); on SQL Server 2025 for a cleaner output.
  • Confirm the count exceeds the recommended threshold (typically flagged above 1,000, with concern starting around 10,000+).

Step 2: Shrink the Transaction Log File

  • Ensure a recent full backup and log backup exist before proceeding (shrinking requires log space to be reusable).
  • Run the following in SSMS Query Editor:
  USE YourDatabaseName;
  GO
  BACKUP LOG YourDatabaseName TO DISK = 'C:\Backups\YourDatabaseName_log.trn';
  GO
  DBCC SHRINKFILE (YourDatabaseName_log, 1);
  GO
  • Verify the log file name using sp_helpfile if the logical name differs from the default.

Step 3: Regrow the Log File with Proper Sizing

  • Manually grow the log file to its intended operational size in one or few large increments rather than relying on auto-growth from a small base.
  • Example command:
  ALTER DATABASE YourDatabaseName
  MODIFY FILE (NAME = YourDatabaseName_log, SIZE = 8192MB);
  GO
  • This single growth event creates VLFs at the optimal size/count ratio defined by SQL Server 2025’s improved VLF algorithm.

Step 4: Configure Sensible Auto-Growth Settings

  • In SSMS, right-click the database → Properties → Files → set Autogrowth for the log file to a fixed MB value (e.g., 512 MB or 1024 MB) instead of a percentage.
  • Alternatively, via T-SQL:
  ALTER DATABASE YourDatabaseName
  MODIFY FILE (NAME = YourDatabaseName_log, FILEGROWTH = 512MB);
  GO
  • Avoid growth increments below 64 MB and percentage-based growth on large logs going forward.

Step 5: Restart SQL Server Service (If Startup Was Already Impacted)

  • Open Services.msc on Windows Server 2025 / Windows 11, locate SQL Server (MSSQLSERVER) or the named instance.
  • Right-click and select Restart to confirm faster startup times post-fix.
  • Monitor the SQL Server Error Log at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for recovery timing improvements.

Alternative or Verification Steps

  • Re-run SELECT * FROM sys.dm_db_log_info(DB_ID('YourDatabaseName')); and confirm the VLF count is now in a healthy range (ideally under 50-100 depending on log size).
  • If shrinking fails due to an open transaction or replication holding the log active, check log_reuse_wait_desc in sys.databases to identify and resolve the blocking factor before retrying Step 2.