Generate Deadlock Summary Information

  • mickegohle

    SSC Enthusiast

    Points: 174

    EXEC master.dbo.xp_readerrorlog 0, 1, N'deadlock victim=process', NULL, NULL, NULL, N'desc'

    Both work in my environment you'd also have to change the Deadlock victim table fields to nvarchar as well not sure whether this will fix it.

    [/quote]

    Thanks, it was the N that made it work 🙂

  • gasegid

    SSC Rookie

    Points: 43

    yes you N

  • gasegid

    SSC Rookie

    Points: 43

    There are deadlocks, however when I run the below query to get the impacted table, there is no result;

    --Deadlocks By Day and No

    SELECT CAST([LogDate] AS DATE) [LogDay] ,

    [TableAffected] ,

    COUNT([TableAffected]) No

    FROM [DBA].[dbo].[DeadlockTable]

    GROUP BY CAST([LogDate] AS DATE),[TableAffected]

    ORDER BY CAST([LogDate] AS DATE),[TableAffected];

    --Summary of impacted tables

    SELECT [TableAffected] ,

    COUNT([TableAffected]) No

    FROM [DBA].[dbo].[DeadlockTable]

    GROUP BY [TableAffected]

    ORDER BY COUNT([TableAffected]) DESC

  • Carolyn Richardson

    SSCrazy Eights

    Points: 8194

    Just checking you ran the stored procs that populate the table you are querying?

    Facts are stubborn things, but statistics are more pliable - Mark Twain
    Carolyn
    SQLServerSpecialists[/url]

  • adey11

    SSC Veteran

    Points: 282

    Thank you for thisd tool Carolyn.

    Why does the stored procedure usp_RefreshDeadockObject search for "objectlock lockPartition=0" please, and why does this not appear in my SQL Errorlog with the required trace files in place? I am receiving deadlock output, but this string is absent in all of them. I have 16 CPU's but no partitioned tables (Standard Edition).

  • steveyeadon

    SSC Rookie

    Points: 28

    Thanks for an interesting article. Is there a reason why you didn't use extended events. You can use extended events to capture deadlock graphs and additional details, you could also write some of that data to a histogram to get a summary.

  • Carolyn Richardson

    SSCrazy Eights

    Points: 8194

    Lock Partitioning is a feature in SQL Server that is automatically enabled for machines with more than 16 logical processors – aimed at reducing lock contention and cannot be disabled in these cases. 'objectlock lockPartition=0' was set in my case as I have less than 16 processors so this will be something that needs to be adjusted in the the Stored Proc in these circumstances.

    Facts are stubborn things, but statistics are more pliable - Mark Twain
    Carolyn
    SQLServerSpecialists[/url]

  • ilyakhil

    Grasshopper

    Points: 24

    Hi Carolyn,

    I like your post and decided to implement this approach in my environment.

    Only on e difficulty I got when tested your procedures.

    EXEC master.dbo.xp_readerrorlog 0, 1, N'objectlock lockPartition=0',

    NULL, NULL, NULL, N'desc';

    returned nothing for any log file.

    I'm monitoring my system with "Idera" and I'm getting notifications there are some deadlocks.

    Could you please clarify I need to use N'objectlock lockPartition=0' lock partition=0 in any case?

    Did you test your script yourself?

  • Carolyn Richardson

    SSCrazy Eights

    Points: 8194

    https://www.mssqltips.com/sqlservertip/1476/reading-the-sql-server-log-files-using-tsql/%5B/url%5D

    Gives a good explanation of the undocumented proc sp_readerrorlog and the parameters:-

    CREATE PROC [sys].[sp_readerrorlog](

    @p1 INT = 0,

    @p2 INT = NULL,

    @p3 VARCHAR(255) = NULL,

    @p4 VARCHAR(255) = NULL)

    AS

    BEGIN

    IF (NOT IS_SRVROLEMEMBER(N'securityadmin') = 1)

    BEGIN

    RAISERROR(15003,-1,-1, N'securityadmin')

    RETURN (1)

    END

    IF (@p2 IS NULL)

    EXEC sys.xp_readerrorlog @p1

    ELSE

    EXEC sys.xp_readerrorlog @p1,@p2,@p3,@p4

    END

    This procedure takes four parameters:

    1. Value of error log file you want to read: 0 = current, 1 = Archive #1, 2 = Archive #2, etc...

    2. Log file type: 1 or NULL = error log, 2 = SQL Agent log

    3. Search string 1: String one you want to search for

    4. Search string 2: String two you want to search for to further refine the results

    So EXEC master.dbo.xp_readerrorlog 0, 1, N'objectlock lockPartition=0',NULL, NULL, NULL, N'desc'; 0,1 will look in the current active error log then for the string 'objectlock lockPartition=0' and no further results. Look in your log see what you should be looking for filter the log on objectlock lockPartition see what your log gives you and amend the procedure when I filter the log as per objectlock lockPartition=0 I get many results unfortunately.

    Facts are stubborn things, but statistics are more pliable - Mark Twain
    Carolyn
    SQLServerSpecialists[/url]

  • ilyakhil

    Grasshopper

    Points: 24

    Hi Carolyn,

    Thank you for your explanation but I think you are only partially correct.

    objectlock lockPartition=0 works only partially and cannot find all deadlock situations.

    Please look at: https://readuncommitted.com/2012/07/07/deadlocks-involving-lock-partitions/

    for details.

    Any way thanks for the very interesting post.

    I found a lot of information looking for the answers.

    Thanks

    Ilya

  • Michelle Mueller

    SSC Rookie

    Points: 26

    I was really excited when I saw this article back in December.  And then much less so when these scripts never populated the tables, even when I was in the middle of a deadlock.  Now that I have finally found the time to get back to this, it would be helpful if you corrected the post so that the correct scripts are in it rather than us needing to fix it ourselves. It took a month an a half for me to find the time to get back to this, research master.dbo.xp_readerrorlog, and fix the script so it works. I believe most of the errors are already pointed out in the comments here. However, one of the scripts has the spelling error of "deadock" instead of "deadlock".  

    Additionally, your script is missing the deadlock type of "keylock." This is my most common type of deadlock, so I found a lot of these when I inspected my entire error log.

Viewing 11 posts - 16 through 26 (of 26 total)

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