Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

trying to KILL a SPID, getting 'Cannot use KILL to kill your own process.' Expand / Collapse
Author
Message
Posted Thursday, October 10, 2013 6:06 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, February 17, 2014 10:08 PM
Points: 271, Visits: 728
It's happening on an instance of SQL Server installed on my own machine. Inside a database called Sandbox, I was running a query that didn't want to finish, so I cancelled it. But, it appears it's still running cause when I do sp_who2 I get
54 RUNNABLE Domain MachineName . Sandbox SELECT INTO 31 33 10/10 17:00:06 Microsoft SQL Server Management Studio - Query 54 0

The SPID is 54 and when I try to kill it I get that error. So, I created another database login for the instance of SQL Serrver with sysadmin and public server roles checked and logged out as current user and back in with new login to kill the spid. Still can't.

I've also rebooted. Still SPID 54 is there hogging resources and preventing me from updating a table on the Sandbox database.

I need to kill the spid cause it's locking the resources on the database that I need. Not having access is ruining my evening plans. How to solve this problem of SPID locking my database?

NOTE: I am willing to take almost any action to solve this short of tossing the laptop, uninstalling SQL Server and reinstalling, deleting the whole database (cause it's not backed up, as it's been my sandbox).
Post #1503836
Posted Thursday, October 10, 2013 8:49 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:21 PM
Points: 2,842, Visits: 2,423
In your example, process 54 is the connection that you are using to execute the sp_who2 command. It is not doing anything unless you are actually running a command. And, as the error message from the KILL command says, you cannot kill the session you are using to run the KILL command.

I think you may be confused about how to read the output from the sp_who2 command. Chances are the CPUTTime and DiskIO value increase each time to run the command - this is because the sp_who2 uses CPU and disk resources to get the data required for the command to run.



