Geek Answers Handbook

Memory Exception in SQL Server 2012

I am trying to execute a large sql script containing about 1,000,000 simple UPDATE queries.

The total size of this script file is about 100 MB.

When I run this script, I get an Out Of Memory exception.

When I split a file into 10 MB chunks, I can run each of them.

However, for convenience, I would like to have only one script, I can run right away. Is there any statement that I can imagine, so that SQL Server releases the allocated memory after running each query so that I can execute this large script right away?

+6
source share
3 answers

If you haven’t already done so, insert GO every thousand statements or so. Otherwise, the whole file will be one big package. SQL Server calculates a single execution plan for a batch that can push you toward resource constraints.

You may run into the problem of a different type of resource if you run the entire file in the same transaction. The larger the transaction, the more disk space for your TX log file that will need to complete processing your file.

+7
source

UPDATED: Open a command prompt window (run + cmd) and run the following:

 sqlcmd -S YOURSQLSERVER\INSTANCENAME -ic:\your_script_file.sql -oc:\your_log_file.log
+3
source

I noticed that there seems to be a memory leak in SSMS 2012, which seems to be related to the results window. Try putting set nocount on at the top of your script to prevent the results pane from populating with "1 line (s)" messages. Alternatively, is there a way to write your update statements as a single statement so that you don't have to work constantly with the server and client?

+1
source

More articles:


All Articles