September 13, 2016 at 4:02 am
Hi
I am looking for proper Lock email notification HTML format (Setup Job) that can email me every times lock happens. I am sitting with problem where the application owners are not taking responsibly as the application is not closing there connections. This is causing huge problems.
I am looking for email proving what host process is causing the problem - Even where the connection is coming from. For example
Server G1-App server and G1-Userpc.
Attached is two Lock emails.
The one LockEMail does send notifications but not all the time is it missing Locks when it happens. Second LockEmail2 is not sending any details when locks happens. (I was busy compiling this.)
September 13, 2016 at 4:44 am
I think you'd be better off with an alert that notifies you every time there's a blocked process event (if that's actually what you want - you may change your mind the first time you get deluged with e-mails!). This thread explains how to do that. I haven't been through the steps myself, so I can't vouch for its efficacy.
John
September 13, 2016 at 4:55 am
Thank you. Any advice is welcome.
September 13, 2016 at 5:05 am
September 14, 2016 at 7:45 am
I just finished doing something similar with a co-worker yesterday. We used sp_WhoIsActive to find locks with
@find_block_leaders = 1,
@get_full_inner_text = 1,
@get_plans=1,
@sort_order = '[blocked_session_count] DESC'
And using the alert built in to SSMS to run a job that'll add the info from WhoIsActive to a temp table to send.
This link : http://sqlblog.com/blogs/adam_machanic/archive/2011/04/25/capturing-the-output-a-month-of-activity-monitoring-part-25-of-30.aspx does most of the work.
The alert from SSMS on its own will tell you when there are deadlocks past a specified amount of time, but WhoIsActive will give more details.
September 14, 2016 at 9:15 am
Thank you for the info
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply