none
Force kill a process RRS feed

  • Question

  • If I have a backup of a database and don't mind using it, is there a way to force kill a process.  Ater an hour of watching a process killed after 10 minutes of execution, I still get the following message when using kill with status only:

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

    Monday, September 12, 2011 8:09 PM

Answers

  • you will not be able to dettach, or delete while the transaction is in the processing or rolling back. The only way would be to stop the SQL Server Instance service and then restart the service. This will have impacts on every other database in the instance so I would not recommend this lightly. Once you have restarted the instance then you would be able to do a restore , replacing the existing database with your backup that you are happy rolling back to.

    Ensure that all work on the other databases in your instance are not doing anything, if you are going to perform a restart of the instance.


    Warwick Rudd MCT MCITP SQL Server 2008 Admin
    • Marked as answer by Antoine F Monday, September 12, 2011 10:12 PM
    Monday, September 12, 2011 8:51 PM
  • run

    kill XX with status_only

    it will present the actual percentage of rollback completed, or run

    select wait_type, wait_time, percent_complete from sys.dm_exec_requests where session_id =

    to see if the process is working; if not, I am afraid you have to take the mssq instace offline to clean it; it's very nasty thing, if the rollback blocks for some reason (sometime unexplicable) you must restart.

    Hope MS will work on this; if it is an external process like DTS, you can get the pid and kill via Task manager, but a sql inside blocked process in this fashion gives you no chance.

    Regards,

               Marco

     

    • Marked as answer by Antoine F Monday, September 12, 2011 10:12 PM
    Monday, September 12, 2011 9:23 PM
  • It will recover, that process works pretty well; check the percentage completed into the errorlog to see the progress; be mindfull those "seconds remaining" it's what we call "windows time". Don't faint if you see ages... it will go faster by time.

    Regards,

            Marco

    • Marked as answer by Antoine F Monday, September 12, 2011 10:12 PM
    Monday, September 12, 2011 9:53 PM
  • Apparently, the solution was to:  ALTER DATABASE <dbname> SET OFFLINE WITH ROLLBACK IMMEDIATE

    Here is the answer I received on connect:

    https://connect.microsoft.com/SQLServer/feedback/details/688033/allow-skipping-transaction-rollback-or-dirty-kill-processes-to-allow-restore-of-database

    "Antoine,
    Thanks for your report.
    The command ALTER DATABASE <dbname> SET OFFLINE WITH ROLLBACK IMMEDIATE
    should take the database offline immediately, and put it in a state where restore can be performed to overwrite it.

    If that does not work, then support should be able to help you determine what is blocking you. This is not a very common situation, as most databases need to harvest any available data before dropping them.

    Thanks,
    Kevin Farlee "
    • Marked as answer by Antoine F Wednesday, September 28, 2011 1:50 PM
    Wednesday, September 28, 2011 1:48 PM

