When is shrinking a data file acceptable?

Click here to monitor SSC

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>

SQLServerCentral is supported by Red Gate Software Ltd.

Log in :: Register :: Not logged in

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>

Recent Posts

Popular Topics

Home

Home » SQL Server 2008 » SQL Server 2008 - General » When is shrinking a data file acceptable?

Add to briefcase

31 posts, Page 1 of 4

When is shrinking a data file acceptable?

Expand / Collapse

Author

Message

Posted Monday, June 13, 2011 8:00 PM

Forum Newbie

Group: General Forum Members
Last Login: Today @ 8:06 AM
Points: 3, Visits: 227

Hello All,

Long time reader, first time poster.

While I agree that shrinking a data file is a bad thing in most cases, I wonder if it could be a good thing in special circumstances.

I am currently working with a vendor that highly recommends doing a shrink data file or "shrink database" in their documentation once a week. They recommend this because they believe it is beneficial to remove the "white space" in the data file because of high volumes of inserts and deletes. In their documentation for the shrink database task they specify to retain the freed file space in the file and to leave 10% free space(which could be altered). So I don’t see where you would get a lot of filesystem fragmentation here. After that they want us to rebuild indexes and update stats(which we already do) . This would take care of the fragmentation from the shrink.

I am wondering if anyone could think of any ramifications of shrinking data files in this case.

Any input would be appreciated.
Thank you,
Josh

Post #1124708

Jeffrey Williams 3188

Posted Monday, June 13, 2011 8:35 PM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, June 10, 2013 12:13 PM
Points: 4,319, Visits: 9,217

Yes, I can think of ramifications - index fragmentation, file fragmentation - and basically, just a waste of time and resources.

If you are constantly shrinking a file - and then it is growing again, that is going to cause file fragmentation. It also causes index fragmentation, which you then have to perform an index rebuild/reorganize to clear up - which will probably cause the file to grow again.

There is absolutely no performance issues with have extra 'white' space available in a database. In fact, it is often desirable to have that space - not only for future growth and to avoid auto growing that file at the worst time (say 8am Monday morning), but also to manage your index rebuilds without having to suffer the wait for the file to grow.

All in all - that is not very good advice.

The only time you should consider shrinking a file is when there has been an extraordinary growth and you know you will never use that space again. For example, you copied the database to a test system - removed most (or all) of the data to build a blank copy. Or, someone ran a bad query that caused the database to double in size, etc...

Jeffrey Williams
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

Post #1124712

Posted Monday, June 13, 2011 8:35 PM

Old Hand

Group: General Forum Members
Last Login: Saturday, June 15, 2013 9:21 AM
Points: 314, Visits: 1,053

Each operation essentially "undoes" the other in terms of logical fragmentation.

Shrinking the database takes pages from the end first, and moves them to free space in the front. Rebuilding an index will try to lay out the pages on disk in order. Doing both sequentially is worse than just wasting time, because what you can end up with is underlying disk fragmentation.

In short, don't do shrink > rebuild or rebuild > shrink.

You should only do a shrink if you're desperate for disk space and don't have time to wait for your infrastructure team to increase your allocation. Or, for the log files, if you're trying to clean up fragmented VLFs.

allmhuran.com - download the SSMSDeploy addin for SSMS 2008
Blog on sqlservercentral

Post #1124713

Posted Monday, June 13, 2011 11:59 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:18 PM
Points: 38,062, Visits: 30,359

Boaz51 (6/13/2011)

I am currently working with a vendor that highly recommends doing a shrink data file or "shrink database" in their documentation once a week. They recommend this because they believe it is beneficial to remove the "white space" in the data file because of high volumes of inserts and deletes.

They are wrong. There's nothing wrong with free space in the DB, SQL in fact needs it.

It was MS Access where you had to compact and repair on a regular basis if you didn't want the file to become unusable. SQL Server != MS Access.

Regular rebuilds will sort out the partially empty pages problem (from deletes and inserts causing page splits). The shrink-reindex (grow) just wastes a hell of a lot of time and resources for no gain

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 #1124759

Posted Tuesday, June 14, 2011 2:56 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, June 15, 2011 12:19 AM
Points: 125, Visits: 58

As mentioned already Defrag over shrink db :D however i can think of one place where a shrink db could be a good idea. Again this might not be applicable in your case.

Say your using a staging database for some kind of ETL process where every week your loading data from different datasource. There would be no harm and probably some good by shrinking this staging db as the space freed could be used by temp db for the next stage of data cleansing.

