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

by

Troubleshooting Guide: SQL Server Error 5089

Why This Error Happens

Error 5089 occurs when an attempt is made to modify change tracking settings (such as altering the retention period or auto-cleanup configuration) on a database where change tracking has not been enabled at the database level. SQL Server requires change tracking to be explicitly turned on via ALTER DATABASE ... SET CHANGE_TRACKING = ON before any table-level or settings-level modifications can be applied. This typically happens when a script or application assumes change tracking is already active, or when it was previously disabled (intentionally or due to a failover/restore operation) without updating dependent scripts.

Step-by-Step Resolution Guide

Step 1: Verify Current Change Tracking Status

  • Open SQL Server Management Studio (SSMS) and connect to the target instance on Windows Server 2025.
  • Run the following query to check the database state:
SELECT DB_NAME(database_id) AS DatabaseName, is_auto_cleanup_on, retention_period, retention_period_units_desc
FROM sys.change_tracking_databases;
  • If the target database does not appear in the result set, change tracking is confirmed to be disabled.

Step 2: Enable Change Tracking at the Database Level

  • Execute the following T-SQL command, replacing YourDatabaseName with the actual database name:
ALTER DATABASE YourDatabaseName
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);
  • Ensure you have ALTER DATABASE permissions or are a member of the sysadmin or dbcreator fixed server role.
  • Confirm there are no active transactions blocking the schema lock by checking sys.dm_tran_locks.

Step 3: Re-Apply the Original Change Tracking Settings

  • Once the database-level setting is active, re-run the original command that caused the error (e.g., updating retention period or enabling tracking on a specific table).
  • Example for enabling tracking on a table:
ALTER TABLE dbo.YourTableName
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON);
  • If working in a high-availability setup (Always On), ensure the change is synchronized across all replicas.

Step 4: Restart the Dependent Service or Application

  • If the error originated from an application (e.g., a sync framework or ETL tool like SSIS), restart the Windows Service or IIS Application Pool associated with it to clear any cached connection states.
  • Use services.msc on Windows Server 2025 to restart the “SQL Server Agent” service if scheduled jobs reference change tracking.

Alternative or Verification Steps

  • Verify the fix by re-running the query from Step 1; the database should now appear in sys.change_tracking_databases with the correct retention values.
  • If the error persists after enabling change tracking, check for database compatibility level issues using SELECT compatibility_level FROM sys.databases WHERE name = 'YourDatabaseName'; and ensure it is set to 130 or higher for full change tracking support in SQL Server 2025.