Using extended events to capture deletion operations

Published Jul 16 2021 02:50 AM 776 Views
Microsoft

Here you have a sample to create an .xel file with extended events generated each time that is detected “%delete from%. As this could influence on the performance I recommend you test it on a non production environment.

 

CREATE DATABASE SCOPED CREDENTIAL [https://azurestorageaccount.blob.core.windows.net/events]
WITH IDENTITY='SHARED ACCESS SIGNATURE',
SECRET = 'sp=racwdl&st=2021-07-15T14:30:44Z&se=2021-07-16T14:30:44Z&sv=2020-08-04&sr=c&sig=xxxxA0Hzo2HkPIgCc%2FetQIeEMcewZ4%2Bojjr1nh4Eh1Y%3D'

CREATE EVENT SESSION ssEventodelete
ON DATABASE
ADD EVENT sqlserver.sql_batch_completed (
ACTION  (sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_id,sqlserver.database_name,sqlserver.session_id,sqlserver.sql_text,sqlserver.username)
WHERE ([sqlserver.sql_text] like '%delete from%'))
ADD TARGET package0.event_file(
SET filename=N'https://azurestorageaccount.blob.core.windows.net/events/DemoDelete.xel')


ALTER EVENT SESSION ssEventodelete ON DATABASE STATE=START

 


An this is the output that it is generated when it is detected a DELETE operation

Palomag_MSFT_0-1626428346279.png

 

Additional Information:

https://techcommunity.microsoft.com/t5/azure-database-support-blog/extended-events-capture-step-by-s...
https://techcommunity.microsoft.com/t5/azure-database-support-blog/lesson-learned-1-capturing-a-tsql...

Enjoy, Paloma.-

Co-Authors
Version history
Last update:
‎Jul 16 2021 02:50 AM
Updated by: