0

I am using SQL Server 2012, I have a script to update the DB, that script is approx 270 MB in size and contains some about 22,00,000 lines.

Error :

enter image description here

Error message : System.OutOfMemoryException

Also,

I tried Transact-SQL command Query method but at particular line its giving error message.

Sqlcmd: Error: Syntax error at line 709918 in file 'database.sql'.

But there is not syntax error.

Please provide any solution on this.

jarlh
  • 42,561
  • 8
  • 45
  • 63
Pramod Raut
  • 677
  • 3
  • 9
  • 22
  • 1
    Get more / assign more memory? Simplify the script into multiple smaller steps? – Allan S. Hansen Sep 14 '16 at 09:15
  • can you run the script from a command line? not in management studio, google SQL run script command line - etc. - sorry you tried that and got syntax error - is there really no syntax error? – Cato Sep 14 '16 at 09:15
  • Possible duplicate of [How do you import a large MS SQL .sql file?](http://stackoverflow.com/questions/431913/how-do-you-import-a-large-ms-sql-sql-file) – mxix Sep 14 '16 at 09:17

2 Answers2

1

That's already quite big for SQL Management Studio, especially if the script contains long lines and anything too complex.

Your best options are probably:

  • Use sqlcmd instead of SQL Management Studio
  • Break the script down into multiple files you can execute one after another

Don't forget that even just for the file, you need at least 550 MiB of memory, since Management Studio is a .NET application and uses Unicode. And then there's the GUI, and the syntax analysis, and parsing... And since the script isn't streamed by SMSS, unlike SqlCmd, you will likely have the whole file in memory at least twice, and you're already getting close to the limit of the 32-bit application, especially on an 32-bit OS...

Luaan
  • 62,244
  • 7
  • 97
  • 116
0

No Need to do any manual splitting or any command line execution.

Just download this software - Big SQL Script File Runner

It will save your full day.

Pramod Raut
  • 677
  • 3
  • 9
  • 22