Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to receive Deadlock information automatically via email.


How to receive Deadlock information automatically via email.

Author
Message
Geoff A
Geoff A
SSChasing Mays
SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)

Group: General Forum Members
Points: 606 Visits: 1791
Comments posted to this topic are about the item How to receive Deadlock information automatically via email.
Gaurav Gosain-395499
Gaurav Gosain-395499
Valued Member
Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)

Group: General Forum Members
Points: 68 Visits: 70
Thanks Geoff.

This is great and very helpful post.

:-D
dude you rocksHehe

Thanks,
GG;-)
valiya.saheb
valiya.saheb
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 11
Excellent Geoef..Rocks:-)
mreizik
mreizik
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 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
alxdean
alxdean
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 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.
sharath.chalamgari
sharath.chalamgari
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1256 Visits: 798
Nice article Geoff, really usefull
SuperDBA-207096
SuperDBA-207096
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1477 Visits: 711
Nice work! This is useful!
Geoff A
Geoff A
SSChasing Mays
SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)

Group: General Forum Members
Points: 606 Visits: 1791
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.
Geoff A
Geoff A
SSChasing Mays
SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)

Group: General Forum Members
Points: 606 Visits: 1791
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.
F. Dwarf
F. Dwarf
SSC Veteran
SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)

Group: General Forum Members
Points: 243 Visits: 559
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search