December 27, 2007 at 7:42 am
Hello,
I've come across a problem that I need some advice on.
The setup:
MS sql 2000 enterprise SP4, running on 2 node cluster.
Legato backup.
The problem:
The legato process have failed at some point (the process didn't remove itself upon completion). This process is running at spid 65 and is blocking the next legato process (doing log backups every hour).
I manually kill process 65, and here all my problems start.
The process doesn't go away but goes into a rollback mode. If I do a "Kill 65 with STATUSONLY" it reports "transaction rollback in progress. Estimated rollback completion: 100%. Estimated time remaining: 0 seconds." ... and it stays that way.
Knowing that the process is held by Legato, I turn to my operating system and kills the legato hook (PID 5088, which I got from sysprocesses), however this has no effect.
So now I have Spid: 65 which is held by a non-existing pid: 5088 still running KILLED/ROLLBACK command and "transaction rollback in progress. Estimated rollback completion: 100%. Estimated time remaining: 0 seconds."
Then I turn to my friend Google.
Here I find lots of people saying "Restart sql service... it's the only way", and I plan when to do this until I find this one post somewhere telling someone NOT to restart since all hell will break loose and the database will be stuck in "recovering" mode.
So my questions are:
If I restart my sql service (or in this case: fail it over to the other node), will this fix my "undead process"-problem or will it kill my server?
Is failing it over even enough?
Is there any way that I haven't tried already that can fix my problem: ie. kill my undead process?
Thanks in advance 🙂
December 27, 2007 at 8:07 am
most of the time just restarting the sqlserver service will solve your problem. (depending on the number of operations it needs to rollback)
Reason: at startuptime of a db, all uncomplete transactions are rollbacked.
Because at that time the sqlserver service is the onlyone allowed for the db, this process can be performed faster then on a live db.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 27, 2007 at 9:14 am
As johan mentioned, a restart should fix things. Every once in awhile a kill hangs on the rollback.
December 27, 2007 at 11:59 am
i think you are using some extended dll thus it get locked...there is a dbcc command to free dll, i dont remember on top of my hand but you can search ....i will search if you didn't find it
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
December 28, 2007 at 12:57 am
Thank you for your replies. I have started to see if I can get a window in for failing over the instance.
Prakash, is there any way to see which dll(s) a sql-process is using?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply