ChristosP avatar image
ChristosP asked

CDC records unexpectedly deleted

By default, records captured by CDC are removed after 3 days, by the CDC Cleanup job. In our solution, we need to keep the captured records indefinitely, so we have disabled the Cleanup job. However, the captured records are still being deleted somehow and we need to find out what is deleting it, and how to stop it. The Cleanup Job is appearing disabled in the UI, and its history of executions is empty. Our code only reads from the CDC tables, and never deletes from them. We are experiencing this issue across multiple installations of SQL Server: - 2012 & 2014 editions - Enterprise & Developer editions Any ideas on what may be deleting the records, or any advice on how to investigate the issue?
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total. avatar image commented ·
What "cleanup" jobs do you have in msdb.dbo.cdc_jobs? Have you tied them back to the actual jobs in msdb.dbo.sysjobs and ensured they are all disabled?
1 Like 1 ·
ChristosP avatar image ChristosP commented ·
Hi Anthony, thank you for your response. There is only one cleanup job in msdb.dbo.cdc_jobs, and the corresponding job in msdb.dbo.sysjobs is disabled. Sorry for posting this as an answer, I tried to post a comment, but I am getting an authentication error in the JS console
0 Likes 0 · avatar image commented ·
Are there any jobs which have a job step to execute sp_MScdc_cleanup_job select * from msdb.dbo.sysjobsteps where command like '%sp_MScdc_cleanup_job%' Maybe you have a rouge job which is outside the cdc process. Other than that I would do a trace on the cdc tables to see what is going on.
0 Likes 0 ·
ChristosP avatar image ChristosP commented ·
The query returned only one result, that is part of the disabled job. I ran 2 traces, looking for any SQL command matching "%sys.sp_MScdc_cleanup_job%" and "%delete%cdc_table_name%" respectively. When I executed those commands manually, the trace detected them. When I ran it overnight, it got nothing. Any other ideas on what else the trace could look for?
0 Likes 0 · avatar image commented ·
I'm at a bit of a loss then, if the job is disabled and there's no other rouge job and the trace isn't picking up the deletions, something funny is going on. I would raise a ticket with Microsoft support to see if they can be of any better assistance.
0 Likes 0 ·
Show more comments

0 Answers


Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.