Long SQL file runs SQL Server out of memory (22,000 lines)

Question

I have a file of a .sql file that is about 22,000 lines long. It's generated by something else, but basically just contains one update statement per line.

I get this error when I run the file in SQL Server Management Studio.

There is insufficient system memory in resource pool 'internal' to run this query

I think I just need to split this query file up, but I'm not sure the best way to go about it. I could cut the file into 2000 line chunks or something I suppose.

This seems like a simple problem, and I will be doing this often enough I would like to come up with a good solution. Any ideas?

Dressler

22000 updates sounds like something is not being done right. There's no way to make this a more set-based operation? – Computation

Well its created from an excel spreadsheet that a very specialized tool creates. The spreadsheet has X lines that need to update corresponding rows in the database, but this is a very old tool and only exports to Excel. – Dressler

Your life sucks. :) Excel is the bane of any DBA. – Computation

How about increasing the SQL server's memory? – Hildagarde

Answer 1

You might want to look into running your script via SQLCMD instead of going through the SSMS GUI interface.

Lineage

Answer 2

I had similar problem and running below command in command prompt from blog saved me.

   sqlcmd -S YOURSQLSERVER\INSTANCENAME -i "C:\Your Script.sql"

Gargan

Answer 3

You're probably hitting a limit on batch size, so SQL Server is having issues parsing the batch. If you can run in about ~5000 line chunks (highlight, Execute) then that'd confirm my theory.

Once you know the correct batch size down, you can either:

1. Insert GO statements in between, to force a new batch
2. Split to multiple files

A little shell scripting should suffice for either. The UnxUtils of split, head, and tail should prove useful.

Mongo

Answer 4

I had the same issue when running on SQL Management studio, what I did was execute it in command line osql –S localhost –d mydb –E –i .\hugescript.sql

http://tutewall.com/cannot-execute-script-insufficient-memory-error-sql-server/

Fernanda

Answer 5

In my case, none of the answers here worked. My script was 700,000 inserts long and it weights 1GB~.

I had to combine the following solutions:

1. Use SQLCMD Mode of SQL Server Management Studio

1) From SSMS, open a new query 
2) From the toolbar, open "Query"
3) Selected "SQLCMD Mode"

2. Set text size to maximum (SET TEXT)
3. Split the script into chunks of 150,000~ inserts (about 250MB each)
4. Run each script separately, from the SQMCMD Mode

:r "c:\path\to\my\chunk_1.sql"
:r "c:\path\to\my\chunk_2.sql"
:r "c:\path\to\my\chunk_3.sql"
:r "c:\path\to\my\chunk_4.sql"

---

Note: I couldn't use directly SQLCMD because I wasn't able to SET TEXT there, adn the default is 4000~ which wasn't enough.

Grandee

Answer 6

You can allocate more memory to your server, with this solution:

1. In Object Explorer, right-click a server and select Properties.
2. Click the Memory node.
3. Under Server Memory Options, enter the amount that you want for Minimum server memory and Maximum server memory.

default of maximum is 2147483647 , I change it to 9147483647 and my problem was solved.

Nephrotomy