Link to home
Start Free TrialLog in
Avatar of DrLechter
DrLechter

asked on

Terminating a kill/rollback process

I have a database that has a killed/rollback process which refuses to complete.  Short of bouncing the SQL server, is there any way to stop it?
Avatar of QPR
QPR
Flag of New Zealand image

How have you killed the process? (tried)
You can try using query analyzer by typing
kill x
where x is the SPID of the process.

This worked for me once when right clicking and killing in Enterprise Manager didn't work.
Have you had a look in profiler to make sure there is no work going on with this SPID?
ASKER CERTIFIED SOLUTION
Avatar of Vadim Rapp
Vadim Rapp
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Just and FYI, some processes cannot be KILLED; example most 'sa' and which ever user id you have set to Start and Run your SQL Server.
vadimrapp1,
Nito8300 is right, most of the system process could not be killed, they al system process which keeps sql server running, these will be running on any of the system databases like Master,MsDb, tempdb...
I don't think DrLechter had in mind any system processes. Most frequently unkillable processes are those that have launched extended stored procedures.
Agreed.
I still say he/she should run profiler before killing the service just in case there is a long running rollback happening.
Avatar of DrLechter
DrLechter

ASKER

The process was, in fact, as user process initiate by me.  It was a stored proc that I had been running in the debugger.  It froze.  I terminated the debugger and the process continued to run.  I killed the process with enterprise manager.  It went into a killed/rollback state but never completed.  It kept reporting 0 time remaning to completion, but would never stop.  I knew that restarting the server would fix the problem, but I was unable to do this at the time.

I do not have access to bounce the server via any normal means.  That is, I do not have sufficient privileges to do this via the enterprise manager and I have no privs to log into the NT server SQL is running on.

I did manage to bounce the server nevertheless.  I created a batch file on the server to run "sc" to stop the agent and server and then restart the agent.  I used a utility I found called sleep to pause the batch file for 30 seconds between each step so it would work correctly.  This fixed the problem.   It seemed an overly complicated solution though.

I find it hard to believe that there is no way to forceably stop a runaway process or to forceable destroy a database just because the system "thinks" users are connected to it.  It seems that there must be a way to blast all the users off of a database?


DrLechter,
> I knew that restarting the server would fix the problem,

Are you able to restart the MSSQLServer service

Run sp_who2 or SP_Lock and check whether there is any deadlocks
If so try to kill the locked process
> It was a stored proc that I had been running in the debugger

which, indeed, involves running a process external to sql server. It can kill a process that runs within its own context (
"in-memory"), but not something that runs as a separate application.

Here's an example. Let's say, you have an application that launches Microsoft Word, sends to it some commands (open file, do something, close file), expecting it to do the work and quit. But Word, when closing, displays a prompt to save the file. Now, if the process that launched Word is local system, then Word also runs in local system context, which means that noone ever would see the prompt and noone would click "cancel" on it. The calling application, naturally, does not know what Word is doing, it is expecting it to close. Basically, that's what is going on with sql server process that launched an external process, like the debugger - which is still waiting for you to click "next", even though the interface where you could do that has died long ago. You might say ok, but even if the application-caller is waiting, what prevents from killing it? The answer to this question is somewhere deeply in Windows internals, most likely due to the implementation of communicating with out-of-process objects; but the bottom line is, you can't.

Even if the caller application is being run not by the system but by the user, while it's expecting a command sent to Word to complete, you can't even focus its window: Windows will suggest that you switch to another application (i.e. to Word) and close it first. It very much looks like this implementation has its roots deeply in Windows 3, DDE, and blocking calls. Not very surprising at all: I still remember how once I was looking into some kernel-related file in Windows XP (already forgot which one), and I saw there nothing else but MS DOS commands MODE and CHCP - obviously, the file (with the name ending with "32" by the way) was nothing but wrapping over those.

> I find it hard to believe that there is no way to forceably stop a runaway process

You better believe.

> or to forceable destroy a database
why destroy the database??? you can stop the server (even if the users are connected), but why destroying the database???

> It seems that there must be a way to blast all the users off of a database?
stopping the server does that.
Technically, there was no answer to my question since I specifically mentioned NOT bouncing the server - but this was the first response saying I had to d othat.  Thanks for all the suggestons and info.