Extracting only Deadlock details from ErrorLog

  • SQL_Hunt

    SSC-Dedicated

    Points: 33264

    Comments posted to this topic are about the item Extracting only Deadlock details from ErrorLog

    Thanks.

  • the other mike.stuart

    SSC Enthusiast

    Points: 138

    Sourav,

    I tried testing your script by doing the following:

    - Create one database connection and run the following code:

    IF (SELECT object_id ('tempdb..##tmp1','U')) IS NOT NULL

    DROP TABLE ##tmp1

    CREATE TABLE ##tmp1 (

    col1 INT

    )

    INSERT ##tmp1 (col1) VALUES (0), (1), (2), (3), (4), (5)

    GO

    BEGIN TRAN

    UPDATE ##tmp1 SET col1 = col1 + 10

    - Create a second database connection and run the following code:

    IF (SELECT object_id ('tempdb..##tmp2','U')) IS NOT NULL

    DROP TABLE ##tmp2

    CREATE TABLE ##tmp2 (

    col1 INT

    )

    INSERT ##tmp2 (col1) VALUES (0), (1), (2), (3), (4), (5)

    GO

    BEGIN TRAN

    UPDATE ##tmp2 SET col1 = col1 + 10

    - Go to the first connection and run the following code:

    UPDATE ##tmp2 SET col1 = col1 + 20

    - Go to the second connection and run the following code:

    UPDATE ##tmp1 SET col1 = col1 + 20

    - I got the following message on the second query:

    Msg 1205, Level 13, State 45, Line 1

    Transaction (Process ID 58) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

    I then ran your code and got a Total_DeadLock_Count of 0. I also looked in the error log and there aren't any entries with '%deadlock%' in the text.

    Do you have some other process that writes 'Deadlock encountered .... Printing deadlock information' to the error log?

    Thanks,

    Mike

  • tuzhen007 58438

    Grasshopper

    Points: 20

    you can use turn on this trace flag:

    DBCC TRACEON (1204,-1);

    and then run his scripts.

  • Lee Linares

    SSCrazy

    Points: 2662

    In your query you have this WHERE clause: select count('x') Total_DeadLock_Count from #error where etext like '%Deadlock encountered ....... Printing deadlock information%'

    This causes it to return 0. There are too many periods in the text searched. This should match the code you use later in the script:

    if charindex('Deadlock encountered .... Printing deadlock information',@etext,1)>0

    Notice that there are only 4 periods in the 2nd statement instead of the 7 periods in the WHERE clause.

    Thanks for sharing your code.

    Lee

  • DesNorton

    SSC-Insane

    Points: 22636

    You could also pass the filter directly to xp_readerrorlog.

    See this post ... http://www.mssqltips.com/sqlservertip/1476/reading-the-sql-server-log-files-using-tsql/

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Another good script, thanks.

  • NBSteve

    Hall of Fame

    Points: 3212

    DesNorton (3/6/2016)


    You could also pass the filter directly to xp_readerrorlog.

    See this post ... http://www.mssqltips.com/sqlservertip/1476/reading-the-sql-server-log-files-using-tsql/%5B/quote%5D

    While generally a good idea, that would defeat the point of this script. When using the built-in filter, you only retrieve rows matching the filter, which would be the first row with the deadlock message in it. The point of this script is to retrieve all of the following rows which have the details about the deadlock, but don't match the filter.

  • DesNorton

    SSC-Insane

    Points: 22636

    NBSteve (10/24/2016)


    DesNorton (3/6/2016)


    You could also pass the filter directly to xp_readerrorlog.

    See this post ... http://www.mssqltips.com/sqlservertip/1476/reading-the-sql-server-log-files-using-tsql/%5B/quote%5D

    While generally a good idea, that would defeat the point of this script. When using the built-in filter, you only retrieve rows matching the filter, which would be the first row with the deadlock message in it. The point of this script is to retrieve all of the following rows which have the details about the deadlock, but don't match the filter.

    I should have been more clear. You pass NULL, NULL as your text filters, and pass in the start-date as a filter. This eliminates the necessity to load a large amount of data, and then delete chunks of it.

  • NBSteve

    Hall of Fame

    Points: 3212

    DesNorton (10/24/2016)


    ...

    I should have been more clear. You pass NULL, NULL as your text filters, and pass in the start-date as a filter. This eliminates the necessity to load a large amount of data, and then delete chunks of it.

    Whoops, you are correct. I cycle my error logs daily and just glossed over the date filter and assumed you meant a text filter. Thanks for clarifying!

Viewing 9 posts - 1 through 9 (of 9 total)

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