Capture TRUNCATE and DELETE events using Audit in SQL Server

Last week a good friend of mine (Parth Shah) asked me a very interesting question that he wants to capture TRUNCATE and DELETE statements being fired on a database with very minimal overhead on the system. When performance is the concern then server side trace or DML triggers for delete statements are definitely not the right options. Let’s see how SQL Server database audit is a help in this matter?

Note: Audit is available from SQL Server 2008 on Enterprise, Developer, and Evaluation editions only.

You can also configure Extended events for the same but you need to be very careful and make sure you have the right filters on it, otherwise you will end up capturing a whole lot of things.

Step 01: Create server audit

capture-truncate-delete-sql-server-01
Step 02: Configure server audit

capture-truncate-delete-sql-server-02

Imp: Configure other parameters correctly, if you are implementing on a production server otherwise you will end up choking server disk space someday. Also do clean the audit files periodically.

Step 03: Create database audit

capture-truncate-delete-sql-server-03

Step 04: Configure database audit

capture-truncate-delete-sql-server-04

Step 05: Enable server audit

capture-truncate-delete-sql-server-05

Step 06: Enable database audit

capture-truncate-delete-sql-server-06

Step 07: Check the audit files are created automatically in the path.

capture-truncate-delete-sql-server-07

Create a sample table and fire truncate or delete statement on it and then check the same by reading the audit logs shown in Step 08.

Step 08: Read the audit files.

capture-truncate-delete-sql-server-08

Prasad Sahoo

Prasad Sahoo, is an enthusiast of Microsoft technologies in general and a passionate database professional, mainly focusing on SQL Server performance tuning and business intelligence. He has contributed a long time of his career working on SQL Server and other RDBMS. If he is not busy with SQL stuff then he must be watching some Hollywood movies.

One thought on “Capture TRUNCATE and DELETE events using Audit in SQL Server

  • September 12, 2016 at 12:11 am
    Permalink

    Dear Prasad,

    It is very helpful to me for implementing this changes on servers.

    Reply

Leave a Reply

%d bloggers like this: