I have a table in a local MS SQL Server database that has 72 columns and over 8 million records.
Most of the columns are floats except for the primary key which is an INT Identity column.
The table also has 3 indexes.
These records are the saved output from a utility I wrote that processed around 100 Billion calculations on different target audiences. I have saved the best and worst results for each target audience for future lookup.
I would now like to move all these records into my staging server's database.
What is the best way to copy transfer these records?
I tried Red Gate, and it just dies and says out of disk space.
My disk has 22 GB free.
Thanks.
Best Answer
Your operation will most likely be a logged operation as you're performing an insert.
Monitor the log file growth and you should see that's what is utilizing your remaining disk space.
Consider inserting using a minimally logged approach, these include:
I have also found an insert to a column with an identity specification will also cause logging to occur.
Links: http://blogs.msdn.com//b/sqlserverstorageengine/archive/2008/02/05/bulk-logging-optimizations-minimal-logging.aspx http://www.sqlmag.com/article/tsql3/minimally-logged-inserts
Also: I would recommend inserting into a heap and then apply your indexes unless your data has been pre-sorted in the source database.
Consider defining the identity value in your source database, select all the data into the destination and then apply the identity specification and set the seed.