Locked File Search & Fix 1

[AKPolarBear]

We use 7.6.100a and MS SQL.

Occasionally we get the dreaded "Locked File" error message on everyone's screen and then everything comes to screeching halt. I say screeching because that is all I can here from the users, screeching.

In betrive, it was easy to locate a locked file and deal with it. In SQL, that is not the case. It is almost faster to go to each user and check their screen than to search for it in SQL. But with distant branch locations, that is not practical. I only have limited knowledge of SQL so maybe I am missing a tool that I could use.

Does anybody have a faster method to find a locked record? I was even thinking of possibly creating a Crystal report to help do the job.

 

[dbjunkie]

AKPolarBear,

You can create a SQL script file and run it in Query Analyzer to track down a lock placed on a specific table.
Try this for your script.


use msllockdb
go
select sessionid, tablename, rowid from maclocks
exec sp_who

 

[kscheeler]

An addition to that one would be this that will combine the sp_who which shows all of the users by spid along with the current locks.

select cast(l.sessionid as char(5)) as Session,
cast(m.loginame as char(20)) as login_name,
cast(m.hostname as char(20)) as computer,
cast(l.dbname as char(20)) as [db_name],
cast(l.tablename as char(20)) as table_name,
l.rowid as record
from msllockdb..maclocks l left outer join master..sysprocesses m on
l.sessionid = m.spid
order by m.loginame, l.dbname, l.tablename

Kevin Scheeler

 

[AKPolarBear]

Thanks to you both for your answers. They both will identify the locked record and process but Kevin's query does exactly what I was looking for and also identifies the user by name. With that, I can shadow their session and try and correct the issue from the front end rather than just killing it and cleaning up the mess.