Yesterday I received a call stating the CDC job had been stopped on a client’s server for the last TWO WEEKS, and of course the database log file filled up the entire drive. You might ask why our monitoring didn’t catch it, and that’s because it was a test environment. So none of this was critical, but I solved this similarly to how I would’ve on a production instance.
My first instinct on this server was to look for space wherever I could find it, since I know the impacted database log file filling up the drive wasn’t going to shrink. So I looked at some of the less critical databases for extra space… and there was nothing substantial enough on that drive.
After some thought, I landed on adding a temporary log file to a different drive for the impacted database using the following script:
ALTER DATABASE <DatabaseName> ADD LOG FILE
(NAME = N'<DatabaseName>_log_temp', FILENAME = N'E:\SQLLog\<DatabaseName>_log_temp.ldf',
SIZE = 20GB, FILEGROWTH = 4GB);Once I came up with the plan to write to another drive, I knew I had two more problems. One was making sure the CDC job would start, and the other was getting this to actually catch up. Even though it was only a test server, CDC was still two weeks behind.
The way I chose to increase capture throughput on this instance was to increase the maximum transactions in a batch, and reduce the polling interval. I did that using this script:
EXEC sys.sp_cdc_change_job @job_type = N'capture',
@maxtrans = 5000, @maxscans = 100, @pollinginterval = 0;The capture job reads the log in scan cycles. @maxtrans is the most transactions it will process in one cycle, @maxscans is the most cycles it will run before it pauses, and @pollinginterval is how many seconds that pause lasts. With the defaults, that’s up to 5,000 transactions (500 × 10) and then a 5 second wait. With these settings it’s up to 500,000 transactions (5,000 × 100), and a polling interval of 0 means there’s no wait between log scans at all.
The new settings don’t do anything until the job restarts. The sys.sp_cdc_change_job documentation says changes “don’t take effect until the job is stopped” and started again, because the job only reads its settings when it starts.
Then I restarted the job through the SQL Agent window in SSMS. Once the job was started, I worked with Claude to write a script that captured sample data of progress CDC was making to help me provide a reasonable timeline for how long it would take for this to catch up.
SET NOCOUNT ON;
DECLARE @sample_sec int = 300;
DECLARE @delay char(8) = CONVERT(char(8), DATEADD(SECOND, @sample_sec, 0), 108);
DECLARE @wall1 datetime2(3) = SYSDATETIME(),
@cap1 datetime2(3) = sys.fn_cdc_map_lsn_to_time(sys.fn_cdc_get_max_lsn());
WAITFOR DELAY @delay;
DECLARE @wall2 datetime2(3) = SYSDATETIME(),
@cap2 datetime2(3) = sys.fn_cdc_map_lsn_to_time(sys.fn_cdc_get_max_lsn());
DECLARE @wall_ms bigint = DATEDIFF_BIG(MILLISECOND, @wall1, @wall2),
@cap_ms bigint = DATEDIFF_BIG(MILLISECOND, @cap1, @cap2),
@lag_s bigint = DATEDIFF_BIG(SECOND, @cap2, @wall2);
DECLARE @rate decimal(18,3) = 1.0 * @cap_ms / NULLIF(@wall_ms, 0);
DECLARE @eta_s bigint = CASE WHEN @rate > 1 THEN CAST(@lag_s / (@rate - 1) AS bigint) END;
SELECT @cap2 AS last_captured_commit,
CONCAT(@lag_s / 86400, 'd ', CONVERT(char(8), DATEADD(SECOND, @lag_s % 86400, 0), 108)) AS current_lag,
@rate AS capture_rate_x,
CASE WHEN @eta_s IS NULL THEN 'Not catching up'
ELSE CONCAT(@eta_s / 86400, 'd ', CONVERT(char(8), DATEADD(SECOND, @eta_s % 86400, 0), 108)) END AS est_time_to_catch_up,
DATEADD(SECOND, @eta_s, @wall2) AS est_caught_up_at;The query basically takes two readings of where capture is, 300 seconds (5 minutes) apart, and compares how far capture moved against how much time passed. It uses that rate to determine the amount of time needed to process the rest of the backlog, then outputs that in a readable date time format. I didn’t get to officially benchmark the speed of the capture process before changing the settings of the job, but I was expecting the entire process to take around 15 hours if it was using the original settings. With the settings I configured, that was reduced to roughly half.
Once CDC was fully caught up, I ran a log backup of the impacted database, and shrank the log file back to its original size prior to the incident. Importantly, I also removed the temp file I created on a different drive using the following command:
ALTER DATABASE <DatabaseName> REMOVE FILE <DatabaseName>_log_temp;Finally, I stopped the CDC job and reverted the capture settings from the ones I set, back to their original, default settings using this command, and restarted the job:
EXEC sys.sp_cdc_change_job @job_type = N'capture',
@maxtrans = 500, @maxscans = 10, @pollinginterval = 5;I wondered why we wouldn’t just want to leave the settings as is. They’re clearly faster, and it’s not impossible that CDC falls behind for other reasons, such as poor disk performance. Microsoft’s page on administering CDC says (maxtrans * maxscans) divided by the polling interval is an upper bound on throughput. With the defaults that’s 500 × 10 / 5, or 1,000 transactions per second. With the polling interval at 0 there’s no wait between scans at all, so there’s no upper bound from that formula.
The job in this case fell behind because it was stopped, not because the defaults were too slow, and faster settings wouldn’t have done anything about that.
The cost of leaving the polling interval at 0 is that the job never waits. The CDC overview says the capture process opens and commits its own transaction on every scan cycle, and it suggests stopping the capture job during busy periods “to prevent log scanning from adding load.” With no polling interval, that log scanning runs back to back for as long as the job is up, whether or not there’s anything new to capture. I didn’t measure what that costs on this server, so I can’t say how much it matters, but it’s load the defaults don’t have.
So I left the defaults in place. The higher settings were useful for working through two weeks of backlog, and switching back afterward is just the same sp_cdc_change_job call and a restart of the job.