After killing my process in SQL erver its showing hung stage(Killed/Rollback) from 2hours. When i tryed to kill the process from Windows its showing 2 process with same name. Please let us know how to identify which process need to be kill.

  • Hi All,

    Today I got a request to kill SSIS process which SID is XXX.

    After killing my process in SQL erver its showing hung stage(Killed/Rollback) from 2hours. When i tryed to kill the process from Windows its showing 2 process with same name. Please let us know how to identify which process need to be kill.

    Please reply ASAP

  • If you've issued a kill command, you now have to wait for the rollback to complete. That can take as long, and sometimes longer, than the original transaction. So, if the original transaction took 3 hours, then you issued a kill statement, you will have to wait probably at least 3 hours. This depends on a ton of factors such as how much data was moved, was the cause of the original long transaction due to large data movements or blocking or resource contention. All those factors could cause the rollback to go up or down.

    You can get a quick look at what the rollback is doing, and if it's being blocked, by querying sys.dm_exec_requests.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Do not kill the process from Windows. There's an MSDN blog somewhere (will find when I get home if you like) that discusses what can happen in that case and it includes the SQL Server process crashing.

    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

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

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