Error starting 50 mb script on SQL Server 2008 R2

Question

I am using SQL Server 2008 R2, I have a script to update the database that the script is approximately 50 MB and contains about 800,000 rows.

Mistake:

TITLE: Microsoft SQL Server Management Studio

Unable to execute script.

ADDITIONAL INFORMATION:

Not enough memory to continue running the program. (Mscorlib)

Can someone help me run this script without getting this error?

Answer 1

use the SQLCMD command line tool, which is much more compact in memory. It is as simple as:

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

You need valid credentials to access an instance of SQL Server or even to access a database

Answer 2

Adding the answer to @ user1293068, I needed to specify an instance name. Here is my full syntax:

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

This is described in Technet Using the sqlcmd Utility .

(Note that you must enter the value of the -S switch, not the -s switch. The switch is case sensitive.)

Answer 3

You can also try to increase the maximum server memory value in the server properties.
To edit this setting, right-click the server name and select Properties> Memory tab.

I ran into this error trying to execute a 30MB SQL script in SSMS 2012.
After increasing the value from 1024MB to 2048MB I managed to run the script.

Answer 4

If credentials are required

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

Answer 5

Well, none of the answers were sufficient to successfully restore my script.

So:

• Ensure that your network account has sufficient permissions to access the instance of SQL Server and the specific database that you are about to restore.

• It’s best if the database that you intend to restore actually exists if it was just generated from Generate Scripts (with changes that include both Schema and Data)

• Run the command-line tool in administrator mode (if necessary), for example, enter CMD from the Start menu to the Start menu

• In the command line tool, type something like "SQLCMD -d CMS -i C:\Carnotaurus\Data\script.sql" . Here, CMS is the name of the database that I intend to restore, and "C:\Carnotaurus\Data\script.sql" is the full path to the script recovery file that was generated by the generated script in SMSS.

Hope this clears some missing steps.

Answer 6

I also encountered a similar problem in SQL Server 2012, since my file size was 70 MB.

The solution that worked for me, Extend the log file to Unlimited.

• Open SQL Server Management Studio
• In the Object Explorer, right-click the database name and select Properties
• In the "File" section, resize "Size" and "Log Size" to an unlimited size.