Cannot execute script: Insufficient memory Error in SQL server


When I am trying to run a script of 300mb on SQL Server 2012 I am getting this issue

Cannot execute script: Insufficient memory to continue the execution of the program (mscorlib)

here is the image

Cannot execute script: Insufficient memory to continue the execution of the program

How to resolve this issue to run script(insufficient memory) in sql server? I need to restore my database using this script


Asked by:- neena
2
: 8967 At:- 7/24/2017 2:40:56 PM
SQL sql-server insufficient-memory cannot-execute-script







3 Answers
profileImage Answered by:- jaiprakash

You can resolve "Cannot execute scriptInsufficient 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.

3
At:- 7/24/2017 3:20:57 PM
Thanks, sqlcmd worked for me, so didn't tried the second method 0
By : neena - at :- 1/9/2018 6:10:45 PM
Well great answer, I was stuck with same issue and it helped me thanks 0
By : jaya - at :- 4/3/2018 12:53:05 PM


profileImage Answered by:- bhanu

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.

2
At:- 3/28/2022 1:11:56 PM


profileImage Answered by:- jaya

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.

0
At:- 6/13/2022 8:28:40 AM






Login/Register to answer
Or
Register directly by posting answer/details

Full Name *

Email *




By posting your answer you agree on privacy policy & terms of use