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

by

Why This Error Happens

Error 5085 occurs because SQL Server 2025 was launched with undocumented (hidden) trace flags—typically via startup parameters in SQL Server Configuration Manager or command-line switches—that explicitly block the engine from toggling database versioning states (such as ALLOWSNAPSHOTISOLATION or READCOMMITTEDSNAPSHOT). These trace flags are often enabled for internal diagnostics, replication scenarios, or legacy compatibility testing, but they override standard ALTER DATABASE behavior at a low level. Since the flags are undocumented, SQL Server refuses the ALTER DATABASE command outright rather than risk inconsistent versioning states in the version store.

Step-by-Step Resolution Guide

Step 1: Identify Active Trace Flags

  • Open a query window and run DBCC TRACESTATUS(-1); to list all globally active trace flags.
  • Cross-reference the output against known versioning-related flags (commonly in the 3xxx or 9xxx undocumented range used internally by Microsoft).
  • Check the SQL Server Error Log (via EXEC xp_readerrorlog 0, 1, 'Trace Flag';) to confirm which flags were applied at startup.

Step 2: Locate and Edit Startup Parameters

  • Open SQL Server Configuration Manager on Windows Server 2025 / Windows 11.
  • Navigate to SQL Server Services > right-click your instance (e.g., MSSQLSERVER) > Properties > Startup Parameters tab.
  • Identify any parameter formatted as -T#### where #### matches an undocumented flag number flagged in Step 1.
  • Remove the suspicious trace flag entry and click Add/Remove to apply the change.

Step 3: Restart the SQL Server Service

  • In Configuration Manager, right-click the instance and select Restart.
  • Alternatively, use PowerShell: Restart-Service -Name 'MSSQLSERVER' -Force.
  • Confirm the service restarts cleanly without rollback or recovery errors in the log.

Step 4: Re-run the ALTER DATABASE Command

  • Execute the original command again, for example:
    ALTER DATABASE [YourDB] SET ALLOW_SNAPSHOT_ISOLATION ON;
  • Confirm no Error 5085 is returned and the command completes successfully.

Step 5: Audit Startup Scripts and Registry for Persistence

  • Check HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<InstanceID>\MSSQLServer\Parameters in the Windows Registry to ensure the flag wasn’t re-injected via automation or a deployment script.
  • Remove any SQLArg entries referencing the removed trace flag to prevent recurrence on future reboots.

Alternative or Verification Steps

  • Run DBCC TRACESTATUS(-1); again post-restart to confirm the specific trace flag no longer appears in the active list.
  • If the flag is required for another business reason (e.g., vendor support), contact Microsoft Support to request documentation on flag compatibility with ALTER DATABASE versioning operations before re-enabling it.