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

by

Troubleshooting Guide: Error 12847 – Change Event Streaming Not Supported in Contained Databases

Why This Error Happens

This error occurs because Change Event Streaming (CES), a feature introduced in SQL Server 2025, relies on internal system objects and metadata structures that are incompatible with the database containment model. Contained databases isolate user and login information within the database boundary, but CES requires instance-level catalog coordination to publish change events, which the contained database boundary blocks. SQL Server therefore prevents the ALTER DATABASE ... SET CONTAINMENT = PARTIAL (or FULL) operation from succeeding whenever Change Event Streaming is already enabled on that database.

Step-by-Step Resolution Guide

Step 1: Verify Current Change Event Streaming Status

  • Open SQL Server Management Studio (SSMS) or Azure Data Studio connected to your Windows Server 2025 / Windows 11 instance.
  • Run the following query to check if CES is active on the target database:
  SELECT name, is_change_event_streaming_enabled 
  FROM sys.databases 
  WHERE name = 'YourDatabaseName';
  • If the value returns 1, this confirms CES is the blocking factor for containment conversion.

Step 2: Disable Change Event Streaming Before Conversion

  • Run the disable command on the target database:
  ALTER DATABASE [YourDatabaseName] 
  SET CHANGE_EVENT_STREAMING = OFF;
  • Confirm no active event stream consumers (e.g., Event Hub or Kafka connectors) are referencing this database before disabling, as this will immediately halt data flow.

Step 3: Attempt the Containment Alteration Again

  • Once CES is disabled, retry the containment change:
  ALTER DATABASE [YourDatabaseName] 
  SET CONTAINMENT = PARTIAL;
  • Check the SQL Server error log located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG to confirm the operation completed without raising Error 12847 again.

Step 4: Re-architect Your CES Workflow (If Required)

  • If your workload requires both containment and streaming, redesign the pipeline to use Change Data Capture (CDC) or external Change Tracking at the instance level instead of database-level CES.
  • Alternatively, keep the database non-contained and manage login portability manually using sp_migrate_user_to_contained for specific users.

Alternative or Verification Steps

  • Run SELECT containment, containment_desc FROM sys.databases WHERE name = 'YourDatabaseName'; to confirm the containment value successfully changed to 1 (PARTIAL).
  • If reverting is necessary, re-enable CES only on non-contained databases by setting CONTAINMENT = NONE first, then run ALTER DATABASE SET CHANGE_EVENT_STREAMING = ON.