[AccessD] Identifying SQL Server blocks/deadlocks?

[Ryan W]

Occasionally I'll get reports from my users that the system is "spinning".
Sometimes it clears itself, sometimes I run sp_who2 and will just kill off
the blocking SPID.

I've asked the users with the blocking SPID if they can tell me what they
were doing and as you expect from an end user you get a vague answer if
they recall at all.


Is there a way to identify blocking transactions post mortem?  I would
think SQL Server would be smart enough to log some of this but all I'm
seeing is "how to identify as it's happening" sort of scenarios.

I'm still on SQL Server 2008, so some of the code snippets I've found don't
work on my version.  I know I need to upgrade but I have been concerned
about query performance changes with newer versions.. but that's an
entirely different subject for later.


TIA.