Databases
StackOverflow2013
Postsdbo
POWhat are the options for getting around a memory shortage when running a large batch of inserts?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POWhat are the options for getting around a memory shortage when running a large batch of inserts?
    primarykey
    data
    text
    <p>I am trying to run a large batch of insert statements (about 30 megs) generated to resynchronize one database to match another. I am running SQL Server 2008 R2 64-bit on a Win 7 machine with 8 gigs of memory.</p> <p>When I run the query in SSMS, I get "Query executed with errors" and no output message. After some fiddling around and trying to run it outside of SSMS, I realized that it is an out-of-memory error. In fact, even after it errors out, I find that the SQL Server service is using about 5.5 gigs of memory!</p> <p>Is there a parameter I can adjust that will allow SQL to run this? The alternative is to write a program that breaks the query into batches and runs them. Interestingly, this seems to only occur with large queries that contain inserts. Large queries that contain updates seem to work fine. Perhaps this is due to the way they are written to the transaction log. The query is output as a series of many inserts, however, and this can't be changed.</p> <p>Anyway, is there a setting in SQL Server I can adjust to fix this? I would appreciate any advice anyone can give.</p> <p>Edit: Thanks for your reply, bluefooted. My colleague and I had the same impression when we first saw the messageless error--it looked like SSMS was running out of memory. However, on further inspection, it looks like it might be SQL Server. Let me explain. We are using Red Gate's SQL Comparison SDK with Visual Studio to generate diff queries and then conditionally running certain statements to bring 2 databases into convergence according to certain rules. Thus, after encountering this problem, we used the included Executor object to run the script. We received the following error:</p> <pre><code>System.Data.SqlClient.SqlException occurred Message=There is insufficient system memory in resource pool 'internal' to run this query. Source=.Net SqlClient Data Provider ErrorCode=-2146232060 Class=17 LineNumber=79180 Number=701 Procedure="" Server=QA1 State=123 StackTrace: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at RedGate.Shared.SQL.ExecutionBlock.BlockExecutor.ExecuteBlock(ExecutionBlock block, String server, String database, Boolean integratedSecurity, String userName, String password) at IO.Practiceware.DbSync.Csharp.Program.RunScript(String path, String server, String database) InnerException: </code></pre> <p>I don't know what can be made of this, but my more knowledgeable colleague said it basically means the SQL Server itself is running out of memory. I'd appreciate any insights you have into this.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USuser1202747
    1. USuser1202747
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POWhat are the options for getting around a memory shortage when running a large batch of inserts?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POWhat are the options for getting around a memory shortage when running a large batch of inserts?
      1. USIcarus
      1. VTFavorite
    3. VO
      singulars
      1. POWhat are the options for getting around a memory shortage when running a large batch of inserts?
      1. USAbdul Hfuda
      1. VTFavorite
    1. COWhat's the out of memory error that you're getting? Usually when I have a problem like that it's SSMS that is running out of memory, not SQL Server. When you say you ran it "outside of SSMS" how did you run it?
      singulars
      1. POWhat are the options for getting around a memory shortage when running a large batch of inserts?
      1. USPam Lahoud
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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