[AccessD] Identifying SQL Server blocks/deadlocks?

[Randall Anthony]

Ryan,
DBCC TRACEON (3605)
DBCC TRACEON (1204)
The trace flag sends trace output to the error log \mssql\Log\Errorlog

HTH.

-----Original Message-----
From: AccessD <accessd-bounces+randall.anthony=cox.net at databaseadvisors.com>
On Behalf Of Ryan W
Sent: Tuesday, May 31, 2022 2:37 PM
To: Access Developers discussion and problem solving
<accessd at databaseadvisors.com>
Subject: [AccessD] Identifying SQL Server blocks/deadlocks?

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.
--
AccessD mailing list
AccessD at databaseadvisors.com
https://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


-- 
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus