You find table changes in SQL Server with three built in features and one software method. Change Tracking lists changed rows. Change Data Capture adds the changed values. SQL Server Audit names the login. All three record changes only after you enable them so log analyzer software scans the transaction log for earlier changes. Test every setting on a copy and backup before you change a production database.
Someone changed rows in a production table and nobody knows who did it or when. Row counts differ from last week and the application shows old prices.
SQL Server does not save a history of table changes by default. Change Tracking, Change Data Capture and SQL Server Audit record table changes only after you enable them. None of them explains a change that already happened.
This guide explains how to find table changes in SQL Server with four methods, which feature shows rows, values or logins and what to do when the change happened before tracking began.
Microsoft compares the tracking features in its track data changes article.
Which Feature Helps You Find Table Changes in SQL Server?
Four options exist and they record different details:
| Option | Records | Names the Login | Needs |
|---|---|---|---|
| Change Tracking | Changed rows and the operation | Only through a context value that you set | Compatibility level 90 or higher |
| Change Data Capture | Changed rows with column values | No | Standard or Enterprise edition and SQL Server Agent |
| SQL Server Audit | Statement text with the login and time | Yes | An audit and a database audit specification |
| Log analyzer software | Inserts, updates and deletes from the LDF file | Yes | A database with its LDF file |
Change Tracking, Change Data Capture and Audit record table changes only after you enable them. Table changes made earlier need Method #4.
Which Checks Should You Make First?
Make these four checks before any setup:
- Backup the database before any change. Explore Different Types of Backups in SQL Server explains every backup type.
- Check the edition with
SELECT SERVERPROPERTY('Edition');because Microsoft states that Change Data Capture needs Standard or Enterprise. - Confirm that SQL Server Agent is started before you enable Change Data Capture.
- Test every setting on a copy because every feature adds some storage or processing cost.
4 Ways to Find Table Changes in SQL Server
Three methods use built in features and the fourth uses software:
| Method | Best For | Main Limit |
|---|---|---|
| Change Tracking | Detecting table changes by row | No column values |
| Change Data Capture | Value history of table changes | Cleanup deletes data after three days by default |
| SQL Server Audit | Finding who made a change | Records start after setup |
| Log Analyzer Software | Changes that already happened | Shows only what the log file contains |
Method #1: Use Change Tracking (Free, Lists Changed Rows)
Change Tracking helps you find table changes in SQL Server at row level. Microsoft states that it saves the primary key and the operation but not the data that changed. Four steps start it:
- Enable Change Tracking on the database:
ALTER DATABASE SalesDB
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);
- Enable it on the table:
ALTER TABLE dbo.Orders
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON);
- Save the current version number:
SELECT CHANGE_TRACKING_CURRENT_VERSION();
- Let users work and execute this query. Replace
0with the version number from step 3:
SELECT ct.OrderID, ct.SYS_CHANGE_OPERATION, ct.SYS_CHANGE_VERSION, o.*
FROM CHANGETABLE(CHANGES dbo.Orders, 0) AS ct
LEFT OUTER JOIN dbo.Orders AS o
ON o.OrderID = ct.OrderID;
Note: SYS_CHANGE_OPERATION shows I for an insert, U for an update and D for a delete. Deleted rows show empty table columns, which is why the query uses the outer join. Microsoft strongly recommends setting snapshot isolation to ON for the database.
Method #2: Use Change Data Capture (Free, Shows Changed Values)
Change Data Capture finds table changes together with their values. Asynchronous scans of the transaction log save the changed data in change tables. Four steps start it:
- Start SQL Server Agent in SSMS or SQL Server Configuration Manager.
- Enable Change Data Capture on the database:
USE SalesDB;
EXEC sys.sp_cdc_enable_db;
- Enable it on the table:
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name = N'Orders',
@role_name = NULL;
- Query the captured changes:
DECLARE @from_lsn BINARY(10) = sys.fn_cdc_get_min_lsn('dbo_Orders');
DECLARE @to_lsn BINARY(10) = sys.fn_cdc_get_max_lsn();
SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_Orders(@from_lsn, @to_lsn, N'all');
Note: The change table stores __$operation as 1 for a delete, 2 for an insert, 3 for the row before an update and 4 for the row after it. Change tables have no login column. Cleanup deletes entries older than three days by default and executes daily at 2 AM.
Microsoft states that the log truncation point does not advance until the capture process gathers the marked changes. Keep SQL Server Agent started for that reason.
Method #3: Use SQL Server Audit (Free, Names the Login)
SQL Server Audit finds table changes by user. Audited actions get saved with the login, the time and the Transact-SQL statement. Three steps start it:
- Create the folder
D:\Auditand start a server audit that saves to it:
USE master;
CREATE SERVER AUDIT TableChangeAudit
TO FILE (FILEPATH = 'D:\Audit\');
ALTER SERVER AUDIT TableChangeAudit WITH (STATE = ON);
- Create a database audit specification for the table:
USE SalesDB;
CREATE DATABASE AUDIT SPECIFICATION Audit_Orders_Changes
FOR SERVER AUDIT TableChangeAudit
ADD (INSERT, UPDATE, DELETE ON dbo.Orders BY public)
WITH (STATE = ON);
- Query the audit file:
SELECT event_time, server_principal_name, database_principal_name,
object_name, statement, host_name
FROM sys.fn_get_audit_file('D:\Audit\*', DEFAULT, DEFAULT);
You can also create both objects in SSMS. Expand Security in Object Explorer, right click Audits and select New Audit.
Note: Microsoft states that all editions support database level audits from SQL Server 2016 SP1 and recommends sys.fn_get_audit_file_v2 for new work. BY public audits every user. Querying the function needs CONTROL SERVER before SQL Server 2022 and VIEW SERVER SECURITY AUDIT from SQL Server 2022 onward.
Method #4: Find Past Changes in the Transaction Log With EmailDoctor (Names the Login)
Methods 1 to 3 record only what happens after setup. EmailDoctor SQL Server Log Analyzer scans the LDF file of a database and lists the table changes that the log still contains. Every entry shows the table, the transaction, the login name and the time.
Online DB mode connects to the live server. Offline DB mode opens the LDF file directly when the server is not available.
Scan the Log File
Six steps scan the log:
- Start the log analyzer on your Windows PC.
- Press Open and locate the LDF or MDF file.
- Choose Online DB Option, enter the server name, set the Authentication type and open the Select Database list to choose the database.
- Click OK and wait until the scan ends. Totals of records, inserts, updates and deletes appear in a report. Click OK again.
- Click a table name in the left panel to preview its log entries. Click the Login Name column header to sort the entries by login.
- Click a transaction to see its details.
Export the Changes
Seven steps export the changes:
- Tick the tables and click Export.
- Tick INSERT, UPDATE and DELETE for the records that you need.
- Tick Date Filter and set the date range.
- Choose an export option: SQL Server Database, SQL Server Compatible SQL Scripts or CSV.
- Name the destination server and set the authentication type.
- Choose Export to Existing Database or Create New Database, type the database name and click Export.
- Review the status report and click OK.
Note: Only entries that the LDF file contains appear. Entries that the log already overwrote are gone.
Offline DB mode needs no connection. Tick Get all Login\User names and add the server details to show the login names in that mode.
Which Problems Stop You From Finding Table Changes?
Five problems cause most failed attempts:
| Problem | Reason | Fix |
|---|---|---|
| CHANGETABLE returns no valid result | The version number is older than the minimum valid version | Check CHANGE_TRACKING_MIN_VALID_VERSION and start again |
| Change Data Capture shows no rows | The capture process saves entries only while SQL Server Agent is started | Start the Agent and wait for the next scan cycle |
| Error 22832 | The Database Engine or the Agent uses the NETWORK SERVICE account | Change the service account |
| MSG_INVALID_AUDIT_FILE | The path is wrong or the file is not an audit file | Add the folder path with a file name pattern |
| Old changes are missing | Cleanup removed them or tracking was off | Use Method #4 |
Frequently Asked Questions
Can SQL Server Show Who Made Table Changes?
Yes. SQL Server Audit saves the login name and the statement. Log analyzer software also shows a Login Name column for every transaction.
Does Change Tracking Store the Old Values?
No. Microsoft states that it captures the fact that rows changed and not the data that changed. Use Change Data Capture for historical values.
How Long Does Change Data Capture Store Table Changes?
Three days by default. Microsoft states that cleanup deletes older entries and executes daily at 2 AM.
Why Does the Change Data Capture Query Return No Rows?
Entries reach the change table only while SQL Server Agent is started. Start the Agent and wait for the next scan cycle.
Can I Find Table Changes in SQL Server Made Before I Enabled Tracking?
Not with Methods #1 to #3 because they record table changes only after setup. Method #4 scans the transaction log and lists the changes that the log still contains.
Does Change Tracking Need Snapshot Isolation?
No. Microsoft strongly recommends setting snapshot isolation to ON because that setting makes the change information consistent.
Which Editions Include Change Data Capture?
Standard and Enterprise. Microsoft states that Change Data Capture requires one of these editions.
Which SQL Server Versions Does the Log Analyzer Support?
SQL Server 2025, 2022 and 2019 plus older versions appear on the software page.
Author’s Verdict
Change Tracking is the lightest option to find table changes in SQL Server and shows the changed rows. Change Data Capture adds the values and Audit adds the login.
Enable a feature before you need it because none of them explains an earlier change. Log analyzer software fits the cases where the change already happened.
Save a backup and test every setting on a copy before you change a production database.
- SQL Server does not save a history of table changes by default.
- Change Tracking lists changed rows, Change Data Capture adds values and SQL Server Audit names the login.
- All three need setup before the change.
- Log analyzer software scans the transaction log for table changes that already happened.