DTS Import creates abnormally large DB size

[LampknLn45]

I have looked and looked through the FAQs and forums and cannot find an answer to this.

I have an Access 2000 database that I need to convert to SQL Server 7. I create a new database in SQL Server, then use the Upsizing Wizard in Access to convert my tables, relationships, etc. (no data). I have created a DTS package to import all of the data and it does that without fail. However, what was a 20MB database in Access is now about a 230MB database in SQL Server. I have tried playing with the "Insert Batch Size" setting (which helped bring the size down to 60MB - while considerably slowing down the import process) and the SHRINKDATABASE and SHRINKFILE commands. I've also tried the "BACKUP LOG ... WITH TRUNCATE_ONLY" command. I tried all different combinations of these and nothing seems to work. I've converted 2 other databases and not had this problem. Has anyone else noticed this or is it possible that this is normal? Anyone know how to prevent this from happening?

Thanks for reading,
LampknLn45

 

[JayKusch]

Is that the DB size or the combined DB and Tlog added together. A TLog can grow quite large on a lengthy Insert process.

Thanks

J. Kusch

 

[LampknLn45]

(where "x" is the name of my database)

My x_Log file is around 1 MB. My x_Data file is around 55 MB. When I run "sp_helpdb x" the db_size is 55.63 MB. Again, this is a big jump from the 20 MB size in Access.

Thanks,
LampknLn45

 

[JayKusch]

My be a difference in the datatypes.

Thanks

J. Kusch

 

[LampknLn45]

That is probably the case. Many of my text fields from Access were converted to nvarchar fields. Still, I wouldn't expect to see such a big difference. I've converted other DB's from Access to SQL Server and not seen this.
Oh well, 55 MB isn't huge (not like I'm running out of space). Still, I'd like to understand why this happens.

Thanks,
Ryan

 

[JayKusch]

Did it create NVarChar lenghts of 8000??

Thanks

J. Kusch

 

[LampknLn45]

Ha, ha! Nope. Just normal lengths of 25, 10, 8, etc. Again, none of this makes much sense. I think I'm ready to just accept it and move on.

Thanks,
LampknLn45