Huge size difference between original db and new db

MarkThornton

Earlier today, I created a new, blank database, used SQL Compare to copy across the empty structure of another database, then used SQL Data Compare to copy across the data.

At the end of this process, I noticed the sizes of the data files of the two databases. I was quite surprised by what I found:

Original Database: 318 MB
New Database: 16 MB

As far as I can tell, all the data came across. Please can someone explain this discrepancy?

Robert C

Hi,

If your original database has had larger amounts of data in it in the past, which has since been deleted, it may have a relatively large amount of unused space in the file on disk.

SQL Server doesn't automatically shrink data files, since repeated shrinking and growing can lead to fragmentation.

Have a look at http://msdn.microsoft.com/en-us/library/ms188776.aspx - that might give you an indication of whether this is the case here.

Hope that helps,
Robert

jerryhung

You can also directly compare the table size to pinpoint
It could be the unreleased space, or the index

EXEC sp_msforeachtable 'sp_spaceused "?"'