I need to run a "large" script on SQL Server 2008 R2 Express and it is failing with
There is insufficient system memory in resource pool 'internal' to run this query.
The script is around 10MB saved to disk, contains about 54000 top-level statements (insert/delete/update) and declares about 5000 variables (of type BIGINT).
I am running SQL Server 2008 R2 Express 64bit 10.5.1746. There is 3GB allocated to the VM, 1GB allocated to SQL Server, 512kb minimum memory per query. The results of DBCC MEMORYSTATUS can be found on this link.
The script is merely a restoration of a (lightweight) production database which was exported as SQL statements (data only, no schema).
If it's not possible to do this, I am shocked that SQL Server cannot handle such a basic scenario. I've tested this equivalent scenario on Firebird and Sqlite and it's worked just fine! (and they are open-source products).
NOTE: it is not possible to break the script up as variables declared in the beginning are referenced in the end of the script.
NOTE: Before rushing to flag this as a "duplicate" please note the other similar threads do not address the specific issue "How to run very large script in sql server 2008" .
StrayCatDBA :
SQL Server Express is limited in the amount of memory it can use. Of that memory only a portion can be used for executing queries. You can try setting forced parameterization on the database as it may reduce the memory required for the plan which would leave more for query execution (depends on your specific queries). \n\nThe best option is to use an edition of SQL Server that supports more memory. Developer edition is affordable but can't be used for production use. Standard edition would be your next best bet.",
2013-05-15T04:09:05