When is shrinking a data file acceptable?

Question

When is shrinking a data file acceptable?

Boaz51

SSC Enthusiast

Points: 109
More actions
June 13, 2011 at 8:00 pm

#237898

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

Viewing 15 posts - 1 through 15 (of 30 total)

You must be logged in to reply to this topic. Login to reply

Jeffrey Williams SSC Guru Points: 90244 More actions · Answer 1

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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

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

Don Halloran SSCertifiable Points: 5937 More actions · Answer 2

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.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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, 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

Jayanth Kurup SSC Eights! Points: 829 More actions · Answer 4

As mentioned already Defrag over shrink db 😀 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 ...

Syed Jahanzaib Bin hassan SSChampion Points: 11410 More actions · Answer 5

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

My Blog
www.aureus-salah.com

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

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, 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

Boaz51 SSC Enthusiast Points: 109 More actions · Answer 7

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)

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

Jayanth Kurup SSC Eights! Points: 829 More actions · Answer 8

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.

RichardDouglas SSCarpal Tunnel Points: 4029 More actions · Answer 9

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

[p]
[/p]

Don Halloran SSCertifiable Points: 5937 More actions · Answer 10

Could you please elaborate on that a little more where you could introduce physical fragmentation here.

Just as you said, it's going to happen if you're releasing free space and especially if you're using small growth increments. I've seen a few places with shrinks as part of their "maintenance plans", and these three decisions often seem to go together. If you're not doing that then I don't think you'll be having much impact on disk fragmentation. You'll just be wasting time 😉

Boaz51 SSC Enthusiast Points: 109 More actions · Answer 11

RichardDouglas (6/15/2011)
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!

You are absolutely right. It can be very frustrating when you are the one caught in between the vendor and the business. Especially when asking these questions, the business side will sometimes see it as you are being difficult and hard to work with even though they don't understand. But this can typically be cured with the proper explanation.

Thank you all for your input. I very much appreciate it.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 12

1) please tell the vendor that TheSQLGuru said they have their heads up their collective @sses. Also tell them that they need to get me or some other qualified consultant on board to review their database application for what is likely a HUGE range of suboptimal and out right bad issues. I just helped one vendor achieve a ~70% reduction in SQL Server waits and load from ONE SIMPLE CHANGE - and there is MUCH more to go with them!!

2) NEVER EVER EVER leave a database file at 1MB growth factor!!!

3) Always try to figure out how big a database will be 12-18 months down the road (data AND index sizes combined) and make all files appropriate for that number right now. Monitor at least quarterly. Autogrowth should be an EXCEPTIONAL event, not used to manaage your database size by standard.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 13

Sometimes (often) you just need to ignore what a vendor says because you know more than them.

I ignore vendors all the time when they start spouting nonsense like that.

You are not alone; read through the thread below if it offers any comfort. You're on there!

That Darn Vendor

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=139011

tfifield SSCrazy Eights Points: 9655 More actions · Answer 14

TheSQLGuru (6/16/2011)
1) please tell the vendor that TheSQLGuru said they have their heads up their collective @sses. Also tell them that they need to get me or some other qualified consultant on board to review their database application for what is likely a HUGE range of suboptimal and out right bad issues. I just helped one vendor achieve a ~70% reduction in SQL Server waits and load from ONE SIMPLE CHANGE - and there is MUCH more to go with them!!
2) NEVER EVER EVER leave a database file at 1MB growth factor!!!
3) Always try to figure out how big a database will be 12-18 months down the road (data AND index sizes combined) and make all files appropriate for that number right now. Monitor at least quarterly. Autogrowth should be an EXCEPTIONAL event, not used to manaage your database size by standard.

I'm a little late to jump on the band wagon here. I'm totally with Kevin on this one. I deal with a vendor that I had to train on these points. The vendor was actually a dealer for a major Point Of Sale system.

Their usual database setup was 1MB growth for both data and log files. Their usual maintenance plan had shrinking the database after re-building indexes at night. Ouch!!

These guys knew the app very well and were very well intentioned, but didn't have a clue about database setup or maintenance. After a few customer complaints on performance and me fixing only those 2 points - database growth and NOT shrinking the database after the index re-build, I've found that their usual installations are exactly what I recommended and we're all a happy lot now.

Todd Fifield