How to execute 3GB SQL file (Micrsoft SQL Server)?

Question

I have a big SQL file that does not fit into memory and that needs to be executed against Microsoft SQL Server 2008. It seems that the sqlcmd.exe tool always loads it into memory first which is impossible in this case. Any ideas?

unfortunately, i cannot split the script because it is generated by Redgates excellent SQL Data Compare. the entire script is one big transaction and i want to leave it that way. i had never thought that having a gigantic script is unusual because having much data is common in the database world. the script is 3gb in size.

Answer 1

What/who created the SQL script? Get whatever created the file to split the script up into logic chunks, by either transaction or statement (depending on how the file is structured). If the source can't do this, then whip up a script to split the file up logically.

Answer 2

If it is that big, the script is either too complex or is repetitive. In either case, as others have suggested, the only sensible thing is to break it down into manageable chunks.

Is this a one-off exercise or a regular event?

Answer 3

I've had this problem before where the script had an enormous XML String that was being used with OpenXML. The actual SQL was rather minimal, updating some values in a table.

I ended up inserting the data (in chunks) into a temporary table until all the info that was in the XML was stored. Then I ran my update statement.

Added later after more data got posted:

You may want to select large chunks in the tool and have SQL Data compare generate the scripts in chunks. That way you get the transactions. You can select large sections by simply highlighting a range and hitting the space bar.

Answer 4

RedGate's SQL Compare has an option to execute the statements directly, instead of generating a SQL script and executing it later. Is there a reason this wouldn't work - in other words, is there a reason you require a SQL script and can't use the application's "synchronize now" functionality?

Answer 5

1-800-redgate-support.....

or

• break up transaction script into smaller files
• set database in single user mode
• fullbackup of database
• run each smaller script file; if there is a failure: restore backup, fix script, try again
• back out of single user mode, all done

Answer 6

I ran into this problem a few months ago. I generate sync scripts with SQLDataCompare on a weekly and monthly basis for several of our catalog databases and they are routinely larger than 500MB. My solution was writing a VBscript that chops the update script into 50 to 1000 command batches. The problem with this approach is losing the ability to roll back all changes if something breaks halfway into your database update.

Answer 7

I just want to let everybody know that none of the solutions proposed here will work, even red-gate does not have a way to resolve it (unless you write your own as user Matthew).

Breaking up transactions into smaller files is not an option within red-gate sqldatacompare, you can specify the size of the transactions but you can not divide the script in several different files according and following the size of the transactions.

In addition as someone pointed out, once you divide the script in transactions of any given size you desire, you lose the ability to roll back the changes.

What you end up doing is, transaction splitting or not your scrpit file, it it gets very large, as in my case, no text editor out there can read the 1.2GB .sql that my redgate sqldata compare generated. I have tried Vim, LTF, etc none of them get to open it.