Blog Post: 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.

Minki van der Westhuizen Katharine Towne Malia Jones Jennifer ODell Jenny McCarthy