Post reply

How to receive Deadlock information automatically via email.

  • December 11, 2010 at 2:04 pm

    #232018

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

  • December 12, 2010 at 10:20 pm

    #1261519

    Thanks Geoff.

    This is great and very helpful post.

    😀

    dude you rocks:hehe:

    Thanks,
    GG;-)

  • December 13, 2010 at 12:12 am

    #1261534

    Excellent Geoef..Rocks:-)

  • December 13, 2010 at 12:38 am

    #1261545

    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

  • December 13, 2010 at 2:14 am

    #1261570

    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.

  • December 13, 2010 at 2:51 am

    #1261581

    Nice article Geoff, really usefull

  • December 13, 2010 at 4:16 am

    #1261605

    Nice work! This is useful!

  • December 13, 2010 at 5:52 am

    #1261637

    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.

  • December 13, 2010 at 5:53 am

    #1261638

    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.

  • December 13, 2010 at 6:08 am

    #1261649

    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.

  • December 13, 2010 at 8:21 am

    #1261738

    Nice post, especially since it works from 200 up.

    With 2005 and later you should look into Event Notifications or with 2008 and later Extended Events. These can make this type of monitoring simpler once you understand how they work.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • December 13, 2010 at 9:47 am

    #1261804

    Thanks for the article.After reboot, I tried going through the steps but when I set up the alert at the end, I get the following error message:

    The specified object_name('SQLServer:Loks') does not exist. (Microsoft SQL Server, Error: 14262)

  • December 13, 2010 at 12:05 pm

    #1261881

    Hey Geoff. Thanks for pointing that out. I just followed up the limitations due to the query happening on a different session. Have you tried it with Global Temporary tables using the double hash syntax? ##tablename?

    regards,

    Alex

  • December 13, 2010 at 3:24 pm

    #1261963

    Thanks for the nice resource.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • December 13, 2010 at 3:35 pm

    #1261966

    When I run the sql contained in the job within the QA it works Ok and I get a mail. However when I try and execute the job itself I get the following error:

    Error formatting query, probably invalid parameters [SQLSTATE 42000] (Error 22050). The step failed.

    SQL with email just masked:

    CREATE TABLE tempdb.dbo.ErrorLog (Id int IDENTITY (1, 1) NOT NULL,

    logdate DATETIME, procInfo VARCHAR(10), ERRORLOG VARCHAR(MAX))

    --== We create a 3 column table to hold the contents of the SQL Server Error log. ==--

    --== Then we insert the actual data from the Error log into our newly created table. ==--

    INSERT INTO tempdb.dbo.ErrorLog

    EXEC master.dbo.sp_readerrorlog

    --== With our table created and populated, we can now use the info inside of it. ==--

    BEGIN

    --== Set a variable to get our instance name. ==--

    --== We do this so the email we receive makes more sense. ==--

    declare @servername nvarchar(150)

    set @servername = @@servername

    --== We set another variable to create a subject line for the email. ==--

    declare @mysubject nvarchar(200)

    set @mysubject = 'Deadlock event notification on server '+@servername+'.'

    --== Now we will prepare and send the email. Change the email address to suite your environment. ==--

    EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'default',

    @recipients='xxxxxxxxxxxxxxxxxx',

    @subject = @mysubject,

    @body = 'Deadlock has occurred. View attachment to see the deadlock info',

    @query = 'select logdate, procInfo, ERRORLOG from tempdb.dbo.ErrorLog where Id >= (select TOP 1 Id from tempdb.dbo.ErrorLog WHERE ERRORLOG Like ''%Deadlock encountered%'' order by Id DESC)',

    @query_result_width = 600,

    @attach_query_result_as_file = 1

    END

    --== Clean up our process by dropping our temporary table. ==--

    DROP TABLE tempdb.dbo.ErrorLog

    Any ideas? I'm running 2008 R2 but not sure if that should make a difference.Thanks!

Viewing 15 posts - 1 through 15 (of 69 total)

You must be logged in to reply to this topic. Login to reply