[AccessD] Identifying SQL Server blocks/deadlocks?

Tue May 31 21:18:17 CDT 2022

Ryan,

Let me know how you go.

I will add that I have not had to use my form yet as it has not happened since I created it. I set it all up using the scenario in the link I gave you where a transaction was started but not committed thus causing the lock.

Anita

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

Thanks. I think I’ll draw inspiration from this and make the spid killable via a form. 

IIRC I did have to grant special permissions to my users to use sp_who2 even when calling from a SP. 

Sent from my iPhone

> On May 31, 2022, at 8:49 PM, Anita Smith <anita at ddisolutions.com.au> wrote:
> 
> The sp that kills the process is just a regular stored procedure that runs on the database - not master. It requires no special permissions and accepts the ID of the Process to kill.  I run it from the front end the same as my other stored procedures.
> 
> 
> Anita
> 
> -----Original Message-----
> From: AccessD 
> <accessd-bounces+anita=ddisolutions.com.au at databaseadvisors.com> On 
> Behalf Of Ryan W
> Sent: Wednesday, 1 June 2022 11:36
> To: Access Developers discussion and problem solving 
> <accessd at databaseadvisors.com>
> Subject: Re: [AccessD] Identifying SQL Server blocks/deadlocks?
> 
> I’ve got a hot key a couple users have been instructed they can use to find the locked SPID and go chase down the user on the host name listed to either get a glimpse or what they were doing / and close the access FE. 
> 
> I do like your idea of being able to kill the SPID. Did you wrap that in a SPROC with temporary rights elevation? Would love to hear more on that. 
> 
> Sent from my iPhone
> 
>> On May 31, 2022, at 8:13 PM, Anita Smith <anita at ddisolutions.com.au> wrote:
>> 
>> 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
>> --
>> 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
--
AccessD mailing list
AccessD at databaseadvisors.com
https://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com