How Can I Check My Database's Retention Period for Change Tracking (CT)?

Question

How can I check my database's retention period setting for change tracking (CT)?

Environment

The following SQL Server database connectors:

• Generic SQL Server
• Amazon RDS for SQL Server
• Azure SQL Database
• Azure SQL Managed Instance
• Google Cloud SQL for SQL Server

Answer

You can check your database's retention period setting for CT by querying the sys.change_tracking_databases SQL Server catalog view. Use the following query:

SELECT DB_NAME(database_id) [DATABASE_NAME], retention_period, retention_period_units_desc
FROM sys.change_tracking_databases;

The query output will contain one row for each database in which you have enabled CT. The retention_period column represents the duration your database retains CT data. The retention_period_units_desc column represents the unit of time for the retention period.