Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Mar 10 at 10:50

This question came from our site for professional and enthusiast programmers.

add comment

1 Answer 1

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.