Long SQL file starts SQL Server from memory (22,000 lines)

Question

I have a .sql file, which is about 22,000 lines long. It is generated by something else, but basically contains only one update statement for each row.

I get this error when running a file in SQL Server Management Studio.

There is not enough system memory in the "internal" resource pool to run this query

I think I just need to split this request file, but I'm not sure if this is the best way. I could cut the file to 2,000 lines of string or something that I suppose.

This seems like a simple problem, and I will do it often enough, I would like to find a good solution. Any ideas?

Answer 1

Maybe you should take a look at running the script through SQLCMD instead of navigating through the SSMS GUI.

Answer 2

You probably exceeded the lot size, so SQL Server is having problems with package parse. If you can run about ~ 5000 lines of a line (select, execute), this will confirm my theory.

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

• Insert GO statements between them to force a new batch
• Split into multiple files

There are enough shell scripts for both. UnxUtils split , head , and tail should be useful.

Answer 3

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

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

Answer 4

I had a similar problem and worked under a command on the command line from blog me.

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

Answer 5

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

• In Object Explorer, right-click the server and select Properties.
• Click the "Memory" node.
• In the "Server memory settings" section, enter the amount you want Minimum server memory and maximum server memory.

The default value is 2147483647, I change it to 9147483647 and my problem has been resolved.