Locked tables

[myvision69]

is there a way to find out what tables are locked and by what process?

seems like there is an update somewhere with no commit causing all the users to lock up.

THANKS FOR PROMPT REPLY.

 

[SQLDenis]

sp_who2 look in the BlkBy column

then KILL that spid

also try running sp_lock

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[myvision69]

thanks for prompt reply.

this only gives me active processes but i need to find what tables are locked up so I can troubleshoot the issue?

 

[SQLDenis]

if something is locked you need to kill the process that locked them

if you look in the BlkBy column after running sp_who2 you will see a number
if you need to see the SQL statement you can do DBCC INPUTBUFFER (SPID) where SPID is the number from the BlkBy column

then you have to do
KILL SPID (where SPID is the number from the BlkBy column
) to release the lock

Even if you know that table employees is locked the only way to unlock is is to kill the process that locked it or reboot the machine



Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[mrdenny]

sp_lock will tell you what spids have which locks on which objects. I beleive that this is what you are looking for.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com