Relational Database Systems

Unable to enable CDC

Problem: Unable to enable CDC due to either of following errors even when database owner is SA.

Error Description #1

Msg 3930, Level 16, State 1, Procedure sp_cdc_enable_db_internal, Line 178

The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

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

Could not update the metadata that indicates database DPE_CustTicketDetail is enabled for Change Data Capture. The failure occurred when executing the command 'create user cdc'. The error returned was 916: 'The server principal "sa" is not able to access the database "DPEAudit" under the current security context.'. Use the action and error to determine the cause of the failure and resubmit the request.

Msg 266, Level 16, State 2, Procedure sp_cdc_enable_db_internal, Line 0

Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.

Msg 266, Level 16, State 2, Procedure sp_cdc_enable_db, Line 0

Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.

Msg 3998, Level 16, State 1, Line 1

Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.

Error Description #2

Msg 3930, Level 16, State 1, Procedure sp_cdc_enable_db_internal, Line 178

The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

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

Could not update the metadata that indicates database DPE_CustTicketDetail is enabled for Change Data Capture. The failure occurred when executing the command 'create user cdc'. The error returned was 208: 'Invalid object name 'DPEAudit.dbo.t_util_DatabaseChangeLog'.'. Use the action and error to determine the cause of the failure and resubmit the request.

Msg 266, Level 16, State 2, Procedure sp_cdc_enable_db_internal, Line 0

Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.

Msg 266, Level 16, State 2, Procedure sp_cdc_enable_db, Line 0

Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.

Msg 3998, Level 16, State 1, Line 1

Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.

Error Description #3
 

Msg 22832, Level 16, State 1, Procedure sp_cdc_enable_table_internal, Line 607

Could not update the metadata that indicates table [dbo].[Users] is enabled for Change Data Capture. The failure occurred when executing the command 'sp_cdc_create_change_table'. The error returned was 916: 'The server principal "S-1-9-3-727003095-1134527967-2886334085-1972679761." is not able to access the database "DPEAudit" under the current security context.'. Use the action and error to determine the cause of the failure and resubmit the request.

Msg 266, Level 16, State 2, Procedure sp_cdc_enable_table_internal, Line 0

Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.

Msg 3930, Level 16, State 1, Procedure sp_cdc_enable_table, Line 61

The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

Msg 266, Level 16, State 2, Procedure sp_cdc_enable_table, Line 0

Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.

Msg 3998, Level 16, State 1, Line 1

Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.

Root Cause: There is a trigger enabled on objects.

Troubleshooting Steps:

Step 1: Query sys.triggers to verify Trigger and get Trigger name.

Step2: Disable Trigger

DISABLE TRIGGER TrgDBChangeLog_TrackingDB ON DATABASE

Step3: Now run command to enable CDC (depending on error description*)

exec sys.sp_cdc_enable_db

or

exec sys.sp_cdc_enable_table

*Error Description #1 and #2 is for condition when enabling CDC on database. You may experience Error Description #3 while enabling CDC on tables.

However If your error condition is similar to following:

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

Could not update the metadata that indicates database DPE_CustTicketDetail  is enabled for Change Data Capture. The failure occurred when executing the command 'SetCDCTracked(Value = 1)'. The error returned was 15404: 'Could not obtain information about Windows NT group/user 'Domain\user', error code 0x5.'. Use the action and error to determine the cause of the failure and resubmit the request.

Msg 266, Level 16, State 2, Procedure sp_cdc_enable_db_internal, Line 0

Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 2.

Msg 266, Level 16, State 2, Procedure sp_cdc_enable_db, Line 0

Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 2.

Above error can be resolved by setting database owner to new login. for example:

USE <Database Name>

GO

EXEC sp_changedbowner 'sa'

For Detail refer article published at http://support.microsoft.com/en-us/kb/913423

Leave a Reply

Your email address will not be published. Required fields are marked *

To Top