Sql-server – An mdf file of a Sql server 2008 database is bigger than expected

Yes. You can increase the mdf initial size and SQL Server will grow the file to that size. It's quite safe to do this on a live database, though choose a quiet time! You should find the size increase is very quick. I just grew a test database from 128MB to 4GB and it took 2 seconds.

An initial size of 4GB seems reasonable given the current size of the database. If you have lots of disk space why not set the growth to something high, e.g. 2GB or even 4GB? Growing the database in large increments reduces the physical fragmentation of the mdf file.

You don't need a weekly check as SQL Server will just keep growing the file. Just make sure you don't run out of disk space.

JR

PS I've just seen Aaron's reply. I differ from him in that I have no problem with autogrowth of the database. However you want to set the autogrowth parameters to avoid lots of small increases. 10% is the default, and I think that's far too small for most databases.

PPS that log size looks a bit big. Is the database set to "Full logging", and if so are you sure the database is being backed up? If the log file size gets out of hand you can use "dbcc shrinkfile" to reduce it. See the Books Online for details.

No, using DBCC SHRINKFILE ('filename', target_size) is the right way to do it.

If you want to do it in "chunks", you can either set progressively smaller target sizes, or just let it run as long as you can before it gets cancelled.

A few comments:

• Put a reasonable target size, with some margin of allowed free space. Maybe 90GB total for 75GB of data?
• While the shrink is running, check the activity monitor to see if the SPID is being blocked. If there is an open transaction on a page at the very end of the file, then shrink won't be able to move it until that transaction commits or is rolled back.
• Is the spid actually making progress? (The CPU and IO numbers are changing)
• Shrink can sometimes take a very, very long time, but it should save its progress (meaning it moves 1 page at a time, and when it gets cancelled, all the completed page moves have already been done)
• After cancelling the shrink, try doing a DBCC SHRINKFILE ('filename', TRUNCATEONLY). It should recover all the space its already freed at the end of the file (see my prior point)
• If you get desperate, try restarting SQL in single-user mode, so you know that nothing else is working against the db at that time (obviously, this could be impossible on a prod server)
• Once you are able to complete the shrink, make sure to do a full reindex on the database to eliminate the fragmentation that the shrink creates. This may reclaim some of the space you just freed.
• If you still can't get the shrink to work, check out some of the discussion on this SO question. There are apparently some situations where shrinks might not progress.