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

by

Troubleshooting Guide: Error 5091 – ALTER DATABASE Change Tracking Option Specified More Than Once

Why This Error Happens

Error 5091 occurs when a single ALTER DATABASE statement enabling or modifying change tracking includes the same option — typically CHANGE_RETENTION or AUTO_CLEANUP — more than once within the WITH clause. This is usually caused by malformed or auto-generated T-SQL scripts, often produced by third-party migration tools, ORM frameworks, or copy-paste errors when combining multiple configuration templates. SQL Server’s parser strictly validates that each change tracking parameter appears only once per statement, and it will reject the entire batch rather than silently merging or overriding duplicate values.

Step-by-Step Resolution Guide

Step 1: Locate the Offending T-SQL Script

  • Identify the exact ALTER DATABASE [YourDB] SET CHANGE_TRACKING = ON (...) statement that triggered Error 5091.
  • Check SQL Server Management Studio (SSMS) query history, deployment scripts, or SSDT/DACPAC publish logs for the exact line number.
  • If the error came from an automated job, review the SQL Server Agent job step or the CI/CD pipeline script (e.g., Azure DevOps, Flyway, Liquibase).

Step 2: Review and Clean the Syntax

  • Open the script in SSMS or Azure Data Studio and inspect the WITH clause for duplicate parameters.
  • Example of the incorrect syntax causing the error:
  ALTER DATABASE MyDB
  SET CHANGE_TRACKING = ON
  (CHANGE_RETENTION = 3 DAYS, AUTO_CLEANUP = ON, CHANGE_RETENTION = 7 DAYS);
  • Correct the statement by removing the duplicate option, keeping only one instance of each parameter:
  ALTER DATABASE MyDB
  SET CHANGE_TRACKING = ON
  (CHANGE_RETENTION = 7 DAYS, AUTO_CLEANUP = ON);

Step 3: Re-run the Corrected Statement

  • Execute the corrected ALTER DATABASE command in SSMS or via sqlcmd on Windows Server 2025 / Windows 11.
  • Confirm the command completes with “Command(s) completed successfully” and no errors are returned.
  • If running through a deployment pipeline, update the source script file (e.g., .sql migration file) to prevent recurrence on future deployments.

Step 4: Check for Duplicate Entries in Generated Scripts

  • If the script was auto-generated (e.g., by SSDT, DbForge, or a schema comparison tool), regenerate the script after fixing the source schema definition to avoid reintroducing duplicates.
  • Search the entire script file for repeated CHANGE_RETENTION or AUTO_CLEANUP keywords using a text editor’s find-all function (Ctrl+F) before re-deploying.

Alternative or Verification Steps

  • Verify the fix by querying sys.change_tracking_databases to confirm the correct retention period and auto-cleanup setting were applied:
  SELECT database_id, is_auto_cleanup_on, retention_period, retention_period_units_desc
  FROM sys.change_tracking_databases;
  • If the error persists despite a clean script, restart the SSMS query session or SQL Server client cache, as a stale cached execution plan from a prior failed batch can sometimes resubmit duplicate parameters.