How to check if Change Tracking (CT) is enabled on a SQL Server Database

Change Tracking (CT) feature was introduced in SQL Server 2008, and is used to get the rows in user tables that have changed, without the data that was changed. You can get the latest data directly from the tracked table.

To list all SQL Server databases that have Change Tracking (CT) enabled, use the following query:

SELECT d.name AS 'Database Name', t.*
FROM sys.change_tracking_databases t
INNER JOIN sys.databases d ON d.database_id = t.database_id

list_databases_CT_enabled

You will get one row for each database that has Change Tracking (CT) enabled.

To check if a database has Change Tracking enabled, use the following query:

SELECT d.name AS 'Database Name', t.*
FROM sys.change_tracking_databases t
INNER JOIN sys.databases d ON d.database_id = t.database_id
WHERE d.name = 'DB_Name'

database_CT_enabled

If the database has Change Tracking (CT) enabled you will get one row as the result.

Using SQL Server Management Studio:

In Object Explorer right click on the database -> Click 'Properties'

Click on 'Change Tracking' tab:

database_CT_enabled_SSMS

If the post helped you, please share it:
Pin It

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">