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?
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?
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
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.
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?
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.
18 People are following this question.
