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

by

Troubleshooting Guide: SQL Server Error 5145 – Autogrow Duration Exceeded

Why This Error Happens

This informational warning occurs when SQL Server 2025 has to automatically expand a data or log file, and that operation takes an unusually long time to complete, blocking active transactions in the process. The root cause is typically a large FILEGROWTH increment (either a large fixed MB value or a percentage-based growth on an already large file) combined with slow underlying disk I/O. On Windows Server 2025, this is frequently compounded when Instant File Initialization (IFI) is not enabled for data files, forcing SQL Server to zero-initialize every new byte of disk space before it can be used.

Step-by-Step Resolution Guide

Step 1: Enable Instant File Initialization (IFI)

  • Open the Local Security Policy editor (secpol.msc) on the Windows Server 2025 host.
  • Navigate to Local Policies > User Rights Assignment and locate “Perform volume maintenance tasks.”
  • Add the SQL Server service account (e.g., NT Service\MSSQLSERVER or your custom domain service account) to this policy.
  • Restart the SQL Server service for the change to take effect; note that this only benefits data files (.mdf/.ndf), not log files (.ldf).

Step 2: Adjust Autogrowth Settings via ALTER DATABASE

  • Open SQL Server Management Studio (SSMS) and connect to the instance reporting the error.
  • Run a query to switch from percentage-based growth to a fixed, smaller MB increment:
  ALTER DATABASE [YourDatabaseName]
  MODIFY FILE (NAME = N'YourLogicalFileName', FILEGROWTH = 256MB);
  • Avoid using a growth increment larger than 1GB for data files unless storage specifically supports high-throughput writes.

Step 3: Proactively Pre-Size Database Files

  • Calculate the expected database size for the next 6–12 months based on current growth trends.
  • Use ALTER DATABASE... MODIFY FILE (SIZE = ...) to manually expand the file during a maintenance window (off-peak hours) rather than relying on an unexpected autogrow event during production traffic.
  • This moves the “pain” of file expansion to a scheduled time rather than an unpredictable, high-traffic moment.

Step 4: Audit Underlying Storage Performance

  • Use Windows Performance Monitor (PerfMon) to check the Avg. Disk sec/Write counter on the volume hosting the .mdf/.ldf files.
  • If latency consistently exceeds 15-20ms, the physical storage (SAN/vDisk) may be the bottleneck rather than the SQL configuration itself.

Alternative or Verification Steps

  • Verify the Fix: Check the SQL Server Error Log or query sys.dm_io_virtual_file_stats after the next natural growth event to confirm the duration (in milliseconds) has significantly decreased.
  • Alternative: If IFI cannot be enabled due to security compliance policies (common in high-security government or finance environments), mitigate the impact by scheduling manual off-hours growth via a SQL Agent Job to avoid triggering the event during business hours.