March 30, 2004 at 1:49 pm
Several hours ago, I executed the kill statement against a spid. Executing sp_who indicates the cmd being executed is KILLED/ROLLBACK. At this point I feel sure the kill statement is not doing anything. In the past, I have always scheduled a reboot of the SQL Server Instance. Do I have any other options?
March 30, 2004 at 2:15 pm
It depends on how long the transaction had been running before you killed it. It will take the same amount of time to rollback that transaction...
March 30, 2004 at 2:18 pm
The transaction general takes a few minutes to successfully complete. This time it was killed after running about 30 - 45 minutes.
March 30, 2004 at 2:19 pm
I had the same problem a while ago on our system with a spid that wouldn't die.
Turned out it was the result of an aborted operation.
The only way to kill it in the end was to reboot the server. Looking through the sites on Microsoft wasn't much help on this one I found...
March 31, 2004 at 2:40 am
Was the spid running any extended stored procedures such as xp_Sendmail? If the external process has hung and SQL has lost concept of it's state (success/fail) then you can see this behaviour. I've never found a way of stopping that without restarting the SQL service. If you have some user xp for example you could try something like DBCC yourdll (FREE)?
March 31, 2004 at 3:08 am
Before rebooting the server, i would stop and restart the sqlserver Agent.
In most of the case that helps and it takes only a fews seconds
March 31, 2004 at 7:59 am
LondonNick
I talked to one of the lead developers regarding the use of any extended stored procedure. He doesn't believe they are using any but will have the code rechecked to make sure. xp_sendmail is definetly not being used.
fred2002
SQL Server Agent is not involved with the execution of this code. I'm assuming stopping and starting the service will not help in this case.
========================================
The server involved in this issue is one of our primary SQL Servers so we can't just stop and start any service. It looks like everything is working fine with the spid in this state. We have a planned outage on Saturday morning, so we will get the problem fixed at that time one way or another.
One of the Managers is thinking about opening a Microsoft Premier Support Case for this problem. Has anyone done this before on this particular issue or is there documentation from microsoft that just says reboot the server?
April 1, 2004 at 4:47 pm
I've run into this a couple of times, stopping and starting the SQL service will definately work. Usually happens because a user disconnects wrong (shuts off computer or it crashes).
April 2, 2004 at 5:42 am
I came across a simm. situation as well.
At that time, I issued a
ALTER DATABASE <DBANME> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE <DBANME> SET MULTI_USER WITH ROLLBACK IMMEDIATE
It solved my problem at that time. see if that helps.
April 2, 2004 at 12:55 pm
Thanks for all you help.
I spoke to someone with Microsoft Premier Support Case and the answer I got was to stop and start the SQL Server Services.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply