Enable CDC fails with error Msg 22830, Level 16, State 1, Procedure sp_cdc_enable_db_internal, Line 186


Enable CDC fails with error Msg 22830, Level 16, State 1, Procedure sp_cdc_enable_db_internal, Line 186

Chris Skorlinski
SQL Server Escalation Services

Problem

While enabling Change Data Capture (CDC) on database you get following error:

EXECUTE sys.sp_cdc_enable_db

Msg 22830, Level 16, State 1, Procedure sp_cdc_enable_db_internal, Line 186

Could not update the metadata that indicates database CustomerDb is enabled for Change Data Capture. 
The failure occurred when executing the command 'CREATE TABLE dbo.systranschemas'.
The error returned was 8152: 'String or binary data would be truncated.'.
Use the action and error to determine the cause of the failure and resubmit the request.
Solution

This error occurs when CDC is unable to create an internal table.  Most likely cause is a DDL trigger or security rights are preventing table creation.  To confirm you can run SQL Profiler or use SSMS tool and expand Server Objects, then Triggers to check for DDL triggers.

image


Comments (3)

  1. Nirmal Shah says:

    This error seems to be comming as a result of different problem source. In my case, I was having database which was having some db owner issue. After changing db owner to "SA" it just worked fine. BTW, I did have audit trigger but it didn't give me any trouble even if audit trigger(DDL trigger) was enabled. I hope this helps.

    Thanks

    Nirmal

  2. Marko says:

    Nirmal, your soultion worked in my case. Thank you.

  3. Srisubathra says:

    Yes same worked for me , thanks Nirmal