CODEWITHSUNDEEP
sql-server
Thunderhashy
Thunderhashy

Reputation: 5321

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?

Upvotes: 0

Views: 614

Answers (3)

ps.
ps.

Reputation: 4360

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

DBCC SHRINKDATABASE (UserDB, 10);

Upvotes: 1

gbn
gbn

Reputation: 432261

  • 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

Upvotes: 2

Hogan
Hogan

Reputation: 70523

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

Upvotes: 2

Related Questions