SQL Server CDC Capture Job Failure: Troubleshooting Change Processing Issues

Updated: 18 Apr, 2025 • 0 • min read

Table of Contents

SQL Server CDC Capture Job Failure

This article addresses a specific problem encountered in SQL Server Change Data Capture (CDC) where the capture job fails when processing changes for tables that include system Common Language Runtime (CLR) data types like geometry, geography, or hierarchyid. This issue arises under particular conditions involving Data Manipulation Language (DML) and Data Definition Language (DDL) operations performed on the affected tables. Understanding the symptoms, root cause, and workarounds is crucial for maintaining robust CDC implementations.

Symptoms¶

Consider a scenario where you have enabled Change Data Capture on a SQL Server table. This table is designed to store spatial or hierarchical data, utilizing system CLR data types such as geometry, geography, or hierarchyid. The CDC capture job, responsible for scanning the transaction log and recording changes, is actively processing modifications across various tables within the database. Crucially, at this stage, the capture job has not yet begun processing changes specifically for the table containing the system CLR data type.

Now, let’s introduce a sequence of operations on the table with the system CLR datatype:

Data Manipulation Language (DML) Changes: You perform data modifications on this table. These could include INSERT, UPDATE, or DELETE statements that alter the data within the table’s rows. These DML operations are duly logged in the SQL Server transaction log, awaiting capture by the CDC mechanism.
Data Definition Language (DDL) Changes: Subsequently, and on the same table, you execute Data Definition Language changes. A common example of such a change is adding a new column to the table using the ALTER TABLE ADD COLUMN command. DDL operations modify the structure of the database itself, and like DML operations, are also logged in the transaction log.

In this specific sequence of events – DML changes followed by DDL changes on a table with system CLR datatypes, while the CDC capture job is running but has not yet reached this table – the CDC capture job will encounter a failure when it eventually attempts to process changes for this particular table. The failure manifests with a series of error messages, indicating a problem in constructing replicated commands from the transaction log.

Error Messages¶

When the CDC capture job fails under these circumstances, SQL Server reports a series of error messages. These messages are crucial for diagnosing the problem and understanding the point of failure within the CDC process. The primary error messages you will typically encounter are as follows:

Msg 18805, Level 16, State 1, Procedure sp_replcmds, LineLineNumber[Batch Start Line LineNumber ]
The Log-Scan Process failed to construct a replicated command from log sequence number (LSN) {nnnnnnnn: nnnnnnnn: nnnn}. Back up the publication database and contact Customer Support Services.

This error message, Msg 18805, is a critical indicator of a failure in the log scanning process, which is a core component of CDC capture jobs. It specifically states that the “Log-Scan Process failed to construct a replicated command.” This means the process responsible for reading the transaction log and translating log records into commands that can be replicated or used for change data capture has encountered an issue. The message also points to a specific Log Sequence Number (LSN), which is a unique identifier for records in the transaction log. This LSN is helpful for Microsoft support in pinpointing the exact log record causing the problem if further investigation is needed. The recommendation to “Back up the publication database and contact Customer Support Services” highlights the severity of the error and suggests that manual intervention and potentially expert assistance may be required.

Msg 22859, Level 16, State 2, Procedure sp_replcmds, Line LineNumber [Batch Start Line LineNumber ]
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.

Msg 22859 reinforces the failure of the log scan process. It’s a more general error message that confirms that the log scanning has failed while “processing log records.” This error directs you to look at “previous errors in the current session,” which would include Msg 18805 and potentially others, to understand the root cause. It emphasizes the need to analyze the sequence of errors to diagnose and resolve the issue.

Msg 3621, Level 16, State 6, Procedure sp_replcmds, Line LineNumber [Batch Start Line LineNumber ]
The statement has been terminated.

Msg 3621 indicates that the statement, in this case, the execution of sp_replcmds (a stored procedure used internally by replication and CDC), has been terminated due to an error. This is a standard SQL Server error message that signals the abrupt stop of the current operation because of a preceding problem. It’s a consequence of the log scan failure and not the primary cause, but it confirms that the CDC capture job execution was halted.

