0

For reasons that I am not able to change, there is a company wide policy preventing SQL Agent Jobs on the SQL Servers. We have an application that uses CDC and we would like to use this functionality.

The DBAs suggested using an enterprise scheduling app that is approved for use to schedule the jobs. This seems pretty straightforward, as it seems that there are really only 3 steps involved in getting CDC to run. The only other variable is that the service account that schedules the jobs does not have dbo or sys admin access to our sql servers. The workaround for this is that I execute the CDC steps using execute as OWNER. What I am experiencing is that the procedure sys.sp_mscdc_capture_job just seems to run and never completes.

I have checked the sys.dm_cdc_log_scan_sessions view and I see 2 rows-

session_id start_time end_time duration scan_phase error_count start_lsn current_lsn end_lsn tran_count last_commit_lsn last_commit_time log_record_count schema_change_count command_count first_begin_cdc_lsn last_commit_cdc_lsn last_commit_cdc_time latency empty_scan_count failed_sessions_count

0 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000 0 Aggregate 0 00000000:00000000:0000 00000000:00000000:0000 00000000:00000000:0000 0 00000000:00000000:0000 1900-01-01 00:00:00.000 0 0 0 00000000:00000000:0000 00000000:00000000:0000 1900-01-01 00:00:00.000 0 0 0

1 2017-06-06 18:22:27.660 2017-06-07 13:45:50.943 2 Done 1 00000000:00000000:0000 00000000:00000000:0000 00000000:00000000:0000 0 00000000:00000000:0000 1900-01-01 00:00:00.000 0 0 0 00000000:00000000:0000 00000000:00000000:0000 1900-01-01 00:00:00.000 0 2443 0 - List item

I dont see any data in the CDC tables. Is there something I am missing here? this is the capture job sql:

    create procedure CDCWrappedExecute with Execute as OWNER
    as 
    RAISERROR(22801, 10, -1)
    exec sys.sp_MScdc_capture_job
CraigAlbright
  • 71
  • 4
  • ok, It looks like it is populating the cdc tables but the performance is atrocious. Running for 30-45 minutes populated ~1000 rows – CraigAlbright Jun 07 '17 at 19:05
  • It's almost like SQL Server was designed to use it's own functionality. :) – Jacob H Jun 07 '17 at 19:09
  • Thats a fair comment. And again, the decision to use an external scheduler is not mine. Im just trying to find out if this is a workable solution. – CraigAlbright Jun 07 '17 at 19:20
  • Sorry for the snarky comment, I feel your pain, being directed to use a specific method because *reasons*... Maybe your findings that the workaround is not viable will help change the company policy. Good luck! – Jacob H Jun 07 '17 at 19:23

0 Answers0