[AccessD] Identifying SQL Server blocks/deadlocks?

[Anita Smith]

Hi Ryan,

I have the same issue since rewriting my app to use views in lieu of stored procedures. It has happened twice in 6 months and both times during the busy time of month. I have added timestamp fields in my tables and since then I have not had the problem, although this is not an indication that it has resolved - yet.

I have a stored procedure that identifies the lock. I can then kill the process that is causing the lock.

You can read about it here:

https://everyething.com/how-to-release-or-remove-lock-on-a-table-SQL-server


Anita


-----Original Message-----
From: AccessD <accessd-bounces+anita=ddisolutions.com.au at databaseadvisors.com> On Behalf Of Randall Anthony
Sent: Wednesday, 1 June 2022 05:08
To: 'Access Developers discussion and problem solving' <accessd at databaseadvisors.com>
Subject: Re: [AccessD] Identifying SQL Server blocks/deadlocks?

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

--
AccessD mailing list
AccessD at databaseadvisors.com
https://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com