title: Why does Change Data Capture (SQL Server) job auto stop after specific duration?
body: <p>I'm currently synchronizing data between Maria &lt;--> 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).</p>
  
  <p><strong>My Env</strong></p>
  
  <ul>
  <li>MSSQL <a href="https://hub.docker.com/r/microsoft/mssql-server-linux/" rel="nofollow noreferrer">Docker image</a></li>
  <li>Ubuntu (MacOS also), the CPU and RAM requirement was ensured both for device and Docker.</li>
  </ul>
  
  <p><strong>My problem:</strong></p>
  
  <p>The SQL Agent Job ran perfectly for ~10 minutes. After that, no changes were captured into <code>cdc.dbo_MyTrackedTable_CT</code>.</p>
  
  <p>I want this CDC job will run forever.</p>
  
  <p>I got this message:</p>
  
  <pre><code>Executed as user: 1b23b4b8a3ec\1b23b4b8a3ec$. 
  Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32). [SQLSTATE 42000] (Error 217)
  </code></pre>
  
  <p><strong>My inspection</strong></p>
  
  <pre><code>EXEC msdb.dbo.sp_help_job
      @job_name = N'cdc.MyDBName_capture',
      @job_aspect = N'ALL' ;
  </code></pre>
  
  <p>Return: <code>last_outcome_message</code> </p>
  
  <blockquote>
    <p>The job failed.  The Job was invoked by User sa.  The last step to run
    was step 2 (Change Data Capture Collection Agent).</p>
  </blockquote>
  
  <p>.</p>
  
  <p>Next, take a further inspection:</p>
  
  <pre><code>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'
  </code></pre>
  
  <p>Return:
  <a href="https://i.stack.imgur.com/H5ACV.png" rel="nofollow noreferrer">See this SQL result Image</a></p>
  
  <p><em>Sorry I don't have enough repu to embed img, so the link instead.</em></p>
  
  <p>As you can see the CDC job will start and run..and..retry for 10 times, after 10times, I cannot capture changes anymore.</p>
  
  <p>I need to start the job again by:</p>
  
  <pre><code>EXEC msdb.dbo.sp_start_job N'cdc.MyDbName_capture';
  </code></pre>
  
  <p>Then foreach ~1 minutes, the job retry --> till 10 --> job was stopped ¯_(ツ)_/¯</p>
  
  <hr>
  
  <h2><strong>So can you tell me why and how to fix it ??</strong></h2>
  
  <p>FYI, This is my job configuration:</p>
  
  <pre><code>-- https://docs.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
  ;
  </code></pre>
  
  <p>It's also not a trigger issue right? I feel dangerous when trying to turn trigger off, but no luck was made.</p>
  
  <pre><code>-- Turn off recursion trigger
  ALTER DATABASE MyDBName
  SET RECURSIVE_TRIGGERS OFF;
  </code></pre>