How can we help you today? How can we help you today?

Huge size difference between original db and new db

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?
MarkThornton
0

Comments

2 comments

Sort by
  • 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
    Robert C
    0
  • 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 "?"'
    jerryhung
    0

Add comment

Please sign in to leave a comment.