Why is Server writing to cdc.lsn_time_mapping every 5 minutes?

Question

I have a simple database, with cdc enabled for the database and all tables. I have made 1 insert in 1 table. Simple as simple can.

Using SELECT sys.fn_cdc_map_lsn_to_time(sys.fn_cdc_get_max_lsn()) however, I always end up with a much more recent date then when I did the insert. Researching, I found sys.fn_cdc_get_max_lsn() gets the max lsn value from the table cdc.lsn_time_mapping. Looking at that table I find many more entries then my one single insert. In fact, there is an entry every 5 minutes since cdc was enabled...

Furthermore, all entries (besides my own insert entry) have a tran_id of 0x00 and a tran_begin_lsn of 0x00000000000000000000.

example (headers edited for readability, sorry if they're in the wrong place for you):

 start_lsn               tran_begin_time            tran_end_time   tran_id   tran_begin_lsn
0x00000153000001900001 2012-09-02 04:28:05.620 2012-09-02 04:28:05.620 0x00 0x00000000000000000000
0x00000153000001CD0001 2012-09-02 04:33:06.450 2012-09-02 04:33:06.450 0x00 0x00000000000000000000
0x000001530000020A0001 2012-09-02 04:38:07.273 2012-09-02 04:38:07.273 0x00 0x00000000000000000000
0x00000153000002470001 2012-09-02 04:43:08.157 2012-09-02 04:43:08.157 0x00 0x00000000000000000000
0x00000153000002760001 2012-09-02 12:35:20.253 2012-09-02 12:35:20.253 0x00 0x00000000000000000000
0x00000153000002B30001 2012-09-02 12:40:22.650 2012-09-02 12:40:22.650 0x00 0x00000000000000000000
0x00000153000002F00001 2012-09-02 12:45:24.993 2012-09-02 12:45:24.993 0x00 0x00000000000000000000
0x000001530000032D0001 2012-09-02 12:50:26.647 2012-09-02 12:50:26.647 0x00 0x00000000000000000000
0x000001530000036A0001 2012-09-02 12:55:27.640 2012-09-02 12:55:27.640 0x00 0x00000000000000000000
0x00000153000003A70001 2012-09-02 13:00:28.590 2012-09-02 13:00:28.590 0x00 0x00000000000000000000
0x00000153000003E40001 2012-09-02 13:05:29.507 2012-09-02 13:05:29.507 0x00 0x00000000000000000000
0x00000153000004210001 2012-09-02 13:10:30.400 2012-09-02 13:10:30.400 0x00 0x00000000000000000000
0x000001530000045E0001 2012-09-02 13:15:31.240 2012-09-02 13:15:31.240 0x00 0x00000000000000000000

Since start_lsn for these entries is not null, I always get the last change as if something happened in the last 5 minutes, while the actual real change happened 3 days ago.

These "empty" entries also do not correspond to any changes in the tables.

Like I said, its a very simple database, so no running procedures or anything that do something every 5 minutes.

Im running on SQL Server 2008R2, though the same issues appears to exist in SS2012.

Anybody got a clue why this is happening? And how I can stop it from happening?

Cheers Wouter

Answer 1

0

cdc.lsn_time_mapping will contain records also when there has been no activity. Use cdc.fn_cdc_get_netchanges together with sys.fn_cdc_map_time_to_lsn to get the changes for a given time period.

See BOL: http://technet.microsoft.com/en-us/library/bb522511(v=sql.105)

answered Sep 04 '12 at 06:31 AM

Magnus Ahlkvist
13.7k ● 13 ● 17 ● 30

Magnus, thanks for your reply, your link doesnt seem to be working though. Also, getting the changes over a certain time period is not the problem, retrieving the date/time of the last change is. Since the function sys.fn_cdc_get_max_lsn() is specifically designed to get the lsn of the last change, it seems odd that it returns an lsn number which is not related to any change.

What is the point of the records when there has been no activity?

Wouter

Sep 04 '12 at 07:55 AM wouterp

The link is messed up by the editor. But if you copy the link text it will work.

I don't know how the SQL Server team discussed when they designed it this way, but the BOL documentation for cdc.lsn_time_mapping says it contains records for non activity as well.

Sep 04 '12 at 08:01 AM Magnus Ahlkvist

Seems a weird choice, to design something in such a way it generates meaningless records (and quite a lot too, every 5 minutes is fairly significant) and the function doesnt work, or am i missing something?

Sep 04 '12 at 08:13 AM wouterp

BOL says: "Entries may also be logged for which there are no change tables entries. This allows the table to record the completion of LSN processing in periods of low or no change activity."

Someone more into the bits and bytes of the SQL Server engine might be able to explain in more detail what it means.

Sep 04 '12 at 08:47 AM Magnus Ahlkvist

(comments are locked)

Why is Server writing to cdc.lsn_time_mapping every 5 minutes?

Follow this question

Related Questions