Cannot use KILL to kill your own process

Question

Cannot use KILL to kill your own process

adams.squared

SSCrazy

Points: 2675
More actions
September 27, 2010 at 1:19 pm

#228839

How do I kill a process that is my own? This process has had a table lock in effect since last week. Every time I use the KILL command, I get the result "Cannot use KILL to kill your own process" I haven't found anything useful on the web that will help me. This is on a test system, so I have quite a bit of flexibility in fixing it.

Viewing 11 posts - 1 through 10 (of 10 total)

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

Derrick Smith SSChampion Points: 10833 More actions · Answer 1

Log in using a different login that has access to kill processes? If you're using your domain account, log in as SA or vice versa.

adams.squared SSCrazy Points: 2675 More actions · Answer 2

tried that. under either my windows authorization or using SA, I get the same error.

Derrick Smith SSChampion Points: 10833 More actions · Answer 3

Are you sure you're trying to kill the right spid? You only get that error when you try to kill the spid that you're currently executing from..

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 4

Each time you log in, you get a new spid. If I open 10 query windows from SSMS, I'll have 10 (or 11, Object Explorer) SPIDs assigned.

In the new window, Select @@SPID. THen kill the other SPID with your ID.

adams.squared SSCrazy Points: 2675 More actions · Answer 5

When I run SP_Lock under SA the SPID is 61. Under my Windows login it is 59. The DBID and the ObjID are the same. Below are the results from SP_Lock

spiddbidObjId IndIdTypeResourceModeStatus

59111311510750TAB IS GRANT

Derrick Smith SSChampion Points: 10833 More actions · Answer 6

Ignoring sp_lock for a second..

Run this:

SELECT SPID, login_time, loginame FROM Master.dbo.sysprocesses

where loginame = 'your username here'

Kill the spid that has the login time from last week.

adams.squared SSCrazy Points: 2675 More actions · Answer 7

So on my client using SSMS I log in to the server using my Windows login. The SPID of the lock is the same as my login. I then did a remote into the server and used SA to log on to SQL. The table lock is there and the SPID is the same as SA.

adams.squared SSCrazy Points: 2675 More actions · Answer 8

adams.squared (9/27/2010)
So on my client using SSMS I log in to the server using my Windows login. The SPID of the lock is the same as my login. I then did a remote into the server and used SA to log on to SQL. The table lock is there and the SPID is the same as SA.

I used my login, the SA login and a couple more possibilities; but there are no SPIDs from last week. Just me and the SA from this afternoon.

Derrick Smith SSChampion Points: 10833 More actions · Answer 9

Ok, I think there's a misunderstanding here.

Pretty much every spid will have a lock of some kind on something and will show up in sp_lock. Notice the DBID in yours - Master. You can probably check the OBJECT_NAME of that objectid and you'll get a system table. This is normal.

Originally, I thought you were seeing your old spid open for over a week in something like the sysprocesses list or sp_who2. That would be an issue. Opening a new session and seeing your spid having an Intent Shared lock on a system table is nothing to be concerned about, and will happen almost, if not every, time.

adams.squared SSCrazy Points: 2675 More actions · Answer 10

I think I realized that just about the same time you were typing it. I'm trying to get to the record in the application that had the lock to see if I can reproduce it, but I can't. So it must have been what you said.

Thanks