Change Tracking Internals (SQL2008R2) Part 3

Storage with Change Tracking

Here we will see how Data Storage happens with Change Tracking and what effect does it carry. For Each User Table enabled for Change Tacking there is one internal change table and Internal Transactional Table.

There is one internal transaction table for the database.These internal tables affect storage requirements:

Internal change table for each table enabled for Change Tracking and Internal Transactional Table.

I have enabled a Table for CT; let’s see what the space is used for by internals tables. Initially Internal change Table will be of Zero Size.

Now have some transactions done on the table (Delete or Insert) and check the space used by internal tables.

For each change to each row in the user table, a row is added to the internal change table. This row has a small fixed overhead plus a variable overhead equal to the size of the

Primary key columns. The row can contain optional context information set by an application. And, if column tracking is enabled, each changed column requires 4 bytes in the tracking table.

For each committed transaction, a row is added to an internal transaction table.

sys.syscommittab :

Displays one row for each transaction that is committed for a table that is tracked by SQL Server change tracking. The sys.dm_tran_commit_table management view, which is provided for supportability purposes and exposes the transaction-related information that change tracking stores in the sys.syscommittab system table. The sys.syscommittab table provides an efficient persistent mapping from a database-specific transaction ID to the transaction’s commit log sequence number (LSN) and commit timestamp. The data that is stored in the sys.syscommittab table and exposed in this management view is subject to cleanup according to the retention period specified when change tracking was configured.

Leave a Reply

Your email address will not be published.