sql command

NavStudent

Hello
I would like to know how to find who has locked a table on sql. A client has asked that they get an error that another user has locked the table, but they do not not know who. So they would like to find out who locked the table. Is there a command or stored procedure that you can run on SQL? I searched around and found the following code

SELECT spid 'SPID',
blocked 'Blocked By',
sp.waittype 'Wait Type',
sp.open_tran 'Open Trans',
sp.status 'Status',
sp.waittime 'Wait Time',
cmd 'Command',
sl.name 'Login',
program_name'Program'
FROM master..sysprocesses AS sp
JOIN master..syslogins AS sl ON sp.sid = sl.sid
WHERE (blocked != 0
and blocked != spid)
OR spid IN (SELECT blocked FROM master..sysprocesses WHERE blocked != 0 and blocked != spid)
ORDER BY blocked, spid

This code works, if one user is locked by another, otherwise it does not display any records.

I also found this command

select * FROM master..sysprocesses where status='sleeping' AND waittype=0x000 AND open_tran > 0

but it just gives navision users, and does tell which table or recordset is locked.

There is SP_lock but it gives SID and lock type.
Thank you in advance.

DenSter

There's a tool called 'session monitor' that can help you find the locks. It is on the NAV 4.0 tools CD. You'll need a developer license to run it though, so you're going to have to get help from your solution center.

The other tool is the SQL Profiler. Run a new trace on the server and report the locks. This will give you locking IDs and locked IDs, which you can then map to users in the sessions form. From NAV, click file/database/information and click the sessions tab. There you'll find the Connection ID.

Now you have the locker and the lockee, and you can figure out what they were doing, and you can work on optimizing those processes.

frede

In the virtual table Session in Navision you should be able to look up the user who complaints about the locking - and here look in the field "Blocking User ID" - here you should be able to see who is locking the user.

Do this until there field is blank - and you will end up at the user starting the blocking!

Also you can see what object is active when doing the blocking!

This is all theory - as I am not working on a SQL Navision at this time...

(page 126 in w1w1adg.pdf).

NavStudent

DenSter wrote:

There's a tool called 'session monitor' that can help you find the locks. It is on the NAV 4.0 tools CD.

Could you put a link for this NAV 4.0 tools CD?

NavStudent

DenSter wrote:

The other tool is the SQL Profiler. Run a new trace on the server and report the locks. This will give you locking IDs and locked IDs, which you can then map to users in the sessions form. From NAV, click file/database/information and click the sessions tab. There you'll find the Connection ID.

Can the SQL Profile trace who is locking what after the fact has happened? or you u have to run this and minor when it happens next time?

NavStudent

frede wrote:

In the virtual table Session in Navision you should be able to look up the user who complaints about the locking - and here look in the field "Blocking User ID" - here you should be able to see who is locking the user.

I tested this and it works, except there is 10 sec lock timeout, so you have to do as fast as possible, which means you have to run the same process and find out who locked you out. I guess this is doable. I have to see what the 4.0 Tools has that would show me that I can run a routine that would tell me who has locked what table at that moment.

DenSter

NavStudent wrote:

Could you put a link for this NAV 4.0 tools CD?

It's on partnersource under product releases, I don't have a link handy right now.

NavStudent wrote:

Can the SQL Profile trace who is locking what after the fact has happened? or you u have to run this and minor when it happens next time?

The SQL Profiler trace logs SQL Server events, and you can save the log, import it to Excel, analyze it later. Read all about it in Books Online (Start,All programs, SQL Server, Books Online, and search for SQL Profiler).