SQL Server Agent stops while enabling CDC for table

Question

I am using latest sqlserver (2019) on UBUNTU 18.04. I want to enable CDC for the database and table. Enabling CDC for database is done.

When i try to enable CDC for table, SQL Server Agent stops and gives the log 'SQLServerAgent is not currently running so it cannot be notified of this action.'

I checked the status of SQL Server Agent before enabling CDC. It says status 4, Running.

Then I tried to enable CDC for table. I get the following message in the console.

1> use production7;

2> go

Changed database context to 'production7'.

1> EXEC sys.sp_cdc_enable_table

2> @source_schema = N'prod7',

3> @source_name = N'inventory',

4> @role_name = NULL

5> go

SQLServerAgent is not currently running so it cannot be notified of this action.

I tried select sys.fn_cdc_get_max_lsn(); and it returns NULL — LakshanSS, Apr 18 '19 at 09:37
Agent maintains [its own error log](https://learn.microsoft.com/sql/ssms/agent/sql-server-agent-error-log), check that for problems. (I think the docs are exclusively about Windows, though; on Linux they're undoubtedly somewhere more exciting.) If Agent unexpectedly stops early in the process, the rest will fail with an error, but the ultimate cause would only be recorded in the Agent log. — Jeroen Mostert, Apr 18 '19 at 09:38
Thanks @JeroenMostert, I searched how to read logs in linux. It should be there in /var/opt/mssql/log/ But I have /var/opt/mssql. I dont have a directory called log. — LakshanSS, Apr 18 '19 at 10:25
I tried sql server on a windows machine and did the same thing. It works fine. — LakshanSS, Apr 19 '19 at 19:24

score 0 · Answer 1 · answered Jan 12 '23 at 09:42

The error appears because the SQL Server Agent service is not currently running. To fix this, you need to start SQL Server Agent Once done, the icon will turn green. To avoid such error in the future, I change the “Start Mode” of the service, to automatic. On the General page, check Auto restart SQL Server Agent if it stops unexpectedly.

SQL Server Agent stops while enabling CDC for table

1 Answers1