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
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
Step 04: Configure database audit
Step 05: Enable server audit
Step 06: Enable database audit
Step 07: Check the audit files are created automatically in the path.
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.
--truncate table itemlist --delete from itemlist where 1 = 1 SELECT database_name ,schema_name ,object_name ,session_id ,server_principal_name as [user_name] ,statement ,succeeded ,file_name ,additional_information FROM fn_get_audit_file('C:\DBAudit\AUDIT001\*AUD*', NULL, NULL) WHERE action_id = 'DL' ORDER BY event_time DESC