July 30, 2009 at 3:35 am
From time to time we experience a deadlock on our server.
I use DBCC Inputbuffer(spid) to see the query causing the deadlock.
It is mostly a complex UPDATE executed by some user, but what bugs me is this:
when I run sp_who2, it indicates that the blocking process is"sleeping". So it is done, but why will it not release its hold on the table?
What could be possible reasons for this?
July 30, 2009 at 4:00 am
Sleeping just means not currently running a query. It's easy for a sleeping connection to still be holding locks if a transaction it ran earlier wasn't committed.
Switch traceflag 1222 on. That will result in a deadlock graph been written to the error log every time a deadlock occurs. Post the result of that graph here.
DBCC TRACEON(1222,-1)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply