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

by

Why This Error Happens

Error 5153 occurs because the OVERFLOW clause introduced for memory-optimized filegroup elasticity in SQL Server 2025 is incompatible with the legacy SQL Data Warehouse (SQL DW) emulation layer, informally called the “SQL DW back door,” which is used for backward compatibility with older Parallel Data Warehouse (PDW) workloads. When a database is running under this legacy compatibility mode, the engine blocks any ALTER DATABASE ... MODIFY FILEGROUP statement that includes OVERFLOW, since the DW emulation layer does not recognize the new storage allocation flag. This is typically triggered by an outdated compatibility level, a misconfigured Database Scoped Configuration, or a script ported from an older DW-tier instance that still references deprecated syntax.

Step-by-Step Resolution Guide

Step 1: Confirm the Database Compatibility Mode

  • Open SQL Server Management Studio (SSMS) 21 or Azure Data Studio connected to your SQL Server 2025 instance.
  • Run the following query to check if the database is in legacy DW emulation mode:
  SELECT name, compatibility_level, is_dw_compat_mode
  FROM sys.databases
  WHERE name = 'YourDatabaseName';
  • If is_dw_compat_mode returns 1, this confirms the root cause.

Step 2: Disable the SQL DW Back Door Compatibility Flag

  • Run the following T-SQL command to disable legacy DW emulation for the affected database:
  ALTER DATABASE YourDatabaseName
  SET COMPATIBILITY_LEVEL = 170;
  • Then explicitly turn off the DW back door flag using the new Database Scoped Configuration option:
  ALTER DATABASE SCOPED CONFIGURATION SET DW_LEGACY_MODE = OFF;
  • This step must be run in single-user mode if the database was originally restored from a PDW backup file (.bak with DW headers).

Step 3: Re-run the ALTER DATABASE MODIFY Statement

  • Once legacy mode is disabled, retry the original command:
  ALTER DATABASE YourDatabaseName
  MODIFY FILEGROUP [PRIMARY]
  SET OVERFLOW = ON;
  • Ensure the SQL Server service account has write permissions on the underlying filegroup path (typically C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA).

Step 4: Restart the SQL Server Service

  • Open Services.msc on Windows Server 2025 / Windows 11.
  • Locate SQL Server (MSSQLSERVER), right-click, and select Restart.
  • Alternatively, use PowerShell:
  Restart-Service -Name 'MSSQLSERVER' -Force
  • This clears any cached compatibility flags held in the Resource Database.

Alternative or Verification Steps

  • Verify the fix by re-querying sys.databases to confirm is_dw_compat_mode = 0 and re-running the ALTER DATABASE ... SET OVERFLOW = ON statement without error.
  • If the error persists, drop and recreate the filegroup without the OVERFLOW clause first, then apply it in a separate statement after confirming the database is fully out of DW compatibility mode.