SQL Server 2008: Where is the change tracking table stored?

Question

We have a database running on a shared host via SQL Server 2008. We are now in the position, due to performance constraints, where we have to move the database, along with a website. We use change tracking on the database so that clients can sync using the sync framework.

My question is: is the change tracking table stored within the database in a way that means that when relocated, it will remain in place?

I am currently in the process of learning SQL Server, and the finer points of change tracking still allude me. Conceptually, I don't really understand 'where' the change tracking table is.

Answer 1

The Commit table is a hidden internal table named sys.syscommittab that can only be accessed directly by administrators, and stores one row for each transaction for each modification to a table particpating in Change Tracking.

The related data is stored in seperate system tables, created when change tracking is enabled on a user table.

The internal table created by enabling change tracking at a table level is named sys.change_tracking_[object id], where [object id] is the objectID for the target table.

Answer 2

Deliberated over whether or not to post this as an answer as I don't generally like just giving a link, but in this instance I wanted to summarise some of the points and so wouldn't do well as a comment!

This article is well worth a read which gives a lot of detail on how change tracking and (and change data capture) works.

For Change Tracking, you can find what internal tables are being used to track changes using:

SELECT [name] FROM sys.internal_tables
  WHERE [internal_type_desc] = 'CHANGE_TRACKING';

The important quote is:

The table is what's called an internal table, and there is no control over its name or where it is stored.