[AccessD] Identifying SQL Server blocks/deadlocks?

[Anita Smith]

Hi Ryan,

My application is for a business that process sales all day every day. When this happens it's a major, major, major disaster.

I have created a form that when opened displays the result of a stored procedure that finds locks (as can be seen in my earlier link). I have a textbox where the user can enter the number of the process to kill.

This is a last resort and is only available to power users that have been properly instructed. The stored procedure that finds the locks returns nothing if there are no locks so it is not a major deal to let the user kill a process. 

I would just like to find out what causes this problem though. Twice in 6 months is not major but still a considerable concern of mine.


Anita


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

Hey Anita,
  Thanks for the link. I'll check it out. I'm using sp_who2 as well as sp_whoIsActive (https://github.com/amachanic/sp_whoisactive/releases) and another query I found online.  The issue is sometimes I am not around to quickly kill a process and would love some logging as to not only WHO but what query or transaction is holding a lock.

  I use a mix of views, stored procedures on the back end as well as querydefs/ad-hoc queries either done programatically or stored in a forms recordsource property.

  As I said, sometimes the lock resolves itself and I've had a couple times where it sounds like they were deadlocked for a half hour or more until I got in and killed the offending SPID at that time.  Unfortunately I haven't had time to examine what the SPID is doing as "time is money" and downtime is pretty much unacceptable.

 Most if not all of my server side tables also have a Timestamp column, but perhaps I need to do some more checking to see if some are missing.


On Tue, May 31, 2022 at 7:07 PM Anita Smith <anita at ddisolutions.com.au>
wrote:

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