KILLED/ROLLBACK done, but won't finish
-
We are encountering this problem a couple times a week on a SQL2000 SP2 box.
A process will hang or something, we will kill it, then it ends up in a KILLED/ROLLBACK state. It is done (a subsequent kill says
SPID 66: transaction rollback in progress. Estimated rollback completion: 100%. Estimated time remaining: 0 seconds.
but then just stays there forever. There's no CPU or I/O load generated, if the sql server goes idle it just sits there. This process will block certain activity using the same tables involved, so it presents a problem if it just remains.
I left one running last night (it was executing simply an xp_sendmail), and this morning it's still the same.
A sql server restart will clear the processes, and when the server recovers it does not have a problem, no recovery delay, etc.
It really looks like the recovery completes and hangs prior to deleting the process.
Searching news/web I see people with these, but either they are actually rolling back or the answers presume they are. This one is NOT still rolling back, at least actively.
Does this ring any bells? Any suggestions?
This is a largish server, restarting it is hard to arrange.
-
A similar thing happened to us at least once. We had a process that had been running for a long time and so I killed it. It went into rollback, but never finished. All it did was effectively block important processes. So we restarted that computer and everything has been fine since. This happened on one of our production clustered SQL Server 2000 with SP1. I too will be interested if someone knows what could be happening and if there is a way to avoid it.
Robert Marda
SQL Server will deliver its data any way you want it
when you give your SQL Programmer enough developing time.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems -
I've seen some long rollbacks. Can you define forever?
I wouldn't be surprised if the process notifies 100%, but still is doing cleanup (like deleting log entries or something).
Can you tell how long it takes to get to 100%?
Also, if the xp_Sendmail is hanging, you likely have a dialog box that is waiting for a response (and you can't see it). Be sure the process doesn't have a dialog. You'd be surprised how often a scheduled DTS contains a msgbox() funciton, which will hang it.
Steve Jones
-
Re "how long" the last was a 3 minute runtime (max) rolling back for 20 hours. 🙂
I had some free time and decided to call Microsoft. THey said they have seen this occasionally, where the recovery is done but the process won't go away, but have no specific fixes for that.
However, when I mentioned xp_sendmail they said that was a known issue (Q320407) and had a fix for that. Her suspicion is that prevented the process cleanup, then any procedures that needed to access XP_SENDMAIL would also stall, causing people to kill those which put those similarly into a rollback state.
They sent us that fix, we installed it an hour or so ago and haven't been able to reproduce the problem.
So while I don't know what specifically allows the rollback never to finish, it appears we may have solved the specific problem that got us into this situation.
-
That stinks. Hopefully they will get this sometime. I know how it feels to have no resolution. Got two MS cases now and not sure if they'll be resolved.
Steve Jones
-
Re: I know how it feels to have no resolution
Well, while I'd love to know about the more general killed/rollback hang and what to do, I must say that I did get a solution that may solve our problem. In retrospect, I think everything we've seen this in did have xp_sendmail in it (though I cannot tell when its hung, much less tell retroactively, if that was always running).
So far so good.
We spend our $245 on Microsoft every month or so, and I must say more often than not the problem gets solved. Shouldn't happen in the first place often, but still, I'm reasonably impressed with their PSS folks.
-
What exactly is that fix and is it available on their web site? We have had issues with processes hanging after running xp_sendmail.
In the instance I sited earlier this was not the case as the hung process was used by a datapump in one of our import DTS packages.
As far as I knew there was no fix for the issue when there was a dialog box waiting for a response as Steve mentioned.
Robert Marda
SQL Server will deliver its data any way you want it
when you give your SQL Programmer enough developing time.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems -
I have seen the rollback error and we have had to restart SQL as well. But on the xp_sendmail issue, we were having some severe problems with it hanging to the point that when directly on the server, you couldn't even open Outlook, but a MAPI32.exe process was listed in Task Manager. So, on Microsoft's recommendation, we tested then upgraded to sp2. Guess what, we still had the issue.
The answer came from Compaq to replace the sqlmap70.dll file with the one from sp1. We haven't had a problem since.
Now, this will not resolve the issue if it is a dialog box or if a name cannot be resolved. Hope this helps someone else. We are running SQL Server 2K sp2 on a two-node clustered 2000 Advanced Server.
-
I am curious, how do you guys see the process is rolling back after you kill it?
-
Once you kill a process it will not go away until it has rolled back any uncommitted transctions. During this time when you view the process you will see that it is perfroming a rollback. In SQL Server 2000 they KILL command was improved so you can get a status from the rollback with a command like this:
KILL 15 WITH STATUSONLY
Robert W. Marda
SQL Programmer
bigdough.com
The world’s leading capital markets contact database and software platform.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems -
quote:
I am curious, how do you guys see the process is rolling back after you kill it?sp_who2 or process viewer, or (as mentioned) kill it again.
-
On the support issue, are you aware that if you have MSDN subscriptions you get 4 calls a year. may not sound much but in our dev team we have 40 licenses thats 160 calls a year, and more if you actually get MS to agree it's a bug or unwanted feature which should be changed.
On the rollback should always not that a rollback takes much longer to rollback than create i.e if your transaction takes 10 seconds then rolling back could take 100 seconds or more to rollback.
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons -
I've personally never seen a rollback take longer than the original transaction, although I've seen them take much less time. The only exception was when I had the problem with xp_sendmail mentioned earlier. I can't say that I'm an expert or anything, but I've done lots of killing of jobs, which should count for something.
James Loesch
James C Loesch
James C Loesch -
A similar thing happened to us at least once. We had a process that had been running for a long time and so I killed it. It went into rollback, but never finished. All it did was effectively block important processes like Replication. So we restarted that computer but the process is same as first.but when i removed the network cables from this computer and shut down the issue was resolved . I too will be interested if someone knows what could be happening and if there is a way to avoid it.
Yousaf Khan:
Database developer
-
I had the same issue and found that by killing the host process would resolve the issue.
select hostprocess from master..sysprocesses
where spid=
In my scenario litespeed was the culprit... I went into task manager after running the above query and then ended the process.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply