[CDC] Error 22859, 622, 2601

When you enable SQL CDC (Change Data Capture) feature on DB and table. There are two job created in SQL Agent

• cdc.dbnamexxx_capture
• cdc.dbnamexxx_cleanup

The best practice is to create another SQL “FILE GROUP” and “FILE” then assign CDC tables to the new “FILE GROUP”. I’ve done it. But the job failed and showed the message below

  1: Msg 22859, Level 16, State 2, Log Scan process failed in processing log records. 
  2: Refer to previous errors in the current session to identify the cause and correct 
  3: any associated problems. For more information, query the sys.dm_cdc_errors dynamic management view.

and

  1: The Log-Scan Process failed to construct a replicated command from 
  2: log sequence number (LSN) {000015a9:0000395a:001b}. 
  3: Back up the publication database and contact Customer Support Services. 
  4: [SQLSTATE 42000] (Error 18805)  Log Scan process failed in processing log records. 
  5: Refer to previous errors in the current session to identify the cause and correct 
  6: any associated problems. [SQLSTATE 42000] (Error 22859)  
  7: The statement has been terminated. [SQLSTATE 42000] (Error 3621)  
  8: The statement has been terminated. [SQLSTATE 01000] (Error 3621)  
  9: The call to sp_MScdc_capture_job by the Capture Job for database 'xxxxxx' failed. 
 10: Look at previous errors for the cause of the failure. [SQLSTATE 42000] (Error 22864)

There are two system DMV you can query to see the errors rather than see the job logs.

• sys.dm_cdc_log_scan_sessions
• sys.dm_cdc_errors

1) Before I execute these 2 queries, I thought the 2nd error log was the solution key. So I’ve done something but they were wrong, DO NOT use the solution

• Change DB to FULL recovery mode, I though it needs to read log. and FULL backup and LOG backup. But it still failed, why?

When you enable CDC, SQL server doesn’t flush the log until after the transactions have been read by CDC, so CDC can work with any RECOVERY mode.

2) Then I query the DMV sys.dm_cdc_errors, it showed more details:

  1: The filegroup "SECONDARY" has no files assigned to it. Tables, indexes, 
  2: text columns, ntext columns, and image columns cannot be populated 
  3: on this filegroup until a file is added.

and

  1: Cannot insert duplicate key row in object 'cdc.dbo_xxxx_CT' with unique index 
  2: 'dbo_xxxx_CT_Index’. The duplicate key value is (2).

Is the “The filegroup "SECONDARY" “ the solution?  Because I found the CDC tables located at another filegroup but assigned to “SECONDARY” filegroup, so I follow the instruction below to adjust it

  1: USE [CDC Database]
  2: DBCC SHRINKFILE ('CDC File name',EMPTYFILE) – it located at the wrong filegroup
  3: ALTER DATABASE [CDC Database]  REMOVE FILE [CDC File name]

  1: Msg 1833, Level 16, State 2, Line 3
  2: File 'CDC File name' cannot be reused until after the next BACKUP LOG operation. 
  3: If the database is participating in an availability group, 
  4: a dropped file can be reused only after the truncation LSN of the primary 
  5: availability replica has passed the drop LSN of the file and a subsequent 
  6: BACKUP LOG operation has completed.

3) So, I backup the Log. then

  1: ALTER DATABASE [CDC Database] ADD FILE to FILEGROUP [SECONDARY]

The CDC job is still failed, so I have to do is compromise the CDC data lost and recreate CDC tables

• EXEC sys.sp_cdc_help_change_data_capture
• Disable CDC tables (CDC table will be dropped, but the original table wont be, so don’t worry)
• Re-enable CDC table and assign to the correct role and filegroup

FIXED!!

Below are some of queries to help you identify the issues

--01, Check the last database restore time
SELECT [rs].[destination_database_name],  [rs].[restore_date],  
[bs].[backup_start_date],  [bs].[backup_finish_date],  
[bs].[database_name] as [source_database_name],  
[bmf].[physical_device_name] as [backup_file_used_for_restore] 
FROM msdb..restorehistory rs 
INNER JOIN msdb..backupset bs 
ON [rs].[backup_set_id] = [bs].[backup_set_id] 
INNER JOIN msdb..backupmediafamily bmf  
ON [bs].[media_set_id] = [bmf].[media_set_id]  
ORDER BY [rs].[restore_date] DESC 

--02, check are there any Restore, backup running
SELECT session_id AS SPID, command, a.TEXT AS Query, 
start_time, percent_complete, 
dateadd(second, estimated_completion_time / 1000, getdate()) AS estimated_completion_time
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a
WHERE r.command IN (
        'BACKUP DATABASE',
        'RESTORE DATABASE'
        ,'BACKUP LOG'         )
or r.command like '%RESTORE%'

--03, Check whether DB’s CDC is enabled
USE master;
SELECT @@SERVERNAME, [name], [database_id], [is_cdc_enabled]  
FROM sys.databases;

--04, Check whether tables are CDC enabled for the DB
USE [Yoour DB name]
SELECT DB_NAME(), [name], [is_tracked_by_cdc]
FROM sys.tables;

--05, If you want to disable CDC table, 
--use the SP to find out the source instance
Use [CDC enabled DB]
EXEC sys.sp_cdc_help_change_data_capture

--06, find out some error info
SELECT * FROM sys.dm_cdc_log_scan_sessions 
SELECT * FROM sys.dm_cdc_errors