How to Track Database Changes in SQL Server (Proper Guideline)

You usually get two built-in features to track database changes in your SQL Server. One is the Change Tracing feature, while the other is the Change Data Capture. Once you enable these, the applications can determine DML changes like insert, update, and delete operations.

Although you can create your own programs to track the same changes, it will take significantly more time and effort. So, let’s see how to use these two features for change tracking.

The Process of Tracking Data Changes in Database

Despite offering similar functionalities, Change Data Capture is slightly superior to the Change Tracking feature. That is because it provides historical data that may come in handy.

A. Change Tracking

Change tracking is a feature that enables applications to query for changes and access related info. You can use the SQL Server Management Studio or DDL statements to configure this feature. And for it to work right, you have to enable it at the database level and then for the table to track. Here’s how.

Enable Change Tracking for a Database

Below is an example of how to enable Change Tracking on a database level. Specify the values of CHANGE_RETENTION and AUTO_CLEANUP duration at any time as per your requirements. 

ALTER DATABASE AdventureWorks2022  

SET CHANGE_TRACKING = ON  

(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)

The retention value you set determines how long the system will keep the tracking data. Data older than the specified period gets deleted periodically.

Always use a retention period equal to or more than the maximum interval between the synchronization of applications with the tables. If an application receives updates less frequently, it can have a negative impact on the returned results. The CHANGE_TRACKING_MIN_VALID_VERSION function can come to the aid.

You can also enable Change Tracking through the Database Properties dialog box in SQL Server Management Studio. However, this process will not work if the database has memory-optimized tables. In that case, you have to use T-SQL.

Enable Change Tracking for a Table

After enabling Change Tracking for the database, it is necessary to do the same for the table you want to track. Use the following example as a reference.

ALTER TABLE Person.Contact  

ENABLE CHANGE_TRACKING  

WITH (TRACK_COLUMNS_UPDATED = ON)

By setting the TRACK_COLUMNS_UPDATED option to ON, you can enable the DB engine to store additional data regarding the updated columns. However, you may want to keep it OFF to avoid storage overhead.

B. Change Data Capture

Unlike Change Tracking, Change Data Capture provides historical information, including data change alongside DML (Data Manipulation Language) changes. When enabled, it creates a new system table with the same structure as the source table. On top of that, it adds a new column to contain the change information.

The following example shows how to configure Change Data Capture (CDC) on a database table.

Enable CDC at the Database Level

1. Open SQL Server Management Studio (SSMS) and connect to the SQL Server instance.

2. Right-click on the database and select Properties.

3. Go to Database Properties and navigate to the Change Tracking page.

4. Check the Change data capture option to enable it.

Enable CDC for a Table

Run the ‘sys.sp_cdc_enable_table’ procedure to enable Change Data Capture for specific tables. Below is an example of how to run this function.

EXEC sys.sp_cdc_enable_table

    @source_schema = N'dbo',

    @source_name   = N'YourTableName',

    @role_name     = NULL; -- Specify a role if needed.

Start the Capture Job

SQL Server uses jobs to capture changes and store them in the change table. So, start the capture job for that process to begin.

EXEC sys.sp_cdc_start_job;

Verify CDC Configuration

The below query will show a list of the CDC enabled tables and let you verify whether or not the configuration was complete.

SELECT * FROM cdc.change_tables;

Retrieve Change Data

Use functions like ‘cdc.fn_cdc_get_all_changes and ‘cdc.fn_cdc_get_net_changes’ to retrieve the change data from the CDC enabled tables.

Frequently Asked Questions and Answers – FAQs

How to track database schema changes in SQL Server?

Tracking database schema changes in SQL Server is achievable through the Schema Changes History report, a built-in option in SQL Server Management Studio. Most importantly, it lets you view changes on both the database and instance levels.

How to find who updated records in SQL Server?

The way to find out who updated the records or tables in an SQL server is by accessing the transaction logs.

How to check database history in SQL Server?

Connect to and expand an instance of your database engine from the Object Explorer. After that, select SQL Server Agent and go to the jobs section. Right-click on a job and select View History.

Conclusion

You can enable both Change Tracking and Change Data Capture (CDC) on the same database. Doing so includes the same steps shown in this article. However, when you want to track database changes in your SQL server using CDC, remember that it can generate a lot of data over time, causing storage issues. So, try to clean up old data periodically.