Cannot execute script: Insufficient memory Error in SQL server

You can resolve "Cannot execute script: Insufficient memory Error" by executing your scripts using cmd instead of SQL server.

To run your script in command-line tool using SQLCMD

• Navigate to the scripts folder(location of scripts) using command-line tool
• Execute this command 
  
  

  sqlcmd -S <ComputerName>\<InstanceName> -d <DatabaseName> -i <MyScript.sql>

  If credentials are required

  sqlcmd -S <ComputerName>\<InstanceName> -U <username> -P <password> -d <DatabaseName> -i <MyScript.sql>?

• That's it, you are done, your scripts will run now

Ensure that your network account has sufficient permissions to access both SQL Server instance and the specific database you intend to restore.

Cause:

Sometimes, due to the large size of the script and data, we may encounter this type of error. Server needs sufficient memory to execute and give the result. 

Another solution is to simply increase the Maximum Server Memory value in server properties. To edit this setting, right click on server name and select Properties > Memory tab.

After increasing the value from 1024MB to 2048MB, you can try to run scripts again, and it should throw the error.

Open CM.exe as an administrator and navigate to path where you have saved script.sql and then use below command

SQLCMD -S TestSQLServer\SQLEXPRESS -U username -P MyPassword  -d AdventureWorksDB2018 -i "d:\document\script.sql"

In the above query we are passing username and password also.

Without Username/password for sql server, you can use below query

SQLCMD -d <database-name> -i yourFileName.sql

Thanks.

Run CMD as an administrator and then run your SQL Query using that

C:\Windows>sqlcmd -S SERVERNAME -U USERNAME -P PASSWORD -d DATABASE -i C:\YourLargeQuery.sql

This will execute your query  without giving insufficient memory error.