[AccessD] Identifying SQL Server blocks/deadlocks?

Wed Jun 1 07:19:38 CDT 2022

Anita,
  How are you granting rights to users to use the DMV in the link ? I've
tried to wrap it in a SPROC with 'execute as' higher level permissions but
I'm getting a "user does not have permission to perform this action"
messages.

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

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