How to Determine Who Has a SQL Server Record Locked

[EddieN1]

We're running SQL Server 2012. On occasion, an operator will lock a record and when another operator tries to update the same record, the system throws a Runtime Error. We can't find out who has the record locked and have to wait overnight before the record gets unlocked. Is there a way to determine, using VBA, to determine who has it locked. Thanks, Eddie

[June7]

I have no idea if what you want is possible.

What you can do is track who might be interacting with the db. When user opens the frontend, create a login record (I just update a field in Users table) for that user. When they close the db, create a logout record (I just update same field setting it to null).

I read something about avoiding most occurrences of error by setting LOCK MODE to "WAIT". Since I don't use SQLServer, no idea how nor if that would really accomplish anything.

Here is how one developer solved issue https://www.sqlservercentral.com/For...930-131-1.aspx

[EddieN1]

What I'm looking for is when a record locked condition is encountered, I'd like to issue a SQL Server function that will return the user ID of the operator who holds the record lock. Any other ideas?

[NoellaG]

Hi,

if traceon 1204 or 1222 is turned on you can find the deadlocks in the SQL server logs in the management studio. You can turn these on with the SQL commands
DBCC traceon (1222, -1)
or
DBCC traceon(1204, -1)

In the SQl server log files you'll find the spid that causes the deadlock. Use sp_who to get the login name.

Kind regards
NoellaG