Space Requirement for Transfer

jbsound

I've run into some issues transferring a database from a SQL Server 2000 instance to a SQL Server 2005 Express instance on a laptop.

The source SQL Server 2000 database is 8 Gig large and before freeing up some space on the laptop, I ran into a problem with the transfer process aborting with a 'ran out of space' error.

This leads me to the question:
What are the space requirements for a database transfer? Can you elaborate on what we need to consider in terms of space?

Thanks,

JB

Brian Donahue

Hello JB,

Sorry I don't have a firm answer. It depends on how much data you're comparing and how much data exists in the largest individual table you're transferring. I wouldn't think it would be larger than the total size of the database, though!

Are you remembering to dispose all objects that implement IDisposable, such as ComparisonSession, when you're done with them? These could be taking up temporary file storage unnecessarily on your hard disk.

jbsound

Thanks, Brian!

Individual objects are disposed as long as the are not needed anymore, so we should be safe there.

After a bit more testing I am finding that the transfer process actually requires a significant amount more space than that of the actual source database, which in this case is 8 Gig.

We have a number of tables in that database that contain text and image fields. It looks to me like the 8 Gigs file size that SQL Server reports actually requires a significant amount above that to transfer the data. I'm not done fully testing, but so far I've run out of 23 Gigs hard drive space left on that laptop at some point in the transfer process.

The most significant hard drive space increase happens when some of the larger databases have their data compared. We could break this process up into several processes, but I'd need to evaluate what impact it would have on the overall development process for the project.

Question:
When we are done comparing a single table and we would move on to the next, were you referring to disposing the ComparisonSession right then? Wouldn't this mean that I would have to act on the results of the ComparisonSession before I move on to the next table?

JB

Brian Donahue

Hello,

I don't think you have any control over the table part. Disposing the ComparisonSession would pretty much spell game over for any processing that you wanted to do with the data.

I don't know why there is so much data around.

jbsound

That's pretty much what I figured.

And, the verdict is in: 24 Gigs worth of temporary files for an 8 Gig large SQL Server database transfer. Wow!

Again, the data in the database is made up of normal text and number fields, plus image fields that contain scanned images.

What seems to be a mystery at the moment is what all takes up that much space. I looked at some of the smaller temp files and they seem to either contain the actual data, or seem to contain the SQL scripts to recreate the data on the target database. So they seem to be legitimate files that one would think are needed to complete the job.

JB

James Moore

Hi JB,

With SQL Data Compare 5 (and its toolkit) you can specify a WHERE clause to use during the data selection - using this you might well be able to split your database down into smaller chunks and transfer this in parts.

The reason for the large amount of data on disk is the number of indexes built over the data allowing for quick selection etc. However 24Gb of temp files does seem like quite a lot - if you clear out the temp files directory and run the comparison using the UI do you still get as much data stored in temporary files?

The structure of the databases will also be on disk as the Data Compare engine relies on SQL Compare (so field type conversions can be done correctly).

Also if you have space availible somewhere else (even if it is on a network drive - which will slow things down massively) you can use the RGTEMP environmental variable to specify where SQL Data Compare puts its temporary files.

Hope this helps

- James
--
James Moore
Software Engineer
Red Gate Software Ltd

jbsound

Thanks, James, it does help a bit in understanding how the toolkit operates.

I would have to run the UI comparison to see whether the same space requirements apply. It's a bit tricky to pull off, as I have to do this with a test environment (client desperately needs that laptop where all this is happening on, so it's not available for further testing).

The RGTEMP solution was something I was asking about, as we were trying to go a different route with the transfer. What we ended up doing (before I had the RGTEMP suggestion) is in a way the same thing. We took a workstation with the needed space and ran the transfer from there, again, this was not with the UI, but with the custom tool I built for the client. Essentially worked and that is where we found out how much space was really required.

JB