2

I'm using Change Data Capture(CDC) to track DML changes that are being performed on my SQL Server Tables. However, the capture job is failing post a DDL alteration, with the following error:

Msg 3616, Level 16, State 2, An error was raised during trigger execution. The batch has been aborted and the user transaction, if any, has been rolled back. For more information, query the sys.dm_cdc_errors dynamic management view.

I have my own Alter_table trigger(that uses the eventdata() function) enabled on the same database that tracks the DDL command fired, along with the hostname of the user who fired the command.

I fired 2 DDL table alteration commands. The first DDL commmand(alter table add column) was successfully captured by both the triggers. However the second DDL commmand(alter table alter column) hasn't been captured in the cdc.ddl_history table. Why is this happening? My trigger has successfully logged both the commands.

PS. Querying the sys.dm_cdc_errors system view returns the following information:

1)An error was raised during trigger execution. The batch has been aborted and the user transaction, if any, has been rolled back.

2)Log scan process failed in processing a ddl log record. Refer to previous errors in the current session to identify the cause and correct any associated problems.

3)Log Scan process failed in processing log records. Refer to previous errors in the current session to identify the cause and correct any associated problems.

  • Does disabling and re-enabling CDC for the table fix the issue? – James Anderson Oct 27 '15 at 12:18
  • @James Nope, it doesn't. I have disabled the CDC trigger tr_MScdc_ddl_event that the system creates, for the time being, and it hasn't given me any problems as of yet. – Ritesh Bhakre Oct 28 '15 at 9:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.