Workaround for "Insufficient memory to continue the execution of the program (mscorlib) in Microsoft SQL Server Management Studio when executing a large SQL script

SSMS Insufficient memory to continue the execution of the program (mscorlib)

Issue: "Insufficient memory to continue the execution of the program (mscorlib) in Microsoft SQL Server Management Studio when executing a large SQL script.

Solution/Workaround:

Out of memory errors are thrown by Microsoft SQL Server Management Studio (SSMS) when trying to execute a large SQL file due to lack of sufficient system memory. The script that I tried running was 75 MB and every time the script is executed, it throws the error -"Insufficient memory to continue the execution of the program (mscorlib)".

Running the scripts using Microsoft SQL Server Management Studio (SSMS) will not work. Try running the erring SQL script from command prompt as it is much lighter.

How to run SQL scripts from command prompt?
  • Open Command Prompt on your computer
  • Type the following command (without quotes, < and > symbols): 
sqlcmd -S <servername\instance" -e -d <database name> -i <SQL file path>

If SQL Server instance is not set, omit the instance name. The command will now look like:
sqlcmd -S <servername" -e -d <database name> -i <SQL file path>

Example: 
sqlcmd -S computer401 -e -d schooldb -i D:\SQL Scripts\create_procedures.sql
The SQL script which failed to execute in Microsoft SQL Server Management Studio (SSMS) should now run.


How to find the SQL server instance name in Microsoft SQL Server Management Studio ?
  • Open Microsoft SQL Server Management Studio (SSMS) and login
  • Right-click on the server (root of the tree on the left sidebar)
  • Click on properties
  • In the "Server Properties" windows, click on "View Connection Properties"
  • In the "Connection Properties" window, under "Product", check "Instance Name" property.
How to find the SQL server instance name in Microsoft SQL Server Management Studio


Comment with Facebook

Comments

  1. Good work, but it didn't solved my issue, was able to solve my issue using CMD as given here
    https://qawithexperts.com/questions/59/cannot-execute-script-insufficient-memory-error-in-sql-serve

    ReplyDelete

Post a Comment