Capture Job
The capture job is initiated by running the parameterless stored procedure sp_MScdc_capture_job. This stored procedure starts by extracting the configured values for maxtrans, maxscans, continuous, and pollinginterval for the capture job from msdb.dbo.cdc_jobs. These configured values are then passed as parameters to the stored procedure sp_cdc_scan. This is used to invoke sp_replcmds to perform the log scan.
Capture Job Parameters
To understand capture job behavior, you must understand how the configurable parameters are used by sp_cdc_scan.
maxtrans Parameter
The maxtrans parameter specifies the maximum number of transactions that can be processed in a single scan cycle of the log. If, during the scan, the number of transactions to be proessed reaches this limit, no additional transactions are included in the current scan. After a scan cycle is complete, the number of transactions that were processed will always be less than or equal to maxtrans.
maxscans Parameter
The maxscans parameter specifies the maximum number of scan cycles that are attempted to drain the log before either returning (continuous = 0) or executing a waitfor (continuous = 1).
continous Parameter
The continuous parameter controls whether sp_cdc_scan relinquishes control in after either draining the log or executing the maximum number of scan cycles (one shot mode). It also controles whether sp_cdc_scan continues to run until explicitly stopped (continuous mode).
One Shot Mode
In one shot mode, the capture job requests sp_cdc_scan to perform up to maxtrans scans to try to drain the log and return. Any transactions in addition to maxtrans that are present in the log will be processed in later scans.
One shot mode is used in controlled tests, where the volume of transactions to be processed is known, and there are advantages to the fact that the job closes automatically on when it is finished. One shot mode is not recommended for production use. This is because t relies on the job schedule to manage how frequently the scan cycle is run.
When running in one shot mode, you can compute an upper bound on expected throughput of the capture job, expressed in transactions per second by using the following computation:
(maxtrans * maxscans) / number of seconds between scans
Even if the time that is required to scan the log and populate the change tables were not significantly different from 0, the average throughput of the job could not exceed the value obtained by dividing the maximum allowed transactions for a single scan multiplied by the maximum allowed scans by the number of seconds separating log processing.
If one shot mode were to be used to regulate log scanning, the number of seconds between log processing would have to be governed by the job schedule. When this kind of behavior is desired, running the capture job in continuous mode is a better way to manage rescheduling the log scan.
Continuous Mode and the Polling Interval
In continuous mode, the capture job requests that sp_cdc_scan run continuously. This lets the stored procedure manage its own wait loop by providing not only for maxtrans and maxscans but also a value for the number of seconds between log processing (the polling interval). Running in this mode, the capture job remains active, executing a WAITFOR between log scanning.
Note
When the value of the polling interval is greater than 0, the same upper limit on throughput for the recurring one shot job also applies to the job operation in continuous mode. That is, (maxtrans * maxscans) divided by a nonzero polling interval will put an upper bound on the average number of transactions that can be processed by the capture job.
Capture Job Customization
For the capture job, you can apply additional logic to determine whether a new scan begins immediately or whether a sleep is imposed before it starts a new scan instead of rely on a fixed polling interval. The choice could be based merely on time of the day, perhaps enforcing very long sleeps during peak activity times, and even moving to a polling interval of 0 at close of day when it is important to complete the days processing and prepare for nightly runs. Capture process progress could also be monitored to determine when all transactions committed by mid-night had been scanned and deposited in change tables. This lets the capture job end, to be restarted by a scheduled daily restart. By replacing the delivered job step calling sp_cdc_scan with a call to a user written wrapper for sp_cdc_scan, highly customized behavior can be obtained with little additional effort.