apache kafka - 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.

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