Upsized ACCESS to SQL Server DB Size

[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?

A suggestion was to convert all of the nvarchar to varchar.
This didn't help at all.

Any ideas please?

Thank You
Tareq

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

 

[vongrunt]

.LDF is transaction log file - how large is .MDF file and how large was original Access .MDB?

Did you have BLOB/CLOB fields (memo etc) in Access database?

To see space used by individual tables, run this:

create table #blah
( 	name sysname,
	rows char(11),
	reserved varchar(18),
	data varchar(18),
	index_size varchar(18),
	unused varchar(18)
)
exec sp_msforeachtable 'insert into #blah exec sp_spaceused ''?'', @updateusage=true'
select * from #blah order by name
drop table #blah

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.

 

[Alibaba2003]

Thanks Vongrunt,
it appears that the data size holds realistic values.
However the Indices sizes are very unrealistice. I am not sure what is the unused field shows. But i assume its the space for rows deleted.

How do i manage indices and unused space sizes?

Thank you again
Tareq


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

 

[vongrunt]

Simple test: pick table with most unrealistic index_size, then rebuild it's indexes with:

DBCC DBREINDEX('tablename')

After crunching is done, check used space again. If index_size decreased significantly... bingo.

Also: see DBCC statements in Books Online (SHRINKFILE, SHRINKDATABASE).

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.