Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Is it advisable to shrink the data file? Expand / Collapse
Posted Sunday, June 7, 2009 4:13 PM


Group: General Forum Members
Last Login: Wednesday, December 21, 2011 11:20 AM
Points: 17, Visits: 96

Is it advisable to shrink .mdf file of a database? What are the advantage and disadvantage of this?
Post #730438
Posted Sunday, June 7, 2009 4:16 PM



Group: General Forum Members
Last Login: 2 days ago @ 5:18 AM
Points: 45,619, Visits: 44,147
It is not.

Shrinking causes massive fragmentation and will just result in the data file growing again next time data gets added. When that happens, the entire system will slow down as the file is expanded. Also repeated shrinks and grows will cause fragmentation at the file-system level, which is hard to fix.

See -

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #730439
Posted Monday, June 8, 2009 4:15 PM



Group: General Forum Members
Last Login: Thursday, June 9, 2016 7:29 PM
Points: 490, Visits: 227
Gail, what happens when, after monitoring your data growth for years, you discover that it only fills a little percentage of the datafile, and you are getting short of disk space? In that case, isn't it better to release some space to the file system?

Post #731010
Posted Monday, June 8, 2009 4:25 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, October 25, 2016 10:42 AM
Points: 292, Visits: 967
Gails advice here is correct most of the time. In NORMAL use a database stabalises at a particular size.

HOWEVER if you know your database is normally 50% fulll at 1GB and you took exceptional action that took it to 10GB, and you now have 50% of 1GB again you should shrink back to 1GB.

This should remove any O/S fragmentation created above 1GB.


Post #731016
Posted Monday, June 8, 2009 4:42 PM


Group: General Forum Members
Last Login: Wednesday, November 16, 2016 9:57 AM
Points: 6,147, Visits: 13,680
If I may jump in and deprive Gail of a point...............(She's got plenty)

If your database data file is obviously way over sized and you you know you will never need the file that size and besides you need the disk space, then yes, as Tim says, that is an occasion when you can shrink the file. Of course if you do, don't shrink the file down to its minimum possible and immediately follow it with a reindex.

An option is to use the truncateonly option of dbcc shrinkfile (don't use shrink database). This does not move data about and just trims the file to the last used extent. Depending where this extent is it might recover no space, about what you wanted, or more than you wanted. If more than you wanted increase the file again manually to the desired size. For a large database this method can be much quicker and less intrusive.


Post #731023
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse