Reduce the Size of the MDF File

Question

Hi, I have deleted a lot of records from one of my databases and the mdf file still says the same size as before the records were deleted.  I would have thought it would have decreased a lot after the deletion of a large amount of data.  Do I need to shrink the mdf file?  If so, are there any problems, like loss of data, when shrinking the mdf file?  What is the best way to shrink it?  Backup the mdf file?

Thank You!

Answer 1

By design, the mdf will not reduce in size when you delete data from the database. Think of it as a footprint which grows as more data is added but when deleted, the footprint stays the same. Increasing the size of a file is quite an expensive operation so its a good idea to minimise this (by setting appropriate file sizes on db creation to allow for growth) and the thinking behind not shrinking files when data is deleted.

There isn't a performance hit from having a large mdf which isn't full of data and your database backups will only be the size of the data not the file, so i'd be tempted to leave the database as it is. However, you can shrink the mdf (or ldf for that matter) by issuing DBCC SHRINKFILE(YourFile, TRUNCATEONLY). This won't result in loss of data either.

---

every day is a school day

Answer 2

And for the sake of completeness: I completely agree that you should avoid shrinking the database files unless you REALLY need that space back or you know that it won't grow that big again, but be aware that there is an option 'AUTO SHRINK' at the database level which you can set to ON.

Also be aware of fragmentations - both at the data (indexes) and database file level. I recommend rebuilding your indexes after deleting that many records and updating the statistics for optimal performance.

---

Varsham Papikian, New England SQL Server User Group Executive Board, USA
New England SQL Server User Group; My LinkedIn page
Please remember to click the "Mark as Answer" button if a post helps you!

Answer 3

Here are some thoughts: http://www.karaszi.com/SQLServer/info_dont_shrink.asp

---

Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi

Answer 4

I think "Auto Shrink" option should not be configured as it consumes a lot of resources on the database instance. until you are convinced that "Auto Shrink" is the option you need to configured, it should remains OFF.

If the data you moved out of the DB is one time operation, then you can issue a DBCC SHRINKFILE. However, I prefer to keep more spaces in my data and log files rather than to configure auto shrink / auto grow.

Just my personal thought.. Not to hurt anyone...

Hope this helps.

---

SKG: Please Marked as Answered, if it resolves your issue.

Answer 5

Thank You!!