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

by

Why This Error Happens

This message appears when SQL Server detects that a database file residing on an NTFS-compressed folder or volume has been automatically decompressed by the engine. SQL Server does not support running data or log files on NTFS compression because it conflicts with the internal page allocation and I/O patterns, so the engine silently strips the compression attribute to protect data integrity. While technically informational rather than fatal, it often signals that an administrator or script enabled compression on a directory that later became a SQL Server file location, causing unexpected disk I/O overhead until corrected.

Step-by-Step Resolution Guide

Step 1: Identify the Affected File and Path

  • Check the SQL Server Error Log or Event Viewer (Application log, Event ID 5196) to capture the exact file path referenced in the message.
  • Run the following query to cross-reference the file with its database:
  SELECT name, physical_name FROM sys.master_files;

Step 2: Verify NTFS Compression State

  • Open File Explorer, navigate to the folder in question (e.g., D:\SQLData\MSSQL\DATA), right-click the file or folder, and select Properties > Advanced.
  • Confirm whether “Compress contents to save disk space” is checked.
  • Alternatively, use PowerShell to check compression state in bulk:
  Get-ChildItem "D:\SQLData\MSSQL\DATA" | ForEach-Object { 
      $attr = (Get-Item $_.FullName).Attributes 
      "$($_.Name): $attr" 
  }

Step 3: Remove Compression at the Folder/Volume Level

  • Uncheck compression via Properties > Advanced in File Explorer, applying the change to the folder and all subfiles.
  • Or disable it via command line for the entire directory tree:
  compact /u /s:D:\SQLData\MSSQL\DATA
  • Ensure no backup, maintenance, or deployment scripts re-enable compression on this path (check Scheduled Tasks and DBA automation scripts).

Step 4: Restart SQL Server and Reattach/Validate Database

  • Restart the SQL Server (MSSQLSERVER) service via SQL Server Configuration Manager to clear any cached I/O handles.
  • Run DBCC CHECKDB against the affected database to confirm no corruption occurred during the compression/decompression cycle:
  DBCC CHECKDB (YourDatabaseName) WITH NO_INFOMSGS, ALL_ERRORMSGS;

Step 5: Move Files to a Non-Compressed Volume (If Required)

  • If the underlying volume itself is compressed at the file system level (not just the folder), migrate the data/log files to a dedicated NTFS volume without compression enabled.
  • Use ALTER DATABASE ... MODIFY FILE to update the logical file path after physically moving the file offline.

Alternative or Verification Steps

  • Re-run Get-ChildItem or check folder Properties > Advanced again to confirm the compression attribute no longer appears on the data/log files.
  • Monitor the SQL Server Error Log for 24–48 hours to ensure Event ID 5196 does not reoccur; if it does, audit Group Policy or storage provisioning scripts that may be re-applying NTFS compression automatically at the volume level.