How can I kill a process

Question

Post reply

How can I kill a process

bshaw

Right there with Babe

Points: 750
More actions
April 15, 2004 at 8:08 am

#85173

Analyzing results from a modified sp, I executed the code from the original sp in one QA window, then executed the modified code from the new sp. Both sp's basically did the same thing. One difference was that the original sp created multiple temp tables, performed updates, deletes, etc, but ended with the insertion of records from the temp tables into a table.
The second sp did the same, except all tables were temp tables. As part of the analysis, I executed a query like....
SELECT PartNumber FROM A WHERE PartNumber NOT IN (SELECT PartNumber FROM #tmpB)
The query ran excessively long to the point where I stopped the query. I then attempted to drop the table in the first query, and again, QA ran excessively long. I attempted to drop the table from Enterprice Mgr, and had to end my EM session as it was not responding.
I checked Current Activity and process, and the spid associated with my QA sessions shows a status of rollback with a Wait Type of EXCHANGE, and has been so for a second day now. I have tried every way I know to kill the spid, and can't do it. Not sure what caused this lock (I assume it is a lock), but I'd like to kill it first, then look into what I did wrong. Any ideas.

Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736494 More actions · Answer 1

Rollback means it's rolling back. Likely something is stuck. Did you check for blocks?

If KILL doesn't do it, you'd have to do a restart to end the process.

Rodrigo Acosta SSC-Insane Points: 21261 More actions · Answer 2

Run KILL spid WITH STATUSONLY from QA. If the rollback process is 100% complete and 0 seconds remaining, then it means that it won't rollback and you will have to stop and start the SQL service.

bshaw Right there with Babe Points: 750 More actions · Answer 3

Thanks for the info. There are no locks. This has happened a time or two in the past, and as everyone suggests, the only way we've been able to remove that process is to stop and restart the service.

Thanks