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

by

Troubleshooting Guide: Error 5086 – Cannot Disable Vardecimal Storage Format

Why This Error Happens

This error occurs because SQL Server enforces a hierarchical dependency between database-level and table-level vardecimal storage settings. When the vardecimal storage format is enabled at the database level, individual tables can independently opt into this format, but SQL Server will not allow the database-level setting to be disabled while any table within it still has the format actively enabled. Essentially, the system is blocking the operation to prevent orphaned or inconsistent storage states between the database and its underlying tables.

Step-by-Step Resolution Guide

Step 1: Identify Affected Tables

  • Run a query against the system catalog views to find every table still using the vardecimal format.
  • Execute the following T-SQL command to pull the list of offending objects:
SELECT OBJECT_NAME(object_id) AS TableName 
FROM sys.tables 
WHERE uses_vardecimal_storage_format = 1;
  • Note down every table name returned, as these must be addressed before touching the database setting.

Step 2: Disable Vardecimal Format on Each Table

  • Use the system stored procedure sp_tableoption to turn off the setting for every table identified in Step 1.
  • Run the following command for each table (replace YourTableName accordingly):
EXEC sp_tableoption 'YourTableName', 'vardecimal storage format', 'OFF';
  • If you have a large number of tables, consider generating a dynamic SQL script using a WHILE loop or a cursor to automate this for all tables simultaneously.

Step 3: Disable the Database-Level Setting

  • Once all tables return 0 for uses_vardecimal_storage_format (verified by re-running the query from Step 1), proceed to disable the setting at the database level.
  • Execute the following procedure, replacing YourDatabaseName with your specific database:
EXEC sp_db_vardecimal_storage_format 'YourDatabaseName', 'OFF';
  • Ensure no active transactions are locking the tables during this process, as locks can cause the procedure to hang or time out.

Step 4: Restart Associated Services (If Required)

  • In rare cases involving Windows Server 2025 environments with high availability (AlwaysOn) configurations, changes may not propagate immediately to replicas.
  • Restart the SQL Server service via services.msc or use PowerShell:
Restart-Service -Name 'MSSQLSERVER'
  • Verify that the SQL Server Agent service is also running to ensure any scheduled maintenance jobs don’t conflict with the schema change.

Alternative or Verification Steps

  • Verification: Re-run the query SELECT * FROM sys.databases WHERE name = 'YourDatabaseName' and check the is_vardecimal_storage_format_enabled column to confirm it now reads False (0).
  • Alternative: If sp_tableoption fails due to replication constraints, you may need to temporarily disable replication or Change Data Capture (CDC) on the specific tables before retrying the toggle, then re-enable it once the format change is successful.