Post #1503852
Posted Friday, October 11, 2013 2:30 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:30 PM
Points: 41,531, Visits: 34,448
How are you identifying that the session 54 (which is your current session that you're running the sp_who from) is locking resources and preventing updates?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1503912
Posted Saturday, October 12, 2013 6:31 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, February 17, 2014 10:08 PM
Points: 271, Visits: 728
GilaMonster (10/11/2013)
How are you identifying that the session 54 (which is your current session that you're running the sp_who from) is locking resources and preventing updates?

I'm concluding that session 54 is locking resources and preventing updates for the following reasons:
(1) The Command the SPID is referring to is called SELECT INTO, which is the last query I ran against Sandbox and it kept executing without returning results or timing out.
It's the query I cancelled.
(2) Since cancelling it, subsequent queries on that database, which have no reason to run slow eg. ALTER TABLE <tablename> ALTER COLUMN <column name> datetype just took over 3 minutes to complete. .
(3) I'm the only one who touches this db and so I know everything was just fine before the SELECT INTO query.

The SPID is now clocking 140954 CPUTime and 35925 DiskIO, even though I haven't been on the instance for 2 days. No other SPIDs are taking any more than 15 CPU time or 5 DiskIO. Mostly, 0s up and down the sp_who2 columns.

Any reason why I shouldn't be able to kill the SPID regardless of why I think it's the offending one or not? I'm pretty sure it is, but don't know how else to prove it.
Post #1504238
Posted Sunday, October 13, 2013 2:41 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:30 PM
Points: 41,531, Visits: 34,448
polkadot (10/12/2013)
GilaMonster (10/11/2013)
How are you identifying that the session 54 (which is your current session that you're running the sp_who from) is locking resources and preventing updates?

I'm concluding that session 54 is locking resources and preventing updates for the following reasons:
(1) The Command the SPID is referring to is called SELECT INTO, which is the last query I ran against Sandbox and it kept executing without returning results or timing out.


Yes, it's executing every time you look, because it is the sp_who that you're executing. sp_who runs a SELECT INTO (look at it's code if you want) and hence when you run sp_who, one of the things it sees is itself running. That's all you're seeing.

If you run SELECT @@spid (shows your current sessionID) right before the exec sp_who, you'll see it says 54. You're seeing 54 always running because you're using it to see what's running.

(2) Since cancelling it, subsequent queries on that database, which have no reason to run slow eg. ALTER TABLE <tablename> ALTER COLUMN <column name> datetype just took over 3 minutes to complete. .


If there were locks still being held from some earlier activity, the alter table would hang forever, not take longer.

That taking longer could be for any one of a large number of reasons, log growth, concurrent activity outside of SQL, disks, fragmentation, data size, etc, etc

Additionally the reboot you did would have terminated any sessions, rolled back open transactions and terminated any locks. Locks cannot persist through a restart of SQL.

The SPID is now clocking 140954 CPUTime and 35925 DiskIO, even though I haven't been on the instance for 2 days. No other SPIDs are taking any more than 15 CPU time or 5 DiskIO. Mostly, 0s up and down the sp_who2 columns.


That's cumulative since the connection started. That CPU and disk IO is from you repeatedly running sp_who and whatever else you're running on that session. It doesn't mean the session is using CPU now, only that it has since it started

Any reason why I shouldn't be able to kill the SPID regardless of why I think it's the offending one or not? I'm pretty sure it is, but don't know how else to prove it.


Because you cannot kill the current session. You cannot use KILL to terminate the session that KILL is running from, and that's what you're trying to do.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1504247
Posted Sunday, October 13, 2013 5:38 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, March 14, 2014 2:19 AM
Points: 2,820, Visits: 3,916
polkadot (10/10/2013)
I've also rebooted. Still SPID 54 is there hogging resources and preventing me from updating a table on the Sandbox database.
Did i see notice something here ? or i am overlooking .


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1504253
Posted Sunday, October 13, 2013 10:29 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, February 17, 2014 10:08 PM
Points: 271, Visits: 728
OK, coincidence that my last query should have been a SELECT INTO.

Yes, Bhuvnesh, I think restarting SQL Server should have reset the numbers for SPID 54. Besides, I wasn't repeatedly running sp_who2 like a maniac, or anything.

Clearly, there is some spid hanging and the only one in the sp_who2 report for Sandbox is SPID 54. All the other SPIDS are for the other databases in my instance, like master, msdb, and others.

I just tried to delete Sandbox db, BUT, SQL Server won't let me.

Something, and if it isn't SPID 54 I don't know how to find out what is, is preventing me from *even deleting* the db. I get error:

"Cannot drop database "Sandbox" because it is currently in use."

What to do. I am now ready to delete the db and I thought that was pretty broad stroke to fix problem. Perhaps tossing the laptop wouldn't be a solution either. Any other suggestions pl-easse?
Post #1504278
Posted Sunday, October 13, 2013 9:01 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:30 PM
Points: 41,531, Visits: 34,448
polkadot (10/13/2013)
I think restarting SQL Server should have reset the numbers for SPID 54.


It did. No connections or connection-related information survives a restart.

Clearly, there is some spid hanging and the only one in the sp_who2 report for Sandbox is SPID 54. All the other SPIDS are for the other databases in my instance, like master, msdb, and others.


There's no indication from anything you've said that there's anything hanging anywhere. The restart would have terminated all running connections and if there was still something else running the sp_who would have shown it.

I just tried to delete Sandbox db, BUT, SQL Server won't let me.


Probably the session that you're currently running all those sp_who and kill from is using that DB, or another object explorer connection.

Something, and if it isn't SPID 54 I don't know how to find out what is, is preventing me from *even deleting* the db. I get error:

"Cannot drop database "Sandbox" because it is currently in use."


What database context are you running the DROP DATABASE from? If you're currently using the Sandbox DB yourself (the database drop down in the menu reads Sandbox, then the connection that you're currently using is using that DB and preventing it from being dropped.

Disconnect object explorer, run USE master, make sure all your other query windows are closed then drop the DB. You cannot drop a database that you are using, even if the only connection using it is the one running the DROP.

What to do. I am now ready to delete the db and I thought that was pretty broad stroke to fix problem. Perhaps tossing the laptop wouldn't be a solution either. Any other suggestions pl-easse?


You mean other than suggesting you don't actually have a problem?

There's nothing wrong with your databases from what you've said. Nothing unusual, nothing sgtrange.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1504310
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse