Discrepency in the size of mdf files in SQL server

I am having 2 .bak files for the same database in MSSQL Server. One is the test snapshot and the other is the production snapshot. The test one is about 500 MB and the production one is about 10 GB. This is because the Test one has many tables truncated. When I开发者_如何转开发 restore them in sql server a .mdf file is created at \Microsoft SQL Server\MSSQL.1\MSSQL\Data folder. But I see that the sze of the .mdf file is about 10 GB even when I restore only the test snapshot. Since the test snapshot has many tables truncated and data is less, I would assume that the the size of the .mdf file should be less than that for production snapshot. But that is not the case. Does sql server reserve space in the mdf file and hence it is the same size for both the versions?

Yes it does reserve space. You can see this in the create database page and later in the properties pages.

• MDF = space reserved
• bak = 8k pages with data

When the database is restored, it reads the size from sys.database_files. The pages are put back into the same relative place.

Unless you are really short of disk space, I'd leave it... but you can shrink the files if it makes you feel better

You are using (ballpark figures) 64,000 pages but have reserved 12,800,000

i think you need to run the following on your DB.

DBCC SHRINKDATABASE (UserDB, 10);