Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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
Author
Message
Posted Sunday, June 7, 2009 4:13 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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

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


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:43 PM
Points: 40,411, Visits: 36,861
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 - http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, November 20, 2014 8:57 PM
Points: 480, Visits: 215
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?

Alberto
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: Thursday, November 13, 2014 1:08 AM
Points: 267, Visits: 898
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.

Tim


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

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:11 AM
Points: 5,886, Visits: 13,043
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