samuel_yanzu (6/16/2008)
I concur! I am having major problems with one of your applications at the moment and this will be a good way determining whether deadlocks are correlating to any of the timeouts and MS Access "not responding" screens that they are experiencing. I hope it is then I can look forward to getting the developers to clean up their dodgy code! 😀
Sounds like you are having long duration blocking, not deadlocks. If a deadlock occurs, one of the session involved in the deadlock will get an error along the lines of 'Your session was involved in a deadlock and was picked as the deadlock victim'. The other session will probably not notice anything.
Check the sys.dm_exec_sessions and sys.dm_exec_requests DMVs and see if you have a lot of sessions with a blocked_by that's not 0
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