Using extended events to capture deletion operations

Published Jul 16 2021 02:50 AM 1,418 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.-

%3CLINGO-SUB%20id%3D%22lingo-sub-2555424%22%20slang%3D%22en-US%22%3EUsing%20extended%20events%20to%20capture%20deletion%20operations%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2555424%22%20slang%3D%22en-US%22%3E%3CP%3EHere%20you%20have%20a%20sample%20to%20create%20an%20.xel%20file%20with%20extended%20events%20generated%20each%20time%20that%20is%20detected%20%E2%80%9C%25delete%20from%25.%20As%20this%20could%20influence%20on%20the%20performance%20I%20recommend%26nbsp%3Byou%20test%20it%20on%20a%20non%20production%20environment.%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3ECREATE%20DATABASE%20SCOPED%20CREDENTIAL%20%5Bhttps%3A%2F%2Fazurestorageaccount.blob.core.windows.net%2Fevents%5D%0AWITH%20IDENTITY%3D'SHARED%20ACCESS%20SIGNATURE'%2C%0ASECRET%20%3D%20'sp%3Dracwdl%26amp%3Bst%3D2021-07-15T14%3A30%3A44Z%26amp%3Bse%3D2021-07-16T14%3A30%3A44Z%26amp%3Bsv%3D2020-08-04%26amp%3Bsr%3Dc%26amp%3Bsig%3DxxxxA0Hzo2HkPIgCc%252FetQIeEMcewZ4%252Bojjr1nh4Eh1Y%253D'%0A%0ACREATE%20EVENT%20SESSION%20ssEventodelete%0AON%20DATABASE%0AADD%20EVENT%20sqlserver.sql_batch_completed%20(%0AACTION%26nbsp%3B%20(sqlserver.client_app_name%2Csqlserver.client_connection_id%2Csqlserver.client_hostname%2Csqlserver.client_pid%2Csqlserver.database_id%2Csqlserver.database_name%2Csqlserver.session_id%2Csqlserver.sql_text%2Csqlserver.username)%0AWHERE%20(%5Bsqlserver.sql_text%5D%20like%20'%25delete%20from%25'))%0AADD%20TARGET%20package0.event_file(%0ASET%20filename%3DN'https%3A%2F%2Fazurestorageaccount.blob.core.windows.net%2Fevents%2FDemoDelete.xel')%0A%0A%0AALTER%20EVENT%20SESSION%20ssEventodelete%20ON%20DATABASE%20STATE%3DSTART%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CBR%20%2F%3EAn%20this%20is%20the%20output%20that%20it%20is%20generated%20when%20it%20is%20detected%20a%20DELETE%20operation%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Palomag_MSFT_0-1626428346279.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F296303iBF53705ED2289E60%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Palomag_MSFT_0-1626428346279.png%22%20alt%3D%22Palomag_MSFT_0-1626428346279.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAdditional%20Information%3A%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fazure-database-support-blog%2Fextended-events-capture-step-by-step-walkthrough%2Fba-p%2F369013%22%20target%3D%22_blank%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fazure-database-support-blog%2Fextended-events-capture-step-by-step-walkthrough%2Fba-p%2F369013%3C%2FA%3E%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fazure-database-support-blog%2Flesson-learned-1-capturing-a-tsql-command-timeout%2Fba-p%2F368791%22%20target%3D%22_blank%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fazure-database-support-blog%2Flesson-learned-1-capturing-a-tsql-command-timeout%2Fba-p%2F368791%3C%2FA%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%3CP%3EEnjoy%2C%20Paloma.-%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-2555424%22%20slang%3D%22en-US%22%3E%3CP%3EHow%20configure%20extended%20events%20to%20capture%20deletion%20operations%3C%2FP%3E%3C%2FLINGO-TEASER%3E
Co-Authors
Version history
Last update:
‎Jul 16 2021 02:50 AM
Updated by: