SOLVED – SQL Server CDC Logs Query Using “fn_dblog” Runs Forever

by

Recently we ran into an issue where SQL Server CDC logs query was not returning any results and was running forever. A few days prior to that we enabled CDC on the database level as well as table level (for all tables). Also, we set the polling interval in sys.sp_cdc_change_job as 1 day. Consequently, CDC log table was adding up and we were not even able to get the count. We were only able to get the top N rows.

We were using AWS DMS to migrate data from SQL Server to Aurora Postgres. I have a complete guide on this here.

So it was clear that deleting the records from the CDC log table will help us fix this issue. The @maxtrans and @maxscans parameter in sys.sp_cdc_change_job is responsible for how many records should be deleted (@maxtrans * @maxscans) every @pollinginterval seconds (in our case it is 1 day). You can run the following query to check what is configured in your system.

exec sys.sp_cdc_help_jobs;

Once we configure the sys.sp_cdc_change_job parameter we need to stop and start the job again. And only now we noticed that the job itself is getting stopped automatically. We found it when we try to stop the job. It threw an exception that there was no job running.

Solution

Fortunately, the solution was as simple as possible. Just disable the CDC at the database level using the following script. 

exec msdb.dbo.rds_cdc_disable_db 'database_name'

Later we enabled the CDC for the database and table that were required.

If this did not resolve your issue here are some useful resources.

Feel free to leave a comment down here. I would love to hear from you. Happy programming!!

Leave a Comment