unused space in db of SQL server 2000

[mipo]

I am wondering why there is so much unused space in my db. The backup is only 230MB (zipped) but if restored it became more than 6GB. I realized there is a lot of unsed space hanging around.

I checked with sp_spaceused and got following:

database_name database_size unallocated space
----------------------- ------------------ ------------------
madison 6755.13 MB 326.26 MB


reserved data index_size unused
------------------ ---------------------------------- ------------------
6581624 KB 845880 KB 363184 KB 5372560 KB

After executing the following:

EXEC sp_msforeachtable 'DBCC DBREINDEX (''?'')'
DBCC SHRINKDATABASE ('MADISON')
DBCC UPDATEUSAGE ('MADISON')

Only minor size reduction gained:

database_name database_size unallocated space
------------------------------------------ ------------------
madison 5614.69 MB 1100.02 MB


reserved data index_size unused
------------------ ---------------------------------- ------------------
4622000 KB 675928 KB 237656 KB 3708416 KB

So from 5,3 GB to 3,7 Gb it is not a lot freed unused space and the question is why is it not freeing more space or is it just the way db handles space? What is the server hiding? Autoshrink is on in option tab. Anything else that could be done or checked?

Thanks

mipo

[rmiao]

You can shrink db in enterprise manager, worked great for me. Ensure move data to beginning of the file when shrink db.

[skhanal]

Specify a free space target in your SHRINKDATABASE command

DBCC SHRINKDATABASE ('MADISON',10)

Then run it again to release unused spaces as

DBCC SHRINKDATABASE ('MADISON',10,TRUNCATEONLY)

[DLu]

Please also check the log size, I usually need to trancate_only to free the log before shrink the database.

-D