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

by

Troubleshooting Guide: Error 9112 – Incremental Statistics Not Supported

Why This Error Happens

Error 9112 occurs when a CREATE STATISTICS or CREATE INDEX statement specifies the INCREMENTAL = ON option against a database whose compatibility level is set below SQL Server 2014 (120), as incremental statistics require this minimum level to function. This typically happens after a database is restored or migrated from an older legacy instance without updating its compatibility settings, or when the target table is a type that does not support partitioned statistics (such as a memory-optimized table in certain configurations). Additionally, this can occur if the statistics are being created on a view or an unsupported indexed structure that inherently blocks incremental stat collection.

Step-by-Step Resolution Guide

Step 1: Verify the Current Database Compatibility Level

  • Open SQL Server Management Studio (SSMS) and connect to your Windows Server 2025 instance.
  • Run the following query to check the current level: SELECT name, compatibility_level FROM sys.databases WHERE name = 'YourDatabaseName';
  • Confirm if the result is lower than 120.

Step 2: Upgrade the Database Compatibility Level

  • Execute the ALTER DATABASE command to force the engine to support modern features.
  • Use the command: ALTER DATABASE YourDatabaseName SET COMPATIBILITY_LEVEL = 160;
  • Note: While SQL Server 2025 supports higher levels, ensure your application code is tested against this level before deploying to production to avoid query plan regressions.

Step 3: Validate the Target Object Type

  • Check if the statistics are being applied to a filtered index or a non-standard table type, as these may still throw errors even with correct compatibility levels.
  • Ensure the script does not target a COLUMNSTORE index, as incremental stats behave differently there.
  • Modify the script to remove the INCREMENTAL keyword if the table structure is confirmed to be unsupported (e.g., specific memory-optimized OLTP tables).

Step 4: Recreate the Statistics Object

  • Drop the existing broken statistics object to clear the cache: DROP STATISTICS YourTable.YourStatsName;
  • Re-run the creation script with the corrected syntax: CREATE STATISTICS YourStatsName ON YourTable (YourColumn) WITH INCREMENTAL = ON;

Alternative or Verification Steps

  • Verification: Query sys.stats and check the is_incremental column for your specific object to confirm the value is now set to 1.
  • Alternative: If upgrading the compatibility level is not immediately possible due to legacy application constraints, simply remove the WITH INCREMENTAL = ON clause from your T-SQL script to create standard, non-incremental statistics as a temporary workaround.