Find Objects with Most deadlocks
RofL
Posts: 13
Hi,
I'm monitoring a Microsoft Dynamics NAV database with SQL Monitor, and whilst very happy SQL Monitor is alerting on a heck of a lot of deadlocks/blocks on the NAV database (100's a day.) I'm wondering if there is a way to query the SQLMonitor database to group the deadlocks by object? I've had a look myself but I've not really got anywhere, because the TargetObject column in the alert.alerts table doesn't specify an object that is deadlocked (see example below). However when I review deadlocks on the alert page SQL Monitor is able to identify the objects affected, so clearly SQL Monitor can infer which objects are affected at some point.
7:Cluster,1,4:Name,s21:navsql.foobar.com,9:SqlServer,1,4:Name,s0:,10:SqlProcess,2,9:LoginTime,d635779120793400000,9:SessionId,I235,
I'm kind of guessing that this is not possible, but would be good to know for sure before I move on.
Thanks for any advice.
I'm monitoring a Microsoft Dynamics NAV database with SQL Monitor, and whilst very happy SQL Monitor is alerting on a heck of a lot of deadlocks/blocks on the NAV database (100's a day.) I'm wondering if there is a way to query the SQLMonitor database to group the deadlocks by object? I've had a look myself but I've not really got anywhere, because the TargetObject column in the alert.alerts table doesn't specify an object that is deadlocked (see example below). However when I review deadlocks on the alert page SQL Monitor is able to identify the objects affected, so clearly SQL Monitor can infer which objects are affected at some point.
7:Cluster,1,4:Name,s21:navsql.foobar.com,9:SqlServer,1,4:Name,s0:,10:SqlProcess,2,9:LoginTime,d635779120793400000,9:SessionId,I235,
I'm kind of guessing that this is not possible, but would be good to know for sure before I move on.
Thanks for any advice.
Comments
Thanks for using the Redgate forums.
I have attached a script which might get you some of what you want.
You can add a line at the bottom to filter only the lines containing the object name. The object names are held within the full message so it would require further manipulation to get what you need I think. Let me know if you need any further assistance.
Dan Bainbridge
Product Support Engineer | Redgate Software
Thanks again!