Friday, May 24, 2013

Dynamics GP - SQL - Transaction was deadlocked on lock resources with another process and has been chosen as the deadlock victim

To identify deadlocks, run this script.
However, you may have to run this before you get the error message, as the deadlock will have already been removed by the time you see the error message.
Attempt to replicate the lock, and run the script to see what's being locked.


--------------------------------------------------------------------------------------------------
select
 db_name(sp.dbid) as database_name, d.text as sql_text, spid, blocked, cmd, sp.waittime, hostname, program_name,loginame, *
 from master.sys.sysprocesses sp OUTER APPLY sys.dm_exec_sql_text(sql_handle) D
 where sp.blocked <> 0
 union all
 select db_name(sp.dbid) as database_name, d.text as sql_text, spid, blocked, cmd, sp.waittime, hostname, program_name,sp.loginame, *
 from master.sys.sysprocesses sp OUTER APPLY sys.dm_exec_sql_text(sql_handle) D
 where blocked = 0 and spid in (select blocked from master.sys.sysprocesses where blocked <> 0)
 order by sp.waittime desc
-------------------------------------------------------------------------------------------------------------------
Original post

No comments:

Post a Comment