• We have sql job the running for a log time.So i have cancel that job.BUt that

    job spid is still running showing in command as killed/rollback.

    wheni tried to kill that spid it is dispalying as below.

    SPID xx: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.

    Please suggest.

    It is production server 2005 and i can not restart the server.

    Many thanks.

  • may we know what the stopped SQL job was doing?

    if it is a dbcc checkdb task then you don't have to worry as the rollback is occurring in the snapshot?

    you can also possibly ignore the rollback if the process was related to backup.

    check the process related details from sysprocesses, more specifically try and find out the DBID associated with the process.

    Also try running dbcc inputbuffer(spid) as well.

  • Check what it was doing.

    You may well have to restart the SQL instance, don't do so until you know exactly what the job was doing.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • dbcc inputbuffer(spid)

    If the above command indicates any cmdshell / bcp execution, kill it from Task Manager. It could be a malicious activity as well.

    Be cautious enough, it's your PROD server.

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

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