Troubleshooting Guide: SQL Server Error 3764 (Change Data Capture Conflict)

Why This Error Happens

Error 3764 occurs when an attempt is made to drop, alter, or disable an object (typically a table, schema, or database) that still has Change Data Capture (CDC) enabled on it. SQL Server protects the integrity of CDC-tracked objects by preventing structural changes or removal until CDC is explicitly disabled. This commonly happens during schema migrations, table drops, or database downgrade scripts where CDC cleanup was skipped.

Step-by-Step Resolution Guide

Step 1: Identify Which Tables Have CDC Enabled

• Run the following query to list all CDC-enabled tables in the affected database:

  SELECT s.name AS SchemaName, t.name AS TableName, t.is_tracked_by_cdc
  FROM sys.tables t
  JOIN sys.schemas s ON t.schema_id = s.schema_id
  WHERE t.is_tracked_by_cdc = 1;

• Note the exact schema and table name referenced in the error message (%.*ls).

Step 2: Disable CDC on the Specific Table

• Execute the stored procedure to disable capture instances for the table:

  EXEC sys.sp_cdc_disable_table
      @source_schema = N'dbo',
      @source_name   = N'YourTableName',
      @capture_instance = N'all';

• Repeat this for every table flagged in Step 1 that is blocking your DROP/ALTER operation.

Step 3: Disable CDC at the Database Level (If Required)

• If you are attempting to drop the entire database or remove CDC completely, disable it at the database scope:

  USE YourDatabaseName;
  GO
  EXEC sys.sp_cdc_disable_db;

• Verify that the CDC jobs (capture and cleanup) are removed from SQL Server Agent via SSMS under SQL Server Agent > Jobs.

Step 4: Retry the Original Operation

• Re-run the original DROP TABLE, ALTER TABLE, or DROP DATABASE command that triggered Error 3764.
• Confirm the operation completes without the CDC-related block.

Step 5: Re-enable CDC (If Still Needed)

• If CDC tracking is still required for business logic (e.g., ETL pipelines), re-enable it only after structural changes are finalized:

  EXEC sys.sp_cdc_enable_table
      @source_schema = N'dbo',
      @source_name   = N'YourTableName',
      @role_name     = NULL;

Alternative or Verification Steps

• Verify the fix by querying sys.tables again to confirm is_tracked_by_cdc = 0 for the target table, and check cdc.change_tables to ensure no orphaned capture instances remain.
• If disabling CDC via T-SQL fails due to permission errors, ensure the executing account is a member of the db_owner role or has been granted sysadmin rights, as CDC management requires elevated database-level permissions.