locked
Where is Change Tracking data stored ? RRS feed

  • Question

  • Hi Friends,
    On SQL 2008, where exactly change tracking data is stored ?
    Is that within the same DB that you enable CT or another system DB ?
    If I changed DB recovery to simple, will still CT work for me ?

    Thanks in advance ,
    Patrick
    Tuesday, February 23, 2010 6:39 PM

Answers

  • Hi,

    1. Change tracking data is stored in the following internal tables.
      • Internal change table
      • There is one internal change table for each user table that has change tracking enabled.
      • Internal transaction table
      • There is one internal transaction table for the database.
    Internal tables are placed on the same filegroup as the parent entity. You could use the sys.internal_tables catalog view to show all the internal tables and parent entities. For example:
    select name, object_name(parent_id) as parent_object from sys.internal_tables

    For more information about change tracking data storage and internal tables , please refer to http://technet.microsoft.com/en-us/library/bb964713.aspx and http://technet.microsoft.com/en-us/library/ms366343.aspx.

    2. A database can be enabled change tracking under the simple recovery model.

    3. Change tracking captures the fact that rows in a table were changed, but does not capture the data that was changed.  Nevertheless, change data capture provides historical change information for a user table by capturing both the fact that DML changes were made and the actual data that was changed. I think this is the significant difference between change tracking and change data capture. For more information about Comparing Change Data Capture and Change Tracking, please see http://technet.microsoft.com/en-us/library/cc280519.aspx

    If there are any more questions, please let me know.
    Thanks.

    ***Xiao Min Tan***Microsoft Online Community***
    Thursday, February 25, 2010 5:29 AM

All replies

  • I'm using CHANGETABLE and not CDC tables.
    what in that case ? CDC is not enables for each table, but using CHANGETABLE is and I can grab thechanges that way.
    what is the difference between CDC schema table and CHANGETABLE then ?



    Wednesday, February 24, 2010 12:27 AM
  • Change Tracking uses tempdb heavily whereas CDC uses the transaction log.  See Paul Randal's article in Technet:

    http://technet.microsoft.com/en-us/magazine/2008.11.sql.aspx?pr=blog
    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    Wednesday, February 24, 2010 4:38 AM
  • Hi,

    1. Change tracking data is stored in the following internal tables.
      • Internal change table
      • There is one internal change table for each user table that has change tracking enabled.
      • Internal transaction table
      • There is one internal transaction table for the database.
    Internal tables are placed on the same filegroup as the parent entity. You could use the sys.internal_tables catalog view to show all the internal tables and parent entities. For example:
    select name, object_name(parent_id) as parent_object from sys.internal_tables

    For more information about change tracking data storage and internal tables , please refer to http://technet.microsoft.com/en-us/library/bb964713.aspx and http://technet.microsoft.com/en-us/library/ms366343.aspx.

    2. A database can be enabled change tracking under the simple recovery model.

    3. Change tracking captures the fact that rows in a table were changed, but does not capture the data that was changed.  Nevertheless, change data capture provides historical change information for a user table by capturing both the fact that DML changes were made and the actual data that was changed. I think this is the significant difference between change tracking and change data capture. For more information about Comparing Change Data Capture and Change Tracking, please see http://technet.microsoft.com/en-us/library/cc280519.aspx

    If there are any more questions, please let me know.
    Thanks.

    ***Xiao Min Tan***Microsoft Online Community***
    Thursday, February 25, 2010 5:29 AM