Today i was battling in the trenches while trying to fix a deployment issue on a foreign webhost with a support team that had a lead time of 3 days on a support ticket when i came across a very annoying issue. While trying to import a large MSSQL script (140mb) i was having a repeated dual to the death with the error “701 Insufficient Memory” and his evil twin (kind of like the white dreadlock guys in the Matrix) - “System.OutOfMemoryException” in SQL management studio. But never fear, there is a solution, and its really simple.
A bit of background
One of the greatest free tools to come from Microsoft in the last couple of years in my opinion is the Database Publishing Wizard. It generates a single .sql script for your entire database – schema and data together. This is a god send, as quite often when working with a non-dedicated server you can have very limited access to the database itself (sometimes just a web interface) and this allows you to run one script command and you on your way – too easy.
Usually you do this when you deploy the first time, and the database is quite small in size so its not an issue – but what if your using it as a way to bring the data back or copy it between two sites. This seems silly but as many of you may know that sometimes bad things happen to good people – this was one of these days.
Attempt #1 – SQL Management Studio
Some of you DBA SQL Sensei's out there will be laughing that i even tried – as this is something I've not done on MS SQL before i didn’t know the rules of the game. I found out the hard way.
Exception of type ‘System.OutOfMemoryException’ was thrown. (mscorlib)
As you can see from the above image, SQL Management Studio didn’t feel like playing ball on this. A quick Google and it was very clear that i was not the only one having this issue, not many people had answers.
However saddened i may have been by this, onwards and upwards.
Attempt #2 – SQL Server Agent Package
So not to be beaten down i thought i may be able to process this as a job with the SQL Server Agent and let it do the job for me. I thought that this was more of a transaction way of doing things and that i wouldn’t run into any memory errors there – surely people run large SQL agent jobs all the time.
Exception of type ‘System.OutOfMemoryException’ was thrown. (mscorlib)
Exactly the same error – no change at all. What a let down. I was starting to get quite frustrated, as although in this blog post i have skipped from one to the other i actually had tried quite a few varieties of the above two attempts to no avail.As i was on a deadline to deliver this i had to keep moving.
Attempt #3 – C30 Simple SQL Bulk Copy
Simple SQL Bulk Copy is one of those utilities that just does what you want when nothing else will. The known issues with SSIS packages not keeping auto-increment ID’s when it does exports are a things of the past. This utility is the Knight in shining armour that you were staying up late at night waiting for.
While i cannot talk highly enough of this utility it doesn’t do views and functions, so it was copying everything but these things and showing an error while copying them (FYI: it will still keep copying everything else so not all will be lost if you have a use for it).
AAAARRRGGHHH!
The solution: SQLCMD.EXE
As most l33t h4x0rs will tell you there is no place like command prompt, terminal or ssh session. Usually if you know the syntax you can do more with less and a lot of the time in a more stable environment.
So I'll keep it really simple: SQLCMD is your new friend. It will process the script one line at a time and kick through it bit by bit – this is a lot slower than any of the other forms of importing but hey Rome wasn’t built in a day.
Simply use the syntax below and you should be on your way:
sqlcmd -S YOURSQLSERVER\INSTANCENAME -i "C:\Your Script.sql”
A special note should be made that my first try of the above failed with a Error 701 – out of memory error. Opening my large script seemed to really mess with Windows XP’s memory and it wasn’t able to properly clear it. After a quick reboot the above worked a treat.