We are using Change Data Capture on SQL Server for a database to capture changes. We enable this using:
exec sys.sp_cdc_enable_db
A requirement for this to work and for us to pull back the changes recorded is that SQL Server Agent is running.
Over the last week or two I am receiving frequent reports that the changes are not appearing at the destination. On investigation I have found that SQL Server Agent has not been running and / or CDC is no longer enabled for the database.
I have explained that SQL Server Agent is set to restart automatically if it were to unexpectedly stop working. This suggests to me that the only reason for it to be turned off is that it has been manually turned off.
As for CDC on the database. I cannot see this 'property' of the database turning itself off. I can only imagine that this is being manually turned off, or overwritten.
I need to produce a list of reasons as to why a db admin would turn either of these off. I believe that there are genuine reasons that either of these need to be disabled in order to perform some form of administrative task. What reasons for turning these off could there be?
From the direction of Jeroen's comment, I investigated the logs.
I found that SQL Server Agent was being turned off on system shutdown. setting the service to start up automatically has resolved this.
The CDC Job was being disabled because of a bug in SQL Server 2017. Installing the latest cumulative updates seems to have fixed this.