I received one question on my email regarding how to capture TRUNCATE and DELETE statements being fired on a database and this a very interesting question.
We can track these events many ways but When performance is the concern then server side trace or DML triggers for delete statements are definitely not the right options.
So, we can do it through SQL Server database Audit with very minimal overhead on the SQL Server.
we can also capture these events through Extended events but we need to be very careful and make sure that we have the right filters on it, otherwise we will end up capturing a whole lot of events.
Create server audit
Configure server audit
Create database audit
Configure database audit
Enable server audit
Enable database audit
Check the audit files are created automatically in the path.
- Create a Demo database and a demo table and fire truncate or delete statement on it and then check the audit logs then we get below entries:-
Read the audit files through below query find the output like below:-
SELECT
database_name
,schema_name
,object_name
,session_id
,server_principal_name as [user_name]
,succeeded
,file_name
FROM fn_get_audit_file('E:\Tutorials\*AUD*', NULL, NULL)
WHERE action_id = 'DL'
Hope it will help !!!