Menu
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?
803
SQL Server is designed to use all the memory on the server by default. The reason for this is that SQL Server cache the data in the database in RAM so that it can access the data faster than it could if it needed to read the data from the disk every time a user needed it.
By default, SQL Server's max memory is 2147483647 – a heck of a lot more than you actually have.
Memory Management on SQL Server works on the Fill-and-Flush algorithm. The default values do not restrict the memory consumption from growing unless there's a request from the Operating System. The sizing depends on various components of the system—in many cases, setting it between 70% and 80% is a good starting point.
You might want to look into running your script via SQLCMD instead of going through the SSMS GUI interface.
118
I had similar problem and running below command in command prompt from blog saved me.
sqlcmd -S YOURSQLSERVER\INSTANCENAME -i "C:\Your Script.sql"
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
