I'm currently synchronizing data between Maria <--> MSSQL. That is 2 way sync. I used SQL Server on Windows and everything works well until several days before... I switched all the test DB to Linux server, so MSSQL was run on a Docker container (the official image).
My Env
- MSSQL Docker image
- Ubuntu (MacOS also), the CPU and RAM requirement was ensured both for device and Docker.
My problem:
The SQL Agent Job ran perfectly for ~10 minutes. After that, no changes were captured into cdc.dbo_MyTrackedTable_CT.
I want this CDC job will run forever.
I got this message:
Executed as user: 1b23b4b8a3ec\1b23b4b8a3ec$.
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32). [SQLSTATE 42000] (Error 217)
My inspection
EXEC msdb.dbo.sp_help_job
@job_name = N'cdc.MyDBName_capture',
@job_aspect = N'ALL' ;
Return: last_outcome_message
The job failed. The Job was invoked by User sa. The last step to run was step 2 (Change Data Capture Collection Agent).
.
Next, take a further inspection:
SELECT
job.*, '|' as "1"
, activity.*, '|' as "2", history.*
, CASE
WHEN history.[run_status] = 0 THEN 'Failed'
WHEN history.[run_status] = 1 THEN 'Succeeded'
WHEN history.[run_status] = 2 THEN 'Retry (step only)'
WHEN history.[run_status] = 3 THEN 'Canceled'
WHEN history.[run_status] = 4 THEN 'In-progress message'
WHEN history.[run_status] = 5 THEN 'Unknown'
ELSE 'N/A' END as Run_Status
FROM msdb.dbo.sysjobs_view job
INNER JOIN msdb.dbo.sysjobactivity activity ON job.job_id = activity.job_id
INNER JOIN msdb.dbo.sysjobhistory history ON job.job_id = history.job_id
WHERE 1=1
AND job.name = 'cdc.MyDBName_capture'
AND history.run_date = '20180122'
Return: See this SQL result Image
Sorry I don't have enough repu to embed img, so the link instead.
As you can see the CDC job will start and run..and..retry for 10 times, after 10times, I cannot capture changes anymore.
I need to start the job again by:
EXEC msdb.dbo.sp_start_job N'cdc.MyDbName_capture';
Then foreach ~1 minutes, the job retry --> till 10 --> job was stopped ¯_(ツ)_/¯
So can you tell me why and how to fix it ??
FYI, This is my job configuration:
-- https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sys-sp-cdc-add-job-transact-sql
EXECUTE sys.sp_cdc_change_job
@job_type = N'capture',
@maxscans = 1,
@maxtrans = 500,
@continuous = true,
@pollinginterval = 1
;
It's also not a trigger issue right? I feel dangerous when trying to turn trigger off, but no luck was made.
-- Turn off recursion trigger
ALTER DATABASE MyDBName
SET RECURSIVE_TRIGGERS OFF;
