Home » Blog » SQL Server » How to Find Table Changes in SQL Server?

How to Find Table Changes in SQL Server?

Neeraj Kumar
Published By Neeraj Kumar
Debasish Pramanik
Approved By Debasish Pramanik
Published On
Last Updated

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:

OptionRecordsNames the LoginNeeds
Change TrackingChanged rows and the operationOnly through a context value that you setCompatibility level 90 or higher
Change Data CaptureChanged rows with column valuesNoStandard or Enterprise edition and SQL Server Agent
SQL Server AuditStatement text with the login and timeYesAn audit and a database audit specification
Log analyzer softwareInserts, updates and deletes from the LDF fileYesA 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:

  1. Backup the database before any change. Explore Different Types of Backups in SQL Server explains every backup type.
  2. Check the edition with SELECT SERVERPROPERTY('Edition'); because Microsoft states that Change Data Capture needs Standard or Enterprise.
  3. Confirm that SQL Server Agent is started before you enable Change Data Capture.
  4. 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:

MethodBest ForMain Limit
Change TrackingDetecting table changes by rowNo column values
Change Data CaptureValue history of table changesCleanup deletes data after three days by default
SQL Server AuditFinding who made a changeRecords start after setup
Log Analyzer SoftwareChanges that already happenedShows 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:

  1. Enable Change Tracking on the database:
ALTER DATABASE SalesDB
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);
  1. Enable it on the table:
ALTER TABLE dbo.Orders
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON);
  1. Save the current version number:
SELECT CHANGE_TRACKING_CURRENT_VERSION();
  1. Let users work and execute this query. Replace 0 with 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:

  1. Start SQL Server Agent in SSMS or SQL Server Configuration Manager.
  2. Enable Change Data Capture on the database:
USE SalesDB;
EXEC sys.sp_cdc_enable_db;
  1. Enable it on the table:
EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name = N'Orders',
    @role_name = NULL;
  1. 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:

  1. Create the folder D:\Audit and 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);
  1. 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);
  1. 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:

  1. Start the log analyzer on your Windows PC. Log analyzer home screen
  2. Press Open and locate the LDF or MDF file. Open button
  3. Choose Online DB Option, enter the server name, set the Authentication type and open the Select Database list to choose the database. Online DB Option window
  4. Click OK and wait until the scan ends. Totals of records, inserts, updates and deletes appear in a report. Click OK again. Scan report with totals
  5. 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. Table log preview Entries sorted by login name
  6. Click a transaction to see its details. Transaction details

Export the Changes

Seven steps export the changes:

  1. Tick the tables and click Export. Export button
  2. Tick INSERT, UPDATE and DELETE for the records that you need. Record type filters
  3. Tick Date Filter and set the date range. Date filter
  4. Choose an export option: SQL Server Database, SQL Server Compatible SQL Scripts or CSV. Export options
  5. Name the destination server and set the authentication type. Database credentials
  6. Choose Export to Existing Database or Create New Database, type the database name and click Export. Export button for the new database
  7. Review the status report and click OK. Export status report

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:

ProblemReasonFix
CHANGETABLE returns no valid resultThe version number is older than the minimum valid versionCheck CHANGE_TRACKING_MIN_VALID_VERSION and start again
Change Data Capture shows no rowsThe capture process saves entries only while SQL Server Agent is startedStart the Agent and wait for the next scan cycle
Error 22832The Database Engine or the Agent uses the NETWORK SERVICE accountChange the service account
MSG_INVALID_AUDIT_FILEThe path is wrong or the file is not an audit fileAdd the folder path with a file name pattern
Old changes are missingCleanup removed them or tracking was offUse 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.
  Neeraj Kumar

By Neeraj Kumar

Neeraj Kumar is a Principal Technical Architect at EmailDoctor with 19+ years of experience in email migration, cloud migration, data recovery and digital forensics. He guides enterprise teams through Exchange, Office 365 and Google Workspace projects and shares what works as an EmailDoctor writer. Every method in his posts is tested on Windows and Mac before it goes live.