Debezium: No maximum LSN recorded in the database; please ensure that the SQL Server Agent is running

Question

This question is related to: Debezium How do I correctly register the SqlServer connector with Kafka Connect - connection refused

In Windows 10, I have Debezium running on an instance of Microsoft SQL Server that is outside of a Docker container. I am getting the following warning every 390 milliseconds:

No maximum LSN recorded in the database; please ensure that the SQL Server Agent is running
[io.debezium.connector.sqlserver.SqlServerStreamingChangeEventSource]

I checked Debezium's code on Github and the only place that I can find this warning states in the code comments that this warning should only be thrown if the Agent is not running. I have confirmed that the SQL Server Agent is running.

Why is this warning showing up and how do I fix it?

Note:

My current solution appears to only work in a non-production environment - per Docker's documentation.

Douceur

Answer 1

LSN is the "pieces" of information related about your SQL Server changes. If you don't have LSN, is possible that your CDC is not running or not configured properly. Debezium consumes LSNs to replicate so, your SQL Server need to generate this.

Some approaches:

1. Did you checked if your table are with CDC enabled? This will list your tables with CDC enabled:

SELECT s.name AS Schema_Name, tb.name AS Table_Name
, tb.object_id, tb.type, tb.type_desc, tb.is_tracked_by_cdc
FROM sys.tables tb
INNER JOIN sys.schemas s on s.schema_id = tb.schema_id
WHERE tb.is_tracked_by_cdc = 1

2. Your CDC database are enabled and runnig? (see here)

Check if enabled:

SELECT * 
FROM sys.change_tracking_databases 
WHERE database_id=DB_ID('MyDatabase')

And check if is running:

EXECUTE sys.sp_cdc_enable_db;  
GO  

3. Your CDC service are running on SQL Server? See in docs

EXEC sys.sp_cdc_start_job;  
GO  

4. On enabling table in CDC, I had some issues with rolename. For my case, configuring at null solved my problem (more details here)

    EXEC sys.sp_cdc_enable_table
        @source_schema=N'dbo',
        @source_name=N'AD6010',
        @capture_instance=N'ZZZZ_AD6010',
        @role_name = NULL,
        @filegroup_name=N'CDC_DATA',
        @supports_net_changes=1
     GO

Mainstream

sys.change_tracking_databases is for Change Tracking which is not the same as Change Data Capture. Change Tracking is also not used by Debezium AFAIK. The rest of this answer does still seem like good things to check when seeing this warning. – Meritocracy

Answer 2

Adding more to William's answer.

For the case SQL Server Agent is not running

You can enable it by following :

1. Control panel >
2. Administrative Tools >
3. Click "Services"
4. Look for SQL Server Agent
5. Right click and Start

Now you can fire cdc job queries in your mssql.

PS: you need to have login access to windows server.

Gery

Answer 3

Another possibility of this error (I just ran into this warning myself this morning trying to bring a new DB online) is the SQL login does not have the permissions needed. Debezium runs the following SQL. Check that the SQL login you are using has access to run this stored procedure and it returns the tables you have set up in CDC. If you get an error or zero rows returned, work with your DBA to get the appropriate permissions set up.

EXEC sys.sp_cdc_help_change_data_capture

Meritocracy

Answer 4

This will also happen if all CDC tables are empty (i.e. there has been no change recorded in the database since the last CDC entry expired).

That's kind of annoying in developpement environnement as it happens more often that a DB isn't being used for an extended period of time.

Shontashoo