Generate Deadlock Summary Information

Question

Generate Deadlock Summary Information

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

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

mickegohle SSC Veteran Points: 234 More actions · Answer 1

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 More actions · Answer 2

gasegid

SSC Rookie

Points: 43

December 15, 2016 at 12:01 pm

#1918266

yes you N

gasegid SSC Rookie Points: 43 More actions · Answer 3

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: 8357 More actions · Answer 4

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 More actions · Answer 5

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 More actions · Answer 6

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: 8357 More actions · Answer 7

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 More actions · Answer 8

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: 8357 More actions · Answer 9

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 More actions · Answer 10

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 More actions · Answer 11

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.