How to receive Deadlock information automatically via email.

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Geoff Albin » How to receive Deadlock information...

56 posts, Page 1 of 6

1 2 3 4 5 »»»

How to receive Deadlock information automatically via email.

Rate Topic

Display Mode

Topic Options

Author

Message

Geoff A

Posted Saturday, December 11, 2010 2:04 PM

SSC-Addicted

Group: General Forum Members
Last Login: Monday, May 20, 2013 7:10 AM
Points: 485, Visits: 1,568

Comments posted to this topic are about the item How to receive Deadlock information automatically via email.

Post #1033380

Gaurav Gosain-395499

Posted Sunday, December 12, 2010 10:19 PM

Valued Member

Group: General Forum Members
Last Login: Friday, May 17, 2013 4:38 AM
Points: 66, Visits: 68

Thanks Geoff.

This is great and very helpful post.

BigGrin

dude you rocks Hehe

Thanks,
GG Wink

Post #1033587

valiya.saheb

Posted Monday, December 13, 2010 12:12 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, December 14, 2010 9:31 AM
Points: 1, Visits: 5

Excellent Geoef..Rocks Smile

Post #1033605

mreizik

Posted Monday, December 13, 2010 12:38 AM

Forum 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

alxdean

Posted Monday, December 13, 2010 2:14 AM

Forum 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

sharath.chalamgari

Posted Monday, December 13, 2010 2:51 AM

Ten Centuries

Group: General Forum Members
Last Login: Monday, September 17, 2012 7:30 AM
Points: 1,038, Visits: 679

Nice article Geoff, really usefull

Post #1033658

SuperDBA-207096

Posted Monday, December 13, 2010 4:16 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, January 02, 2013 12:15 PM
Points: 1,443, Visits: 711

Nice work! This is useful!

Post #1033690

Geoff A

Posted Monday, December 13, 2010 5:52 AM

SSC-Addicted

Group: General Forum Members
Last Login: Monday, May 20, 2013 7:10 AM
Points: 485, Visits: 1,568

alxdean (12/13/2010)

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

Geoff A

Posted Monday, December 13, 2010 5:53 AM

SSC-Addicted

Group: General Forum Members
Last Login: Monday, May 20, 2013 7:10 AM
Points: 485, Visits: 1,568

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

F. Dwarf

Posted Monday, December 13, 2010 6:08 AM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 2:45 PM
Points: 236, Visits: 377

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 »

56 posts, Page 1 of 6

1 2 3 4 5 »»»

Permissions