Hypothetically speaking of course ...

Post #1124816

Syed Jahanzaib Bin hassan

Posted Tuesday, June 14, 2011 3:40 AM

SSC-Addicted

Group: General Forum Members
Last Login: Monday, July 18, 2011 3:54 AM
Points: 492, Visits: 481

you can do shrink the database in only 1 special circumstance when only 1 door is open ,you are completely lost with space and there is no any other option you have

Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA

My Blog
www.aureus-salah.com

Post #1124839

Posted Tuesday, June 14, 2011 4:48 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:18 PM
Points: 38,062, Visits: 30,359

There are valid reasons to shrink databases (out of space is seldom a good one), but not on a regular basis.

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 #1124866

Posted Tuesday, June 14, 2011 3:19 PM

Forum Newbie

Group: General Forum Members
Last Login: Today @ 8:06 AM
Points: 3, Visits: 227

I completely agree with you guys and did before I even posted. I was trying to gather as much technical detail as I can so that I am prepared to combat their recommendation.

GilaMonster (6/13/2011)

[quote]
Regular rebuilds will sort out the partially empty pages problem (from deletes and inserts causing page splits). The shrink-reindex (grow) just wastes a hell of a lot of time and resources for no gain

allmhuran (6/13/2011)

Each operation essentially "undoes" the other in terms of logical fragmentation.

Shrinking the database takes pages from the end first, and moves them to free space in the front. Rebuilding an index will try to lay out the pages on disk in order. Doing both sequentially is worse than just wasting time, because what you can end up with is underlying disk fragmentation.

I understand how it could be redundant to move a page up to the front with the shrink then turn around and move it with the rebuild. I guess I need to get more info from them on exactly how much data is being inserted and deleted from these tables. If it helps at all, this is Enterprise Vault and the DBs can range from 20 gig to 180 gig.

I dont see how you would get underlying disk fragmentation if you are not releasing any of the file free space back to the OS. I do understand that if the file were full and we were rebuilding large indexes while autogrowing 1MB at a time or we released all the free space and grew it again, we would be introducing physical fragmentation. In this case, there would be plenty of space in the data file. We also dont have any control over the physical disk fragmentation, i believe that is handled on the SAN by that group. We are pretty much presented a LUN and thats about as much say so as we have for disk. Could you please elaborate on that a little more where you could introduce physical fragmentation here.

Thanks

Post #1125361

Posted Wednesday, June 15, 2011 12:08 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, June 15, 2011 12:19 AM
Points: 125, Visits: 58

I wonder if you should just partition the huge tables with index aligned to the partitions and using a high fill factor and then just forget about it.

If your worried about fragmentation and the impact it has on I/O there are other factors that should be taken into account before your going into discussions of shrink db. E.g choosing the right raid to help reduce the impact of fragmentation to some extent , with read ahead optimization you would be able to get away with it.
Also things like partitioned tables etc help make sure historical data doesn't require the same amount of management as new data. Capacity planning is another factor , if your discussing the shrink db option too early in your implementation your sizing needs to be re-looked. Drop indexes which are not used and basically monitor the perf mon counters to see the rate at which reads and writes are happening and check if the indexes get fragment too often. Shrink db is just a quick and dirty solution for when you dont want to tune your database. Its not always the right answer just a work around.

Post #1125467

Posted Wednesday, June 15, 2011 3:27 AM

SSC Eights!

Group: General Forum Members
Last Login: Thursday, June 13, 2013 3:50 AM
Points: 969, Visits: 652

You seem to have a pretty good handle on this, but with all due respect I think you may be going about this the wrong way. Your time is precious and should be spent doing more productive things, step back a little and take a look at the bigger picture...

Your company are paying them (probably a substantial amount) for their service and they have supplied this documentation to you. It is not unreasonable to ask them to supply the reasons why you should implement their suggestions and ask them to back it up with the relavant Microsoft technical articles and baselines that prove their statements are indeed correct.

Of course they will not be able to do this, then you can step in with collated advice from this thread as your counter argument. Definitely list this MS technical article in your arguement - http://technet.microsoft.com/en-us/magazine/2008.08.database.aspx

We've all been there and unfortunately sometime it becomes more of a political argument than a technical one at times. Companies tend to favour external knowledge rather than internal purely because they pay more for it.

Good luck!

Hope this helps,
Rich

Post #1125540

« Prev Topic | Next Topic »

Add to briefcase

31 posts, Page 1 of 4

Permissions

Expand / Collapse