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

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

more ▼

asked Sep 03, 2012 at 12:06 PM in Default

avatar image

0 3 3 4

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

cdc.lsn_time_mapping will contain records also when there has been no activity. Use cdc.fn_cdc_get_net_changes_ 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)

more ▼

answered Sep 04, 2012 at 06:31 AM

avatar image

Magnus Ahlkvist
22.5k 20 44 43

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?


Sep 04, 2012 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, 2012 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, 2012 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, 2012 at 08:47 AM Magnus Ahlkvist
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Sep 03, 2012 at 12:06 PM

Seen: 3439 times

Last Updated: Aug 21, 2014 at 09:18 AM

Copyright 2018 Redgate Software. Privacy Policy