Msg 22864, Level 16, State 1, Procedure sp_MScdc_capture_job, Line LineNumber[Batch Start Line LineNumber ]
The call to sp_MScdc_capture_job by the Capture Job for database 'DatabaseName' failed. Look at previous errors for the cause of the failure.

Msg 22864 is specific to Change Data Capture. It clearly states that “The call to sp_MScdc_capture_job by the Capture Job…failed.” sp_MScdc_capture_job is the stored procedure that drives the CDC capture process. This error message confirms that the overall CDC capture job has failed and points back to “previous errors” to find the root cause, again emphasizing the importance of analyzing the preceding error messages, particularly Msg 18805 and Msg 22859.

Error Log Entry¶

In addition to the error messages reported in the SQL Server Management Studio (SSMS) or job history, you might also find an entry in the SQL Server error log. This error log often contains more detailed information about system-level errors and can be crucial for in-depth troubleshooting. The error log entry relevant to this CDC capture job failure scenario is typically:

Error: 913, Severity: 16, State: 16.
Could not find database IDID. Database may not be activated yet or may be in transition. Reissue the query once the database is available. If you do not think this error is due to a database that is transitioning its state and this error continues to occur.

Error 913 “Could not find database IDID” is somewhat misleading in this context. While it suggests a problem with database availability, in this specific CDC issue, it’s often a secondary symptom rather than the primary cause. The database is likely available and active. The error arises because the log scan process, encountering difficulties with the log records related to the CLR datatype table after the DDL change, might enter a state where it’s unable to properly access or interpret database metadata, leading to this “database not found” error in the error log. It’s important to recognize that in this CDC capture job failure scenario, Error 913 is usually a consequence of the underlying log processing issue (Msg 18805 and Msg 22859) rather than a genuine database availability problem.

More Information¶

It is important to note a crucial detail that helps in understanding the nature of this issue. The problem described above does not occur if the CDC capture job initially processes only the DML changes made to the table with the system CLR datatype, and then, on a subsequent run, processes the DDL change.

This distinction highlights that the timing and order of operations are critical factors in triggering this CDC capture job failure. Specifically, the issue seems to arise when a DDL change (like adding a column) is made to a table containing system CLR datatypes after DML changes but before the CDC capture job has processed the log records for those DML changes and the DDL change in the same pass.

The underlying reason for this behavior likely lies in how SQL Server’s CDC mechanism and log scanning process handle DDL changes, especially in conjunction with system CLR datatypes. DDL changes, particularly those that alter the schema of a table, can introduce complexities in log record interpretation. When combined with the intricacies of system CLR datatypes and the timing of CDC processing, a race condition or incompatibility can occur. It is speculated that when the DDL change is encountered in the log before the DML changes related to the same table (in a single capture job run), the CDC process might not be correctly prepared to handle the schema change in the context of the preceding DML operations involving CLR datatypes.

If the CDC capture job processes the DML changes first, completes successfully, and then in a subsequent run encounters and processes the DDL change, the system appears to handle the situation correctly. This suggests that separating the processing of DML and DDL changes, especially for tables with system CLR datatypes, is a key factor in avoiding this failure.

Workaround¶

To mitigate and resolve this issue when you encounter a CDC capture job failure due to DML and DDL changes on tables with system CLR datatypes, you can employ the following workaround methods. These methods aim to ensure that DML and DDL changes are processed by the CDC capture job in a way that avoids the described failure.

Method 1: Restart the Capture Job After DDL Operations

The most straightforward and often effective workaround is to manually restart the CDC capture job after you have performed the DDL operation (e.g., adding a column) on the table with the system CLR datatype. The steps for this method are:

Perform DML Changes: Execute your Data Manipulation Language (INSERT, UPDATE, DELETE) statements on the table containing the system CLR datatype.
Perform DDL Change: Execute your Data Definition Language statement (e.g., ALTER TABLE ADD COLUMN) on the same table.
Stop the CDC Capture Job: Using SQL Server Management Studio (SSMS) or Transact-SQL commands, stop the CDC capture job associated with the database and the affected table. You can typically do this by right-clicking on the capture job in SQL Server Agent and selecting “Stop Job” or by using the sp_cdc_stop_job stored procedure.
Restart the CDC Capture Job: After stopping the job, immediately restart it. In SSMS, you can right-click the job and select “Start Job.” Alternatively, use the sp_cdc_start_job stored procedure.

