I am trying to execute a large sql script which contains about 1000000 simple UPDATEqueries.
The total size of this script file is about 100 MB.
When I run this script I'm getting an Out Of Memory exception.
When I split the file into chunks of 10 MB I can run each of them.
However, I for convenience I would like to have only one script I can run at once. Is there any statement I can introduce so SQL server releases allocated memory after running each query so I can execute this large script at once?
If you have not done so already, insert a GO every thousand statements or so. Otherwise the whole file will be one large batch. SQL Server calculates a single execution plan for a batch which may be pushing you to resource limits.
You might run into another type of resource problem if you run the whole file in the same transaction. The larger the transaction, the more diskspace your TX log file will need to complete the processing of your file.