Is it advisable to shrink the data file?

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Is it advisable to shrink the data file?

Is it advisable to shrink the data file?

Rate Topic

Display Mode

Topic Options

Author

Message

jeet_personal

Posted Sunday, June 07, 2009 4:13 PM

Grasshopper

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

GilaMonster

Posted Sunday, June 07, 2009 4:16 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:07 PM
Points: 37,687, Visits: 29,946

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

Alberto dbLearner

Posted Monday, June 08, 2009 4:15 PM

SSC-Addicted

Group: General Forum Members
Last Login: Wednesday, April 03, 2013 6:22 PM
Points: 480, Visits: 210

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

Tim Walker.

Posted Monday, June 08, 2009 4:25 PM

SSC Veteran

Group: General Forum Members
Last Login: Today @ 4:35 PM
Points: 258, Visits: 808

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

george sibbald

Posted Monday, June 08, 2009 4:42 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 4:02 PM
Points: 5,269, Visits: 11,203

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 »

Permissions