Databases
StackOverflow2013
Postsdbo
POCanceling Insert Query - Taking Hours
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POCanceling Insert Query - Taking Hours
    text
    copied!<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>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload