deadlock issues in sql server

Question

Hi Team,

SQL server 2008R2(ofcourse EOL).

I got deadlock in errorlog but unable to identify the login name,hostname,programe which is caused-

how we can identify those information's, the alert we come to know after 20mins but tried from sys.dm_exec_connections,sessions,requests,threads,locks.

2022-03-08 13:34:00.170 spid91 Error: 1205, Severity: 13, State: 51.
2022-03-08 13:34:00.170 spid91 Transaction (Process ID 91) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
2022-03-08 13:34:00.170 spid28s deadlock-list
2022-03-08 13:34:00.170 spid28s deadlock victim=process6644bc8
2022-03-08 13:34:00.170 spid28s process-list
2022-03-08 13:34:00.170 spid28s process id=process6644bc8 taskpriority=0 logused=1104 waitresource=KEY: 6:72057596670902272 (f002f9b9e5b2) waittime=144 ownerId=2257917463 transactionname=user_transaction lasttranstarted=2022-03-08T13:34:00.030 XDES=0x800151d0 lockMode=S schedulerid=7

2022-03-08 13:34:42.680 spid138 Transaction (Process ID 138) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
2022-03-08 13:34:42.680 spid18s deadlock-list
2022-03-08 13:34:42.680 spid18s deadlock victim=process66c7948
2022-03-08 13:34:42.680 spid18s process-list
2022-03-08 13:34:42.680 spid18s process id=process66c7948 taskpriority=0 logused=1104 waitresource=KEY: 6:72057596670902272 (af0277cdb720) waittime=1541 ownerId=2258060996 transactionname=user_transaction lasttranstarted=2022-03-08T13:34:41.137 XDES=0x4a1a87f010 lockMode=S schedulerid
2022-03-08 13:34:42.680 spid18s executionStack

please assist on this.

Answer 1

Usually, the deadlock trace in the errorlog has more information. You should see all processes, and also a resource list. And, yes, client hostname and application name are usually included. At least in more modern versions. You should also see the commands submitted and the execution stack.

The best way to capture deadlocks is the system_health session, but this session is not available on SQL 2008. I think in your case the best is to set up a server-side trace that captures the event Lock:DeadlockGraph. Just don't look at the graphical representation that Profiler shows, because that information dumbed down and misses important important information. For instance, the fact that these deadlocks occur inside a transaction. (So you need to account for previous statements as well.)

Answer 2

resource lists everything exists but I dont see anything hostname,login,program name details,tried using XML no luck,is there anyway using those resouce lists can be identify?.

2022-03-08 13:34:00.180 spid28s Proc [Database Id = 6 Object Id = 580613557]
2022-03-08 13:34:00.180 spid28s resource-list
2022-03-08 13:34:00.180 spid28s keylock hobtid=72057596670902272 dbid=6 objectname=XXXXXindexname=ix_depmodeldata_modelguid id=lock68de859180 mode=X associatedObjectId=72057596670902272
2022-03-08 13:34:00.180 spid28s owner-list
2022-03-08 13:34:00.180 spid28s owner id=process669ee08 mode=X
2022-03-08 13:34:00.180 spid28s waiter-list
2022-03-08 13:34:00.180 spid28s waiter id=process6644bc8 mode=S requestType=wait
2022-03-08 13:34:00.180 spid28s keylock hobtid=72057596861022208 dbid=6 objectname=XXXXXXXXXXXXXXX indexname=ix_
uid id=lock41d4160d80 mode=U associatedObjectId=72057596861022208
2022-03-08 13:34:00.180 spid28s owner-list
2022-03-08 13:34:00.180 spid28s owner id=process6644bc8 mode=U
2022-03-08 13:34:00.180 spid28s waiter-list
2022-03-08 13:34:00.180 spid28s waiter id=process669ee08 mode=U requestType=wait

Answer 3

Hi @S_NO ,

Suggest you using the scripts from below blog to get the detail information about deadlock. The columns of the table are mentioned below for your reference and explanation:

DeadLockDateTime
This is the time when the actual deadlock happened. The trace might take couple of minutes to populate the deadlock but the time will be accurate.

HostName
The machine name which is accessing the data. This is the application server or the client application machine name. This is important to identify the system which is trying to access the data.

LoginName
The SQL/Windows authenticated login name which is used to access the database. This is critical to identify which login is being used to access the data.

ClientApp
The client application name will be provided in this column. It will be like SQL Server Management Studio or the dot net framework application or whichever application you are using to get the data to the user.

VictimTSQL
This is the TSQL for the session which was chosen as the deadlock victim and didn’t complete its processing.

ProcessTSQL
This is the TSQL for the process which was executed successfully but caused the deadlock to happen.

Please refer to blog Monitoring SQL Server Deadlocks – the easy way.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".