Upsizing Data to SQL Server

[Alibaba2003]

The size of the sql server mdf and ldf files after the upsize is 15 times as big as the original access database size???? and I cannot shrink it either .. any clue why?

Thank You

It's Nice to Be Important But It's more Important to BE Nice

 

[gmmastros]

When you upsize an Access database to a SQL Server database, all of you string fields will be converted to nVarChar fields. The n in front of the field type indicates it is a unicode field. unicode is used to display an expanded character set (like japanese and chinese letters). If you don't support any languages other than english, then you should change the data types to varchar.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Alibaba2003]

Thanks George,
Any side effects for converting to varchar.
I get the warning data may be lost?
I tried it on a development version with few tests and
no data was lost.
Any particular scenarios were the data will be
actually "lost"?

thanks
Tareq

It's Nice to Be Important But It's more Important to BE Nice

 

[gmmastros]

If all of your data (in the varchar fields) are *regular* characters, then you shouldn't lose any real data.

The 'data' you are losing is unicode representation of your data.

Since the files are 15 times larger than the original, there must be more going on here. Unicode data takes 2 bytes per character. Non-unicode takes 1 byte per character. So, at best, this would only account for a doubling in size (not 15 times larger).

I recommend that you backup your database before continuing this process. In Enterprise Manager, right click on the database, click on 'all tasks' then click on backup.

Then, do a shrink... right click on database, click 'all tasks', click shrink database, click the check box for 'Move pages to beginning of file before shrinking', then click OK.

There is another forum for SQL Server (Microsoft SQL Server: Programming). The guys over there are VERY sharp, and can probably help more than me.

Hope this helps.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Alibaba2003]

George,
The database is bigger than it was after the update. No clue what is going on .. i posted the question in the SQL programming forum

Thanks again.
Tareq

It's Nice to Be Important But It's more Important to BE Nice