Is there a way to remove a specific captured event (deadlock) from SQL Monitor

DavidT

There was an deadlock event yesterday on an ad-hoc SQL query that contained PHI.  I have been tasked with removing that captured event from the SQL Monitor.  Is there a quick way to do this or a SPROC/Query that can be used to identify and remove that specific captured event?

Russell D

This isn't going to be easy I'm afraid. To try and point you in the right direction, it's a case of tracking the alert keys in the alert.Alert table based on the TargetObject column, and you can then delete the row from there: you have to trace through the keys to find the right ID, though. It isn't going to be easy but you are free to do anything you want with the database - please do make sure you have a working back up though.

We can't really write sql queries for people but I will look a little further into this next week and see if I can provide any more useful information.

Russell D

Certainly for extended event deadlocks you can trace this in the data.Cluster_SqlServer_ExtendedEvents_Instances_View, and edit the XML data in the EventData field.