August 14, 2008 at 12:42 am
Is there any way, a trigger mail/task could be generated when a particular databse gets deadlocked for more than a particular amount of time...say 5 min.
Scenario:
All the stores which use our production server , are supposed to do a specific task , within a time frame of 3 hrs.Since all of them , update the database at the same time, hence we have frequent deadlocks..which have 2 b monitored and the blocking processes have 2 b killed.
We run a query evry 30min, when d databsae gets deadlocked...nothin execcutes..all d queries related to the databse are deadlocked.
i was wondering if there is any script, which can sent a triger mail, when d deadlock doesnot resolve within 5 min. Thanx in advance.
August 14, 2008 at 4:45 am
I think that
http://qa.sqlservercentral.com/articles/Administration/3243/
is helpful...
However you should take steps to minimize deadlock occurence - BOL ---> Minimizing Deadlocks may be a good starting point...
August 14, 2008 at 8:34 pm
thanx..grasshoper , but that didnt help, what i actually need is an alert , whenever the deadlock exceeds 5 min, call it a deadlock or server hang up, but..in my case all the processes go into waiting around 3-4 min and after that, they automatically start running normally.
So what i need is an alert, whenever, the deadlock exceeds 5min.
August 15, 2008 at 2:07 pm
selvin_allsmiles (8/14/2008)
thanx..grasshoper , but that didnt help, what i actually need is an alert , whenever the deadlock exceeds 5 min, call it a deadlock or server hang up, but..in my case all the processes go into waiting around 3-4 min and after that, they automatically start running normally.So what i need is an alert, whenever, the deadlock exceeds 5min.
I seriously doubt that a deadlock last "5" min... A blocking process could but deadlocks are resolved automatically by SQL Server.
unless your apps are setting LOCK_TIMEOUT very high of course 🙂 ( there is always room for making it worse )
* Noel
August 18, 2008 at 5:02 pm
1. Create an Event in SQL Server. Configure it to listen to the SQL Server namespace at \\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER. Make the query of the alert SELECT * FROM DEADLOCK_GRAPH.
2. Create a table somewhere...
CREATE TABLE DeadlockEvents
(AlertTime DATETIME, DeadlockGraph XML) ;
GO
3. Configure the SQL Server Agent options to allow replacement of process level token.
4. Create a job to populate the table. Put a step in the job with the following code. Make sure to do this thru the GUI, if you do it thru sp_addjobstep it tanks because of the quotes.
INSERT INTO DeadlockEvents
(AlertTime, DeadlockGraph)
VALUES (getdate(), N''$(ESCAPE_SQUOTE(WMI(TextData)))')
(I think I got the quotes right on that, test it to make sure.)
5. Configure the alert to fire the job whenever it triggers.
6. Write a query to look at the table and see how many times the alert fires within a given time window.
7. To see the deadlock chart, save the XML as a .xdl file, truncate the leading and trailing [textdata] tags, close and re-open in management studio.
*EDIT, its an .xdl file, not an .xld file...
August 19, 2008 at 10:00 am
Also, just so you know... if you choose to trace deadlocking in the log file, there's a new trace flag in 2005 that gives you a lot more information. Try turning on trace flag 1222 to see it.
August 19, 2008 at 11:52 am
Ideara's Diagnostic manager can setup email elerts and you get alot more info on the db.
August 19, 2008 at 1:25 pm
Daignostic Manager is a fantastic tool. Granted you can probably spend months creating something yourself but why? Agentless based, low impact and footprint, smart monitoring, KLMs pre-configured and totally customizable, and they've just announced SQLMobile DM is now included in the package deal...
Whats not to like?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply