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