Extracting only Deadlock details from ErrorLog

Question

Post reply

Extracting only Deadlock details from ErrorLog

SQL_Hunt

SSC-Dedicated

Points: 33518
More actions
December 3, 2014 at 5:34 am

#293130

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

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

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

the other mike.stuart SSC Enthusiast Points: 138 More actions · Answer 1

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

you can use turn on this trace flag:

DBCC TRACEON (1204,-1);

and then run his scripts.

Lee Linares SSCrazy Points: 2723 More actions · Answer 3

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

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/

How to post data/code on a forum to get the best help.

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 5

akljfhnlaflkj

SSC Guru

Points: 76202

March 10, 2016 at 4:34 pm

#1864448

Another good script, thanks.

NBSteve Hall of Fame Points: 3985 More actions · Answer 6

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

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.

How to post data/code on a forum to get the best help.

NBSteve Hall of Fame Points: 3985 More actions · Answer 8

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!