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?
views:
82answers:
3
+2
A:
Yes it does reserve space. You can see this in the create database page and later in the properties pages.
Hogan
2010-01-15 19:05:11
How can we tell it to not reserve the space?
Harsha
2010-01-15 19:27:01
err... you have to detach and re-attach I think. Try this: right click db, select tasks, shrink, database. If that does not work you can try shrink files. (In sql management studio 2008)
Hogan
2010-01-15 19:29:34
But I can do this after only after I restore the db. I need to do it before restoring db.
Harsha
2010-01-15 19:49:07
Create a new DB -- if you need the same name, rename to old one and create one with the same name.
Hogan
2010-01-15 20:00:46
+2
A:
- 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
gbn
2010-01-15 19:38:07