[AccessD] Identifying SQL Server blocks/deadlocks?

[Ryan W]

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