title: How to salvage SQL server 2008 query from KILLED/ROLLBACK state?
body: <p>I have a stored procedure that inserts batches of millions of rows, emerging from a certain query, into an SQL database. It has one parameter selecting the batch; when this parameter is omitted, it will gather a list of batches and recursively call itself, in order to iterate over batches. In (pseudo-)code, it looks something like this:</p>
  
  <pre><code>CREATE PROCEDURE spProcedure AS BEGIN
      IF @code = 0  BEGIN
          ...
          WHILE @@Fetch_Status=0 BEGIN
              EXEC spProcedure @code
              FETCH NEXT ... INTO @code
          END
      END
      ELSE BEGIN
  
          -- Disable indexes
          ...
  
          INSERT INTO table
          SELECT (...)
  
          -- Enable indexes
          ...
  </code></pre>
  
  <p>Now it can happen that this procedure is slow, for whatever reason: it can't get a lock, one of the indexes it uses is misdefined or disabled. In that case, I want to be able kill the procedure, truncate and recreate the resulting table, and try again. However, when I try and kill the procedure, the process frequently oozes into a KILLED/ROLLBACK state from which there seems to be no return. From Google I have learned to do an <code>sp_lock</code>, find the spid, and then kill it with <code>KILL &lt;spid&gt;</code>. But when I try to kill it, it tells me</p>
  
  <blockquote>
    <p>SPID 75: transaction rollback in
    progress. Estimated rollback
    completion: 0%. Estimated time
    remaining: 554 seconds.</p>
  </blockquote>
  
  <p>I did find a <a href="http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=103218" rel="noreferrer">forum message</a> hinting that another spid should be killed before the other one can start a rollback. But that didn't work for me either, plus I do not understand, why that would be the case... could it be because I am recursively calling my own stored procedure? (But it should be having the same spid, right?)</p>
  
  <p>In any case, my process is just sitting there, being dead, not responding to kills, and locking the table. This is very frustrating, as I want to go on developing my queries, not waiting hours on my server sitting dead while pretending to be finishing a supposed rollback.</p>
  
  <p>Is there some way in which I can tell the server not to store any rollback information for my query? Or not to allow any other queries to interfere with the rollback, so that it will not take so long? Or how to rewrite my query in a better way, or how kill the process successfully without restarting the server?</p>