asked
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
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)
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
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.
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.
9 People are following this question.
