1. Slow rollback, hung rollback
I had a bit of a time with SQL7 today.
Running from ISQL:
First I did a fancy select ... join ... where X is any (...). When it
ran for more than ten seconds, I realized one of my joins required two
large tables be scanned, so I hit cancel. I waited and waited. I
fired up the EM and looked at current activity. SPID12 was in
rollback. I waited and waited, probably twenty minutes. Finally, for
lack of patience, I tried to kill the thread. It still wouldn't go
away (of course not, while it's in rollback, right?) Still, the
server responded so quickly, it seemed to be hung but not doing any
damage, so I proceeded with other things. Oh yes, first I killed the
entire ISQL process -- no difference, of course, to the server.
I restarted ISQL. A while later, I issued a large delete. After
twelve minutes, I realized it was likely to take forty minutes or so,
so, nervously, I hit the cancel on that, too. OK, it went into
rollback. I waited, and waited. This one I waited out: another 70
minutes later, the rollback completed. Ich!
This is on a 2gb RAM machine with tons of mirrored disk, and I'm the
only user, btw.
Meanwhile, good old SPID12 just sat there.
I tried to stop SQLServer from my remote EM, and it would not. I then
called over to our ops people to reboot the system, trying to shut it
down smoothly, but in any case to get it shut and restarted. I don't
know what they had to do, but on restart SPID12 was gone and things
seem fine.
So, my question to the audience today is, has anybody else seen any
problems with rollbacks on SQL7?
Joshua Stern
PS - I note that in the EM, under currentActivity/Locks, if I select a
SPID that is blocked, my local disk starts to thrash, nothing is
displayed, the EM is locked up and has to be killed. Not nice.
2. OnLine 5.00 database corruption
3. "Rollback transaction has no corresponding..." But my sp doesn't do a rollback
4. SQL Server 7.0 for windows NT and Windows 95/98
5. Is it better to drop a rollback segment and recreate it instead of add additional rollback segment?
6. Local memory in ext. stored procedure.
7. Rollback Segment and how to use the statement "Set transaction use rollback segment ???"
8. VFP 3.0 Error routine, i need to control project errors (microdigital@telepac.pt)
9. Problem: Nester Transaction an partial Rollback
10. Rollback Multiple Inserts in Stored Proc
11. rollback
12. Rollback transaction from C++ Layer doesn't succeed
13. possible to rollback a bad statement?
by