Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

How to receive Deadlock information automatically via email. Expand / Collapse
Author
Message
Posted Saturday, December 11, 2010 2:04 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, August 22, 2014 10:18 AM
Points: 514, Visits: 1,727
Comments posted to this topic are about the item How to receive Deadlock information automatically via email.
Post #1033380
Posted Sunday, December 12, 2010 10:19 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, June 19, 2013 5:56 AM
Points: 66, Visits: 70
Thanks Geoff.

This is great and very helpful post.


dude you rocks


Thanks,
GG
Post #1033587
Posted Monday, December 13, 2010 12:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 8, 2013 2:52 AM
Points: 1, Visits: 6
Excellent Geoef..Rocks
Post #1033605
Posted Monday, December 13, 2010 12:38 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 23, 2010 1:10 AM
Points: 1, Visits: 20
Great post,

is there any downgrade in performance when turning on the trace ?
Is it okey to use it in in system that use heavily on SQL db lets say MICROSOFT CRM ?

thanks
Post #1033616
Posted Monday, December 13, 2010 2:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 24, 2012 11:35 PM
Points: 2, Visits: 7
Although this is a very nice way of getting extra info from the error log instead of just sending out a notification message, I do not understand why you create a fully qualified table in temdb instead of simply creating a temporary table in the session using the #tablename syntax. It would automatically create it in tempdb, as that is the way sql server works.

Also I would put the syntaxt for the retrieval and sending of the email into a stored procedure and call the proc using the job. That way you can change the syntax and add, remove detail without the need of altering the job each time.

finally, consider using the built in dynamic management views to augment your reporting such as:

SELECT
t1.resource_type,
t1.resource_database_id,
t1.resource_associated_entity_id,
t1.request_mode,
t1.request_session_id,
t2.blocking_session_id
FROM sys.dm_tran_locks as t1
INNER JOIN sys.dm_os_waiting_tasks as t2
ON t1.lock_owner_address = t2.resource_address;


just my two pennies worth. But hey. Thanks for the trick with the custom job. I'll keep it in mind next time I tune a monitoring solution.
Post #1033646
Posted Monday, December 13, 2010 2:51 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 21, 2014 5:29 AM
Points: 1,179, Visits: 786
Nice article Geoff, really usefull
Post #1033658
Posted Monday, December 13, 2010 4:16 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, January 2, 2013 12:15 PM
Points: 1,443, Visits: 711
Nice work! This is useful!
Post #1033690
Posted Monday, December 13, 2010 5:52 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, August 22, 2014 10:18 AM
Points: 514, Visits: 1,727
alxdean (12/13/2010)
Although this is a very nice way of getting extra info from the error log instead of just sending out a notification message, I do not understand why you create a fully qualified table in temdb instead of simply creating a temporary table in the session using the #tablename syntax. It would automatically create it in tempdb, as that is the way sql server works.


alxdean,

you cannot pass #temp tables with xp_sendmail or sp_send_dbmail, so that is why there is a real table created in the tempdb. IF there is a way to pass a #temp table into sp_send_dbmail, I am not aware of how to do it.

thanks for reading.
Post #1033730
Posted Monday, December 13, 2010 5:53 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, August 22, 2014 10:18 AM
Points: 514, Visits: 1,727
mreizik (12/13/2010)
Great post,

is there any downgrade in performance when turning on the trace ?
Is it okey to use it in in system that use heavily on SQL db lets say MICROSOFT CRM ?

thanks


whatever overhead is created is barely measurable. and of course, if you have deadlock issues, that minor performance hit is nothing compared to having a deadlock problem.

thanks for reading.
Post #1033731
Posted Monday, December 13, 2010 6:08 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, May 23, 2014 1:26 PM
Points: 236, Visits: 410
Great article Geoff!

I have used a trace-based approach in previous jobs to accomplish several monitoring tasks. It is basically as follows:

1.- Create a trace to capture whatever information you want to 'audit'
2.- After 5 minutes (or whatever amount of time you like), close the running trace and open a new one
3.- Inspect the previously closed trace and if it's not empty send an email notification, you might want to attach the .trc file
4.- Repeat from step 2


One of the disadvantages is that one is not notified right after the event occurred, but some minutes later. In the case of deadlocks I'd rather read the graphical version of the deadlock graph instead of the info that is dumped in the error log, but it's just a matter of personal preferences.
Post #1033742
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse