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.

Gaudreau

heh, thanks. I'm sure I put them in but oh well! – Gaudreau

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.

Hardiness

Thanks for the reply. You'll have to forgive my ignorance but with sys.syscommittab being hidden and internal does that mean that it is separate from the database and will not be included in any backup? Worst guess scenario, we move the database and lose change tracking information I guess. – Gaudreau

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.

Octroi

Answer 3

From here it looks like change tracking is a general purpose table in the same database catalogue as your tables. So any movement you do of the database should take the change table with you.

I would investigate the the schema of the database first, and use the MSDN pages.

Sino