How to receive Deadlock information automatically via email.

  • chuckh 3191

    SSC Rookie

    Points: 40

    I'm trying to remember. Do you have to turn back on DBCC TRACEON each time you restart the instance?

  • Geoff A

    SSChampion

    Points: 11417

    chuckh 3191 (12/14/2010)


    I'm trying to remember. Do you have to turn back on DBCC TRACEON each time you restart the instance?

    yes.

    from the article -

    It is important to note that setting trace flags this way will only work until the next time SQL Server is restarted. If you want to make sure your trace flags are always set this way, you can edit the startup options of SQL Server by using the -T switch or creating a SQL Agent job to run that code when the Agent starts.

  • Arman Khan

    SSC Eights!

    Points: 864

    Great post,

    But i have a problem that i am receving the emails with empty Attachment and also what is the Schedule for Sql Agent Job?

    l

    ogdate procInfo ERRORLOG

    ----------------------- ---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    (0 rows affected)

  • shotman

    Right there with Babe

    Points: 769

    Doesn't enabling deadlock graph take performance hit on the system.....true or not? As on our systems i have seen some performance hit when deadlock graph is enabled.

  • Geoff A

    SSChampion

    Points: 11417

    yes, there is a very minor hit, but consider the alternative. everything comes at a cost. would you rather have unknown deadlocks or (IMHO) a barely measurable hit in performance?

  • gary1

    SSC-Insane

    Points: 21148

    reat post,

    But i have a problem that i am receiving the emails with empty Attachment and also what is the Schedule for Sql Agent Job?

    l

    I followed the steps discussed in the article and everything works great but getting EMPTY ATTACHMENT in the email when deadlock occurs.!! I'm able to see the dead lock graph in the errorlog but not getting the email attachment.

    please see the attachment

  • Geoff A

    SSChampion

    Points: 11417

    what happens if you just run this code?

    see attachment....

  • gary1

    SSC-Insane

    Points: 21148

    I ran the below code, but got NO results

    IF OBJECT_ID('tempdb.dbo.ErrorLog') IS Not Null

    BEGIN

    DROP TABLE tempdb.dbo.ErrorLog

    END

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

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

    INSERT INTO tempdb.dbo.ErrorLog

    EXEC master.dbo.sp_readerrorlog

    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)

    DROP TABLE tempdb.dbo.ErrorLog

    Results:

    logdate procinfo ERRORLOG

    Note: I do have the Deadlock graph in SQL Server errorlog. but the above query is not fetching that deadlock details.

    Please advice

  • gary1

    SSC-Insane

    Points: 21148

    I got the results:

    we just need to change the below in your query:

    select logdate, procInfo, ERRORLOG from tempdb.dbo.ErrorLog where Id >= (select TOP 1 Id from tempdb.dbo.ErrorLog WHERE ERRORLOG Like '%deadlock-list%' order by Id DESC)

    thanks

  • gary1

    SSC-Insane

    Points: 21148

    Geoff,

    Do you have similar kind of script to notify when blocking occurs?

    We are experiencing blocking and do not have script to notify. please advice..Thanks

  • Geoff A

    SSChampion

    Points: 11417

    sure, but blocking will need to be a timed query Ie, via SQl Server Agent....

    but you could run a script like in the attachment every so often and send the results, (if there are any) to an email address....

    see attachment...

  • JayK

    SSCrazy

    Points: 2679

    I still cant get this script to work with an SQL SERVER AGENT job - I am trying to solve it over on this forum post but still no luck...

    http://www.sqlservercentral.com/Forums/FindPost1042873.aspx

    did anyone else have the same issue?

  • Geoff A

    SSChampion

    Points: 11417

    based on that other thread, and all the info that is there, can you try this;

    DECLARE @sqlquery nvarchar(250)

    SET @sqlquery = '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)'

    exec (@SQLQuery)

    and tell me if that work on the server in question...

  • JayK

    SSCrazy

    Points: 2679

    Hi Geoff,

    Thanks for getting back. Yes that works without a problem. The issue is this statement:

    EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'default',

    @recipients = 'xxxxx@xxxxxxx',

    @subject = @mysubject,

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

    @query = @sqlquery,

    @query_result_width = 600,

    @attach_query_result_as_file = 1

    The job falls over when it comes to execute the sp_send_dbmail command.

  • Geoff A

    SSChampion

    Points: 11417

    Maybe you can try to;

    Grant execute on sp_send_dbmail to <user>

Viewing 15 posts - 16 through 30 (of 70 total)

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