SQL Server 2005 record lock

I am currently have a locked record in a table called IV00101. There is some kind of lock on one record in this table. There is a part called 255A4330P001. I cannot even do a select statement against this part. If I try, the command just sits there running and the query never completes. Is there a sp I can run that will show me the lock currently on this record?
Who is Participating?
lcohanConnect With a Mentor Database AnalystCommented:
Next time you should try KILL SPID number instead of restarting the server and you can also use Activity Monitor (or SP_WHO2) to get more info about the process that's locking that badly to take action in the future.
lcohanDatabase AnalystCommented:
you can try sp_lock and check for all locks on the objectid for your table
lcohanConnect With a Mentor Database AnalystCommented:
You can replace the table_name in query below and try it on your server to find all lock for that object:

create table #locked_objects (spid int,dbid int,objid int,indid int,type sysname, resource sysname,mode sysname,status sysname)
insert into #locked_objects exec sp_lock
select * from #locked_objects where objid = object_id('table_name')

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
select * from IV00101(nolock) where part= '255A4330P001' 

Open in new window

should work whatever lock is on the table/row ...
rwheeler23Author Commented:
OK, I have the SPID but how do I know which one is the lock? I see about a dozen records on this table. Do any of the columns tells me where the lock is?
rwheeler23Author Commented:
Rebooting the server was the only way to free up the lock. There were about 3 possible candidates. Restarting the SQL service would have accomplished the same thing.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.