October 20, 2014 at 1:10 am
CDC capture job is failing with below error...any suggestions to fix it.
The Log-Scan Process failed to construct a replicated command from log sequence number (LSN) {000134d3:00004a0f:00d7}.
Back up the publication database and contact Customer Support Services.
[SQLSTATE 42000] (Error 18805) 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.
[SQLSTATE 42000] (Error 22859) The statement has been terminated.
[SQLSTATE 42000] (Error 3621) The statement has been terminated.
[SQLSTATE 01000] (Error 3621) The call to sp_MScdc_capture_job by the Capture Job for database 'ABC' failed.
Look at previous errors for the cause of the failure. [SQLSTATE 42000] (Error 22864).
NOTE: The step was retried the requested number of times (10) without succeeding. The step failed.
October 20, 2014 at 1:34 am
Hi,
Try to remove cdc job, backup log file and recreate cdc job. Check BOL to find t-sql command recreating the cdc job.
Jacek
October 20, 2014 at 3:07 am
It is worthy to check CDC status in a few areas.
Please use following command to make sure everything is configured:
EXECUTE sys.sp_cdc_help_change_data_capture;-- Returning change data capture configuration information for all tables
exec sys.sp_cdc_help_jobs-- Reports information about all change data capture cleanup or capture jobs in the current database.
-- Last sessions:
select top 100 *
from sys.dm_cdc_log_scan_sessions
order by sys.dm_cdc_log_scan_sessions.session_id desc--
-- is_cdc_enabled shows 1 when CDC is enabled for DB; if capturing jobs are inactive data is not collected still - check line below
select database_id, name ,is_cdc_enabled FROM sys.databases
-- Reports information about all change data capture cleanup or capture jobs in the current database.
exec sys.sp_cdc_help_jobs
If everything is Ok, remove CDC jobs and recreate using:
exec sys.sp_cdc_add_job @job_type = N'capture';-- adds capture job to collect data. The job should be added automaticaly after activating CDC
exec sys.sp_cdc_add_job
@job_type = N'cleanup'
,@start_job = 0
,@retention = 5760;-- Number of minutes that change data rows are to be retained in change tables. 4 days here.
Jacek
February 24, 2015 at 6:47 pm
This was some great code, Jack. Much of it is working well. However, the cdc_DBName_capture job is taking a heck of a long time. It needs about 18 hours of transactions to catch up but spot checking of tables is not yielding any values yet.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply