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

by

Troubleshooting Guide: Error 5175 – File Expanded to Allow Recovery to Succeed

Why This Error Happens

This error occurs when SQL Server encounters a database file during crash recovery that is smaller than what the transaction log indicates it should be, forcing SQL Server to automatically grow the file to complete the recovery process. This typically happens after an unexpected shutdown, forced termination of the SQL Server service, or a VM snapshot restore where the physical data/log file was truncated or did not match its last known logical size. It can also occur when disk space constraints or third-party backup tools interact improperly with the file system, leaving file metadata inconsistent with actual allocation.

Step-by-Step Resolution Guide

Step 1: Verify Disk Space and File System Health

  • Open File Explorer or run Get-PSDrive in PowerShell to confirm the volume hosting .mdf/.ldf files has sufficient free space (at least 2-3x the database size recommended).
  • Run chkdsk C: /f /r (replace C: with the correct drive letter) on Windows Server 2025 to rule out underlying file system corruption.
  • Check Windows Event Viewer under Windows Logs > System for Disk or NTFS warnings around the time of the crash.

Step 2: Review SQL Server Error Log for Recovery Status

  • Navigate to C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG (path may vary based on instance name/version folder).
  • Search for entries immediately before and after Error 5175, such as “Starting up database” and “Recovery is writing a checkpoint.”
  • Confirm whether the database successfully completed recovery by looking for the message “Recovery completed” or “CHECKDB found 0 allocation errors.”

Step 3: Run DBCC CHECKDB to Validate Database Integrity

  • Open SQL Server Management Studio (SSMS) and connect to the affected instance.
  • Execute the following query against the impacted database:
  DBCC CHECKDB (YourDatabaseName) WITH NO_INFOMSGS, ALL_ERRORMSGS;
  • If corruption is found, review repair options (REPAIR_REBUILD preferred over REPAIR_ALLOW_DATA_LOSS) based on the severity reported.

Step 4: Adjust Autogrowth Settings to Prevent Recurrence

  • Right-click the database in SSMS, select Properties > Files.
  • Set Autogrowth for both data and log files to a fixed MB value (e.g., 256MB) instead of a percentage, avoiding unpredictable expansion behavior.
  • Ensure Instant File Initialization is enabled by confirming the SQL Server service account has the “Perform Volume Maintenance Tasks” permission in secpol.msc under Local Policies > User Rights Assignment.

Step 5: Restart SQL Server Service and Monitor

  • Open Services.msc on Windows Server 2025, locate SQL Server (MSSQLSERVER), and restart it.
  • Monitor the ERRORLOG again for successful database recovery messages without repeated Error 5175 entries.

Alternative or Verification Steps

  • Run SELECT state_desc FROM sys.databases WHERE name = 'YourDatabaseName'; in SSMS to confirm the database status shows ONLINE rather than RECOVERY_PENDING or SUSPECT.
  • If the error persists, restore the database from the last known good backup using RESTORE DATABASE YourDatabaseName FROM DISK = 'BackupPath' WITH RECOVERY; to bypass file-level inconsistencies entirely.