8

So we've started exploring using change data capture on one of our production databases. We'd like to know the datetime of each change. Reading through walkthrough's and tutorials etc it seems that the standard approach is to use the LSN to relate to the cdc.lsn_time_mapping system table. This approach works but is not very straightforward nor performant when talking about 100's of thousands of changes a day.

In a test environment I made the following adjustment to the change track tables. I issued an ALTER TABLE statement to add a column to the end called [__ChangeDateTime] and made it's default value GetDate(). The approach seems to work, the change tracking still functions normally, the datetime's are being captured. But mucking around with system tables makes me a little nervous.

If this isn't a system field that Microsoft added from the beginning they must of had their reasons. Since they instead opted for the LSN to cdc.lsn_time_mapping approach am I setting myself up for problems by creating my own hack this way?

UPDATE:

Discovered during testing that GetDate() at times isn't precise enough for our needs - multiple changes sharing the same time. Recommend using sysdatetime() and datetime2 to move the value out to the nanosecond. Option for 2008+ only obviously.

5 Answers 5

8

Remember that CDC uses a log reader agent to populate the change table. Why is that important? By that mechanism, rows show up in the change tables asynchronously to the changes made in the base tables.

There are actually 3 different time points that can be recorded, in reverse chronological order:

  1. The time the change was delivered to the change table (which is what you're recording).
  2. The time the transaction that contained the change committed (using cdc.lsn_time_mapping).
  3. The time with which you manually populate a column in the base table (using a default constraint, a trigger, etc.).

So the first thing is to be clear on what you want to record. Usually we would care about either #2 or #3.

If the LSN mapping mechanism (#2) isn't performing well enough for you, the only supported alternative is to add a column to the base table and populate it yourself (#3).

With regards to changing the internal tables, as a matter of policy, I think it's best to avoid hacking around with internals when there are supported alternatives. The very last thing you want is an important production system going down, needing to call Product Support, and being denied service because of something like this. Never mind the issues of it potentially breaking things (upgrades), or being broken because it was unexpected (turn CDC off, then on again, as mentioned in the other answer).

3

A practical example:

USE Database;
GO

DECLARE @from_lsn binary(10), @to_lsn binary(10)
SET @from_lsn = sys.fn_cdc_get_min_lsn('schema_tablename')
SET @to_lsn = sys.fn_cdc_get_max_lsn()

SELECT
    sys.fn_cdc_map_lsn_to_time(__$start_lsn) AS 'Time'
    ,[Field1]
    ,[Field2]
    ,[Field3]
FROM [cdc].[fn_cdc_get_all_changes_schema_tablename]
  (@from_lsn, @to_lsn, N'all');
1
  • 1
    This answer would benefit from some comments explaining what you're doing, and why it is relevant.
    – Erik
    Commented Mar 10, 2016 at 0:56
2

The only caveat I would give is that those tables are dropped automatically when CDC is disabled. The column not be recreated automatically when you reneable it

0

Inspired by Gareth's answer, here's a shorter version. This doesn't use DECLARE which is great if you need to create a VIEW (DECLARE isn't allowed in VIEW)

SELECT
    sys.fn_cdc_map_lsn_to_time(__$start_lsn) AS 'Time'
    , *
FROM [cdc].[fn_cdc_get_all_changes_dbo_yourtablename
    (sys.fn_cdc_get_min_lsn('dbo_yourtablename')
    , sys.fn_cdc_get_max_lsn(), N'all')
0

Building off the previous answers, I created this script that will look at all CDC Tables and display the table name and last updated:

DECLARE @tableName varchar(max)
DECLARE @from_lsn binary(10)
DECLARE @to_lsn binary(10) = sys.fn_cdc_get_max_lsn()
DECLARE @results TABLE
(
    [Table] varchar(max),
    [LastUpdated] datetime
)

-- Get list of change tables
DECLARE cdc_tables_cursor CURSOR
FOR SELECT
        [capture_instance]
    FROM cdc.change_tables;

OPEN cdc_tables_cursor
FETCH NEXT FROM cdc_tables_cursor INTO @tableName;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Get Table Name / Last Updated
    SET @from_lsn = sys.fn_cdc_get_min_lsn(@tableName)
    DECLARE @sql nvarchar(max) = '
    SELECT
        top 1
        REPLACE(@tableName,''dbo_'','''') as [Table],
        sys.fn_cdc_map_lsn_to_time(__$start_lsn) AS ''Time''
    FROM [cdc].[fn_cdc_get_all_changes_'+@tableName+'](@from_lsn, @to_lsn, N''all update old'')
    order by 2 desc'

    -- Insert results of dynamic sql
    INSERT @results
    EXECUTE sp_executesql   
        @sql,  
        N'@tableName varchar(max), @from_lsn binary(10), @to_lsn binary(10)',  
        @tableName, @from_lsn, @to_lsn;

    FETCH NEXT FROM cdc_tables_cursor INTO @tableName
END

CLOSE cdc_tables_cursor;

DEALLOCATE cdc_tables_cursor;

-- List Tables/Last Updated
SELECT 
    * 
FROM @results
order by 1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.