I found some column values have been changed on a table, I need to view when and who made these changes.

I tried to read the transaction log backup file, but I didn't understand it and cannot find the information that I look for.

Any help please. If there is a way, if no for the future which is the best way to audit database transactions without impact the performance, SQL audit, or trace or custom triggers?


2 Answers 2


There are several methods to audit database transactions, but they all affect performance more or less

SQL Server Change Tracking and SQL Server Change Data Capture don't show who, when, and how executed the transactions. On the other hand, SQL Server Change Data Capture shows the old and new values for the UPDATE statements. Here is a useful set of comparison notes: SQL Server 2008 Change Tracking (CT) and Change Data Capture (CDC) and Comparing Change Data Capture and Change Tracking

SQL Server Auditing shows who, when, and how, but doesn't show the old and new values for the UPDATE statements

The methods that read the database transaction log files don't add overhead, as there is not additional change capturing. Besides fn_dblog, that can return results not easy to understand, there are third party tools, such as ApexSQL Log

There are two more auditing tools from ApexSQL - ApexSQL Audit which uses triggers, so can impact performance on a high transaction database, and ApexSQL Comply that uses SQL traces

SQL Server database auditing techniques

Disclaimer: I work for ApexSQL as a Support Engineer


I'm an open sorcerer and cannot afford the license fees for Change Data Capture, it's only available with Microsoft SQL Server Enterprise Edition. So I've implemented change tracking with SQL triggers. The trigger adds an entry into the audit table, which contains among other things the before and after image of the data as XML, more details can be found at yact on github.

Here's the basic template for a trigger

-- This a template for a yact auditing trigger.
-- You MUST replace the following strings
--  - {table} with the name of the table to audit
--  - {table_id} with the name of the table's primary key
create trigger [trg_{table}]
on {table} for insert, update, delete not for replication
set NOCOUNT on

-- TODO: operation should be a computed value based on the nullness of old_content and new_content
declare @operation as char(1)
if exists (select * from inserted)
if exists (select * from deleted)
    select @operation = 'U'
    select @operation = 'I'
    select @operation = 'D'

insert into audit (operation, table_name, old_content, new_content) 
    case when d.{table_id} is null then null else (select d.* for xml raw) end,
    case when i.{table_id} is null then null else (select i.* for xml raw) end
    from inserted as i
    full outer join deleted as d on i.{table_id} = d.{table_id}


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.