how to remove a key type lock

Question

Post reply

how to remove a key type lock

Denise McMillan

SSCertifiable

Points: 6216
More actions
April 8, 2008 at 11:24 am

#123286

I have identified a lock in a database. How can I remove it? I tried to kill the process but got message that it is not a valid process id. Can anyone help? Thanks very much.
Lock on table is type: KEY, process id is: -2

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Todd Engen SSCrazy Eights Points: 9869 More actions · Answer 1

Denise,

Is this a continuation of this post? http://www.sqlservercentral.com/Forums/Topic481065-146-1.aspx

Did you try using the UOW ID that was previously suggested and it didn't work?

Denise McMillan SSCertifiable Points: 6216 More actions · Answer 2

Yes this is a continuation of the other posting.

I ran the select req_transactionUOW command and get 15 id's.

Should I kill all of them?

Thanks.

Todd Engen SSCrazy Eights Points: 9869 More actions · Answer 3

One by one.

Since it's a test environment you might want to track back to the cause.

Denise McMillan SSCertifiable Points: 6216 More actions · Answer 4

I used Activity Monitor under Locks by Object tab, Selected object of the table in question. It had under Owner GUID the same number of one of the reqtransactionUOW's in the list. I killed that one and the table is now unlocked.

The other locks are on tempdb tables. Should I kill them then too?

Thanks very much, I have really learned a lot today!

Denise McMillan SSCertifiable Points: 6216 More actions · Answer 5

Oops, I was mistaken about them being on tempdb. Even though I can access the table now, there are still eleven locks showing for the eorla database. Shoul I kill them all? Thanks you!

Todd Engen SSCrazy Eights Points: 9869 More actions · Answer 6

Test environments are typically open to doing things like that without having negative consequences. Only you can decide that though.

If it's shared environment you might want to talk to anyone else that's using it. For example, maybe someone issued a distributed transaction and left it open accidently.

Denise McMillan SSCertifiable Points: 6216 More actions · Answer 7

OK, thanks, I will check into it first.

Can you tell me, does the Process id being a negative two (-2) mean anything?

When I tried to kill using that it said not a valid process id.

Thanks again for all the help.

Todd Engen SSCrazy Eights Points: 9869 More actions · Answer 8

You're welcome. Thanks for posting back what you found.

SQL Server 2005 Books Online
The SPID value of '-2' is set aside as an indicator of connectionless, or orphaned, transactions. SQL Server assigns this value to all orphaned distributed transactions, making it easier to identify such transactions in sp_lock (spid column), sp_who (blk column), syslockinfo, and sysprocesses.