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

by

Troubleshooting Guide: SQL Server Error 5118

Why This Error Happens

This error occurs when NTFS file compression has been manually applied to a database file (.mdf, .ndf, or .ldf) that belongs to a database or filegroup which is not set to READ_ONLY. SQL Server 2025 does not support running read/write operations against compressed data files because the storage engine cannot reliably manage page allocations and transaction logging on compressed NTFS volumes. This typically happens when an administrator compresses a folder on a Windows Server 2025 volume to save disk space without realizing SQL Server data files reside within it, or when a database file is moved to a drive with “Compress this drive to save disk space” enabled by default.

Step-by-Step Resolution Guide

Step 1: Identify the Compressed File and Take the Database Offline

  • Open SQL Server Management Studio (SSMS) and locate the database referenced in the %ls placeholder of the error message.
  • Right-click the database, navigate to Tasks > Take Offline to prevent file-lock conflicts during the decompression process.
  • Alternatively, run the following T-SQL command to force the database offline:
  ALTER DATABASE [YourDatabaseName] SET OFFLINE WITH ROLLBACK IMMEDIATE;

Step 2: Decompress the File via File Explorer or PowerShell

  • Navigate to the physical file location, typically found at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\.
  • Right-click the specific .mdf or .ldf file, select Properties > General > Advanced.
  • Uncheck the box labeled “Compress contents to save disk space” and click OK, then Apply.
  • For a faster method on Windows Server 2025 Core installations, use PowerShell to clear the compression attribute:
  Compact.exe /U "C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\YourDatabase.mdf"

Step 3: Verify Filegroup and Volume Settings

  • Ensure the parent folder or Windows Volume itself does not have the “Compress drive to save space” attribute enabled, as this will re-compress the file automatically.
  • If the goal was data compression, verify you intended to use SQL Server’s native Row or Page Compression (via ALTER TABLE...REBUILD WITH (DATA_COMPRESSION = PAGE)) rather than NTFS compression, as these are mutually exclusive methodologies.

Step 4: Bring the Database Back Online

  • Return to SSMS or use the following query to restart the engine’s access to the file:
  ALTER DATABASE [YourDatabaseName] SET ONLINE;
  • Monitor the SQL Server Error Log to confirm the database transitions to a healthy “ONLINE” state without re-triggering Error 5118.

Alternative or Verification Steps

  • Verification: Run SELECT name, state_desc FROM sys.databases WHERE name = 'YourDatabaseName'; to confirm the state_desc column returns ONLINE.
  • Alternative Fix: If you require compression for archival purposes, set the specific filegroup to READ_ONLY first using ALTER DATABASE [YourDatabaseName] MODIFY FILEGROUP [FilegroupName] READONLY;before re-applying NTFS compression, as SQL Server permits compressed files only within read-only contexts.