Post reply

Killed Jobs hung

  • September 29, 2008 at 9:15 am

    #125653

    I've found others having this problem and most of the threads end up with telling them to re-start their servers. I'm hoping somebody has found a better way.

    I inherited a SQL Server to manage. While reviewing the Activity Monitor on this SQL 2005 server I find a number of OLEDB processes with a status of KILLED/ROLLBACK that are actively accumulating "WaitTime" 1260069771 (all of them are 10 digit values). Some of the Login and Last Batch date/times are almost a year old!

    These appear to be orphaned processes because the stored procedures are run almost daily. I'm 99.9% sure that if I could just nuke these processes there would be no ill effects but I don't have the same level of optimism about restarting the server.

    Are there any other ideas?

    Thanks

  • September 29, 2008 at 9:28 am

    #878459

    If they are already in KILLED/ROLLBACK state, you cannot kill them again. Since they are actively processing a rollback, restarting the server will just continue the rollback when they come back up because open transactions get rolled back when a server restarts.

    You should not only figure out what the processes are and why they are hanging, but you should find out what killed the processes and caused the rollback.

  • September 29, 2008 at 10:01 am

    #878490

    These may have been killed manually by my predecessor since these jobs appear to be "hung". Are we to believe that these processes are rolling back an action since 11/2007? From what I can see they are exports from a linked database so I cannot imagine what would require "rolling back".

    I agree I need to find out why these processes end up "orphaned" and try to eliminate that but I don't think there is any value to leaving them "alive".

    So you think re-starting the server will do nothing but pick them back up again?

  • September 29, 2008 at 10:15 am

    #878496

    If the kill\rollback spid is not using cpu or disk between executions of sp_who2 it is a ghost process. The rollback was done long again ago. A reboot should clear the problem.

    For safety name sure you have quality backups before the reboots.

    This beg the question what service pack are you on?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply