May 12, 2011 at 9:46 am
OK, I had a user that needed me to kill their PID/session. The table is pretty big, 1.5million records. It's a test DB so I can kill the SQL Servie if I have to.
Here's my question.......
When I killed the users PID in "Activity Monitor", say PID 86, it went into Killed/Rollback but was still marked as runnable (Green check). The user said it was running for about an hour, maybe 2. So I letft it in the rollback state for the rest of the day and checked back just before going home. It was still in Rollback state and runnable.
Normally when I kill PID's they die and go away. So figuring this PID was going for an hour or 2 (as the user stated...) I let it go till the following morning. It was still in the same state. The table they were hitting had 1.5million records but there's no way it should've took that long.
I since cycled the SQL service (it's a Dev box) and the PID cleared but what caused this and what can I do to see what in the blinkety blink world was it doing? Chances are this will happen again so any insight/info to what I can do to see what it's doing would be helpfull.
Thanks
May 12, 2011 at 10:17 am
You can use sys.dm_exec_sql_text or the DBCC INPUTBUFFER command to quickly identify what command is being executed.
You may want to try killing the SPID in Query Analyzer.
http://msdn.microsoft.com/en-us/library/ms187730.aspx
http://www.sqlservercentral.com/Forums/Topic457115-146-1.aspx
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply