locked
Data removed automatically from Change Data Capture configured table.. How?? RRS feed

  • Question

  • Hi,

    I have configure Change data capture on couple of tables to keep track of changes done on table.

    After 2 days when I queried those captured tables, no data is there.

    Does sql server 2008 removed data from those tables automatically at some interval?

    Please suggest the needful.


    Vidhyut

    • Moved by Mr. Wharty Monday, May 21, 2012 6:12 AM Not a Training and Certification question (From:Training and Certification)
    Monday, May 21, 2012 6:01 AM

Answers

  • http://msdn.microsoft.com/en-us/library/cc645937.aspx

    Data that is deposited in change tables will grow unmanageably if you do not periodically and systematically prune the data. The change data capture cleanup process is responsible for enforcing the retention-based cleanup policy. First, it moves the low endpoint of the validity interval to satisfy the time restriction. Then, it removes expired change table entries. By default, three days of data is retained.

    http://msdn.microsoft.com/en-us/library/cc645885(SQL.105).aspx

    Change data capture uses a retention based cleanup strategy to manage change table size. The cleanup mechanism consists of a SQL Server Agent Transact-SQL job that is created when the first database table is enabled. A single cleanup job handles cleanup for all database change tables and applies the same retention value to all defined capture instances.
    ---------------------------------

    In other words, CDC feature have auto cleanup functionality and you actually observed behavior of it.

    Tuesday, May 22, 2012 8:25 AM

All replies

  • Hi Vidhyut,

    This is a training and certification forum.  As your question relates to neither of these, I will move it to a more appropriate forum.


    When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answer

    Jeff Wharton
    MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCSD, MCITP, MCDBA
    Blog: Mr. Wharty's Ramblings
    Twitter: @Mr_Wharty
    MC ID: Microsoft Transcript

    Monday, May 21, 2012 6:11 AM
  • http://msdn.microsoft.com/en-us/library/cc645937.aspx

    Data that is deposited in change tables will grow unmanageably if you do not periodically and systematically prune the data. The change data capture cleanup process is responsible for enforcing the retention-based cleanup policy. First, it moves the low endpoint of the validity interval to satisfy the time restriction. Then, it removes expired change table entries. By default, three days of data is retained.

    http://msdn.microsoft.com/en-us/library/cc645885(SQL.105).aspx

    Change data capture uses a retention based cleanup strategy to manage change table size. The cleanup mechanism consists of a SQL Server Agent Transact-SQL job that is created when the first database table is enabled. A single cleanup job handles cleanup for all database change tables and applies the same retention value to all defined capture instances.
    ---------------------------------

    In other words, CDC feature have auto cleanup functionality and you actually observed behavior of it.

    Tuesday, May 22, 2012 8:25 AM