By restarting the capture job after the DDL change, you ensure that the job starts fresh and processes the DDL change and any subsequent DML changes in a new capture cycle. This separation in processing seems to prevent the conflict that leads to the failure.

Method 2: Disable and Re-enable CDC on the Table

A more forceful workaround is to disable Change Data Capture on the affected table and then re-enable it. This method effectively resets the CDC tracking for the table and forces a fresh initialization after the DDL change. The steps are:

Perform DML Changes: Execute your Data Manipulation Language (INSERT, UPDATE, DELETE) statements on the table with the system CLR datatype.
Perform DDL Change: Execute your Data Definition Language statement (e.g., ALTER TABLE ADD COLUMN) on the same table.
Disable CDC on the Table: Use the sys.sp_cdc_disable_table stored procedure to disable CDC specifically for the table that experienced the DDL change. You will need to provide the source schema and source name of the table.
```
EXEC sys.sp_cdc_disable_table
@source_schema = N'your_schema',
@source_name   = N'your_table',
@capture_instance = N'your_capture_instance'; -- If using a specific capture instance name
```
If you are unsure of the capture instance name, you might be able to omit it if you are using the default capture instance. However, it’s best practice to specify it explicitly.
Re-enable CDC on the Table: After disabling, re-enable CDC on the same table using sys.sp_cdc_enable_table. You will need to provide the same parameters as when you initially enabled CDC, including the source schema, source name, role name, filegroup, and potentially captured column list and capture instance name if you used a specific one originally.
```
EXEC sys.sp_cdc_enable_table
@source_schema = N'your_schema',
@source_name   = N'your_table',
@role_name     = N'cdc_admin', -- Or your desired role name
@filegroup_name = N'PRIMARY', -- Or your desired filegroup
@capture_instance = N'your_capture_instance'; -- If you used a specific capture instance name before
```
Ensure you use the same configuration parameters when re-enabling CDC as you did during the initial setup.

By disabling and re-enabling CDC, you are essentially forcing a reset of the CDC metadata for the table. When you re-enable it, the CDC system will start capturing changes from the point of re-enablement, effectively bypassing any potential issues caused by the combination of DML and DDL changes processed in the wrong order.

Choosing a Workaround:

Method 1 (Restarting the capture job) is generally simpler and less disruptive. It’s often sufficient to resolve the issue in most cases. It is the recommended first approach.
Method 2 (Disabling and re-enabling CDC) is a more drastic measure. It should be used if restarting the capture job repeatedly fails to resolve the problem, or if you need a more definitive reset of the CDC tracking for the table. Be aware that disabling and re-enabling CDC might have implications for the sequence numbers and change data available to consumers of the CDC data, depending on your specific CDC implementation and retention policies.

Best Practices and Preventative Measures:

While these workarounds are effective for resolving the immediate issue, it’s also important to consider preventative measures to minimize the chances of encountering this problem in the future.

Schedule DDL Changes Carefully: When possible, try to schedule DDL changes on tables with system CLR datatypes during maintenance windows when CDC capture jobs can be temporarily stopped or are naturally less active. Avoid performing DDL changes concurrently with periods of heavy DML activity on these tables.
Process DDL Changes Separately: If you must perform DDL changes on tables with system CLR datatypes, consider a process where you:
1. Stop the CDC capture job.
2. Perform the DDL changes.
3. Restart the CDC capture job.
  This ensures that the DDL changes are processed in isolation by the capture job on its next run, reducing the likelihood of conflicts.
Monitor CDC Jobs: Regularly monitor your CDC capture jobs for errors and performance. Proactive monitoring can help you identify issues early and take corrective action before they escalate.

By understanding the symptoms, applying the appropriate workaround, and implementing preventative best practices, you can effectively manage and troubleshoot CDC capture job failures related to tables with system CLR datatypes and maintain the integrity of your change data capture processes.

If you have encountered this issue or have further insights or alternative workarounds, please feel free to share your experiences and comments below! Your contributions can help others in the community effectively manage their SQL Server CDC implementations.

Computacionvinchi