All replies

  • More than likely you have undertaken a large number of inserts,updates and deletes in 1 transaction and needs to rollback all of these changes before being able to proceed. Unfortunately there is no way of speeding this up or killing it cold. You will need to wait for this rollback process to complete successfully.
    Warwick Rudd MCT MCITP SQL Server 2008 Admin
    Monday, September 12, 2011 8:21 PM
  • Are there other alternative.  As I said, I do not care about the database, would detaching it, deleting and then recreating it resolve the issue or would I be blocked there as well?  Is there Any way to bypass this?

    Monday, September 12, 2011 8:28 PM
  • you will not be able to dettach, or delete while the transaction is in the processing or rolling back. The only way would be to stop the SQL Server Instance service and then restart the service. This will have impacts on every other database in the instance so I would not recommend this lightly. Once you have restarted the instance then you would be able to do a restore , replacing the existing database with your backup that you are happy rolling back to.

    Ensure that all work on the other databases in your instance are not doing anything, if you are going to perform a restart of the instance.


    Warwick Rudd MCT MCITP SQL Server 2008 Admin
    • Marked as answer by Antoine F Monday, September 12, 2011 10:12 PM
    Monday, September 12, 2011 8:51 PM
  • run

    kill XX with status_only

    it will present the actual percentage of rollback completed, or run

    select wait_type, wait_time, percent_complete from sys.dm_exec_requests where session_id =

    to see if the process is working; if not, I am afraid you have to take the mssq instace offline to clean it; it's very nasty thing, if the rollback blocks for some reason (sometime unexplicable) you must restart.

    Hope MS will work on this; if it is an external process like DTS, you can get the pid and kill via Task manager, but a sql inside blocked process in this fashion gives you no chance.

    Regards,

               Marco

     

    • Marked as answer by Antoine F Monday, September 12, 2011 10:12 PM
    Monday, September 12, 2011 9:23 PM
  • Thanks. 

    restarting mssql also yielded the same result.  I actually restarted the server.

    It is not a production server so it can tolerate a downtime.  The database is in recovery now.  If required, I will restore it.

    Monday, September 12, 2011 9:27 PM
  • It will recover, that process works pretty well; check the percentage completed into the errorlog to see the progress; be mindfull those "seconds remaining" it's what we call "windows time". Don't faint if you see ages... it will go faster by time.

    Regards,

            Marco

    • Marked as answer by Antoine F Monday, September 12, 2011 10:12 PM
    Monday, September 12, 2011 9:53 PM
  • LOL!  Not too long for me.  The recovery process seems to go much faster for me than the rollback.  After 2 hours I was still at 0% rollback.  After 30 minutes, the database is already 80% recovered.

    This is not a production server so I could afford shutting it down.  I am however filling a connect to have microsoft allow us to dirty kill a process or restore a database without having to go through the whole rollback process!  The impacts would have been much more important on a production environment.

    Monday, September 12, 2011 10:00 PM
  • Yes, thanks heaven it goes fast, I experimented it in a long white night after a terabite production db, I was praeying god while I am an atheist !

    The roll back is anyway unavoidable, still we should be able to get rid of a process, whatever the consequencies, without full restart.

    Regards,

            Marco

     

    Monday, September 12, 2011 10:06 PM
  • Database is now recovered.  Thanks for the help guys!

    If you are interested, please vote on my connect:  http://connect.microsoft.com/SQLServer/feedback/details/688033/allow-skipping-transaction-rollback-or-dirty-kill-processes-to-allow-restore-of-database

    Monday, September 12, 2011 10:11 PM
  • Apparently, the solution was to:  ALTER DATABASE <dbname> SET OFFLINE WITH ROLLBACK IMMEDIATE

    Here is the answer I received on connect:

    https://connect.microsoft.com/SQLServer/feedback/details/688033/allow-skipping-transaction-rollback-or-dirty-kill-processes-to-allow-restore-of-database

    "Antoine,
    Thanks for your report.
    The command ALTER DATABASE <dbname> SET OFFLINE WITH ROLLBACK IMMEDIATE
    should take the database offline immediately, and put it in a state where restore can be performed to overwrite it.

    If that does not work, then support should be able to help you determine what is blocking you. This is not a very common situation, as most databases need to harvest any available data before dropping them.

    Thanks,
    Kevin Farlee "
    • Marked as answer by Antoine F Wednesday, September 28, 2011 1:50 PM
    Wednesday, September 28, 2011 1:48 PM
  • Hi Antoine

    Are we sure that is the answer ? Did you test it ?

    I personally have couple of points:

    1) If this works, it could reduce the big issue we have with some processes; which, once on rollback, stuck and you need an instance restart (it happens seldom and under some circumstancies, mssql is stable !). If taking offline that db "with rollback immediate" will work, you will kill that process without restarting the instance. My believe is that the alter db process will queue against the blocking spid.

    2) What is blocking you is simply a process that will not rollback or disappear unless you restart the instance, it is a behaviour difficult to accept, but I've personally never found a way out: if the process stucks that way you can just restart sql instance (if it comes from out sql you could try killing the pid via task manager, very uncommon)

    So if there is a certan way to kill a blocked rolling back spid let's make it clear, but it could not really be a matter of "cleaning", as the process must necessary complete the rollback is doing, otherwise the data will be inconsistent; and actually what is doing is running a recovery process on starting, after restart, to complete the broken rollback. The process must simply be unblocked, but this is not an easy task. Best way is to solve the issue at the base of the cause that makes a rollback go still; this for MS great guys obviously.

    I am talking about cases when "kill XX with statusonly" and select ..percent_completed.. from sys.dm_exec_requests returns constantly 0, and nothing is moving. Does MS have a clear suggestion on this ?

    In this particular case db data consistency is not the matter anyway, as you go with restore; but the command suggested is simply "put the db offline immediately", with rollback, already on course; why should it not queue on locking like other processes ? If this cmd has a "special" force, then can be really used to close a hang session by taking offline the db it is running on. I will test it at the earliest (not to early I hope) occasion.

    Thanks

    Regards,

            Marco

     

     

     

     


    Marco Carozzi

    Wednesday, September 28, 2011 8:52 PM
  • Thank you for this thread. I also found this query useful after I got stuck with the same problem

    ALTER DATABASE MyDatabase SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE;
    One thing to note though; this query will roll back all transactions at the time it is executed.

    Mpumelelo

    Tuesday, May 14, 2013 3:07 PM