October 10, 2013 at 6:06 pm
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 DomainMachineName .SandboxSELECT INTO 313310/10 17:00:06Microsoft SQL Server Management Studio - Query54 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.:w00t: 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).
--Quote me
October 10, 2013 at 8:49 pm
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.
October 11, 2013 at 2:30 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 12, 2013 at 6:31 pm
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.
--Quote me
October 13, 2013 at 2:41 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 13, 2013 at 5:38 am
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 .:w00t:
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
October 13, 2013 at 10:29 am
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:w00t:. Any other suggestions pl-easse?
--Quote me
October 13, 2013 at 9:01 pm
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:w00t:. 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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply