CDC Subscriber Queue Cleanup

Kinetic ERP
1

Does anyone know what (if anything) cleans up the Subscriber queue table? I am working on a Subscriber using PULL that gets the latest entries in the CDC Subscriber queue and that works fine, the OffSet is update to the latest and life is good. But the subscriber queue table appears to not be cleared after I do the pull. Will this live there for ever? Seems awfully wasteful to keep all this data in the qeue forever.

Does it eventually get cleaned up? if so by who

CC: @Olga @Patrick.Ferrington

2

Isn’t there a SQL job that automatically gets created during Epicor installation?

The environment I just looked at has one and I don’t remember creating it myself…

1 Like
3

I thought there was a process to purge those- sorry this isn’t helpful Jose, but I swore when they were giving the insights presentation on this they had said there was a separate process.

4

I thought it was a SQL Agent Task called CapureLogCleanup.

if not exists (select * from cdc.Subscriber where Inactive = 0)
delete from cdc.CaptureLog
where DATEDIFF(dy, OccurredWhenUTC, getdate()) >= 2

5

Here is the code I found. Maybe I wrote it while sleepwalking but I think it’s out of the box epicor

if not exists (select * from cdc.Subscriber where Inactive = 0)
delete from cdc.CaptureLog
where DATEDIFF(dy, OccurredWhenUTC, getdate()) >= 2

6

Nice John

7

Lol, okay, I’m going to say it’s definitely out of the box Epicor now.

1 Like
8

Thanks @TomAlexander
That definitely cleans up the capture log.

However there doesn’t appear to be anything for the SubscriberQueue though now (a few hourws later) it appears to be clearing in some way. :thinking:

9

When you look at your subscriber definition you will see to TTL entries: TTL and Read TTL (not looking at UI but you get the idea). TTL is ‘Time To Live’. I do not remember off the top of my head what the Scale is (seconds, minutes etc…) I’m sure the help likely says if the UI doesn’t’.

Anyway when the CDC Log Processor processes a chunk of records to see if there are any notifications at the end of that it run the following query:

var count = CdcDb.Database.ExecuteSqlCommand(
                "DELETE Q " +
                "FROM cdc.SubscriberQueue Q " +
                "INNER JOIN cdc.Subscriber S " +
                "ON Q.SubscriberID = S.SubscriberID " +
                "WHERE (Q.Offset <= S.Offset AND Q.QueuedUTC < dateadd(second, -S.TTLRead, getutcdate())) " +
                "OR (Q.Offset > S.Offset AND Q.QueuedUTC < dateadd(second, -S.TTLUnread, getutcdate()))");

So that is is doing is saying delete any queued notifications that exceed the TTLUnread column as well as any retrieved queued notifications that have exceeded the TTLRead value. So to see record removed from that table the CDC Log Processor task needs to be running, and then the TTL thresholds need to be exceeded.

I should note that on our Cloud deployments we have noticed that this triggers every 6 seconds which is WAY WAY too often for our poor cloud SQL servers that host hundreds of customers. So we are in the future going to reduce this cleanup to be immediately on start of the CDC Log Processor then every 10 minutes after that. No reason to be spamming SQL server for this.

this is a well timed question BTW I never would have beat Olga to the answer if she wasn’t on vacation :slight_smile:

2 Likes
10

Thank you Patrick!!