Huge size difference between original db and new db

MarkThorntonMarkThornton Posts: 21
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?
Mark Thornton
Database Developer
School of Clinical Medicine
University of Cambridge


  • 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 - that might give you an indication of whether this is the case here.

    Hope that helps,
    Robert Chipperfield
    Red Gate
  • You can also directly compare the table size to pinpoint
    It could be the unreleased space, or the index
    EXEC sp_msforeachtable 'sp_spaceused "?"'
    Jerry Hung
Sign In or Register to comment.