I ran a query a number of hours ago with a select into statement. I canceled the query, but it took awhile and my SQL Server client froze. The database that query was running on is now locked.
When I run sp_who2, I see these statistics:
Status: Runnable
BlkBy: "." <-- meaning blocked by nothing?
Command: Select into
CPU Time : 0
Disk IO : 0
LastBatch : 12/30/2016 (today)
Is it that the query is still taking time to close? The status runnable apparently means its waiting for something — is it waiting for more space allocation? I do not have sufficient privileges to kill the process and no one is around to help.
Could someone assist with this?
Best Answer
Well, what is the last wait type ?
If you cancelled the query, it has to rollback, so the cancel is not immediate. For instance you want to insert into billions of rows in one statement (not sure it is a good idea, but let's imagine). It takes several hours to do it (and your disks are probably burning) and if you cancel it, it may also take hours to rollback everything that has been done.
Also, I often experienced similar issue when using a linked server to Oracle. Because the network was a bit sensitive and Oracle config a bit strict, long query using this linked server (more than 1 hour) were stuck : process wasn't there anymore on Oracle side, but still on SQLServer side, and this one was impossible to kill and was locking destination table. Only solution : restarting SQLServer service.
But that does not explain why SSMS is freezing...