title: Canceling Insert Query - Taking Hours
body: <p>I ran a query to copy 18 million records from one table to another. Since the query was taking a long time to run I was monitoring the table count to tell when it would finish. </p>
  
  <p>I noticed before it reached the number it should have reached the count started dropping back down so i checked the log and it looks like sql server basically ran out of memory: </p>
  
  <p>'The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions.'</p>
  
  <p>The actual insert only ran for about 20 minutes before the error showed up in the log.
  I cancelled the query from management studio about 2 hours ago and judging by the table count this thing has only completed 25% which puts me at about 6 more hours. I'm assuming something along the lines of since all the memory is used up it is now running off the page file and that is why it is taking so long.</p>
  
  <p>Is there anything I can do to speed this up? It has basically made the database unusable because everyone is getting the 'cannot obtain a lock resource' error. I could easily delete the inserted records myself, I'm wandering if perhaps I could kill the process id and 'rollback' the insert myself?</p>
  
  <p><strong>Update</strong></p>
  
  <p>This thing is till running remarkably slow. One thing I have found is that the # locks given by the following query seems outrageous: 83 million. The next highest is a whopping 20.</p>
  
  <pre><code>SELECT request_session_id, COUNT (*) num_locks
  FROM sys.dm_tran_locks
  GROUP BY request_session_id
  ORDER BY count (*) DESC
  </code></pre>