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 ««1234»»»

When is shrinking a data file acceptable? Expand / Collapse
Author
Message
Posted Wednesday, June 15, 2011 5:12 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 4:42 PM
Points: 323, Visits: 1,201
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


allmhuran.com - download the SSMSDeploy addin for SSMS 2008
Blog on sqlservercentral
Post #1125598
Posted Wednesday, June 15, 2011 12:05 PM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 9:56 AM
Points: 3, Visits: 258
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.
Post #1125972
Posted Thursday, June 16, 2011 7:17 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 7:16 PM
Points: 4,471, Visits: 6,401
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 at GMail
Post #1126507
Posted Thursday, June 16, 2011 7:19 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 4:28 AM
Points: 3,113, Visits: 11,537
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


Post #1126511
Posted Thursday, June 16, 2011 12:26 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Sunday, September 28, 2014 1:41 PM
Points: 959, Visits: 2,888
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
Post #1126837
Posted Thursday, June 16, 2011 2:07 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, November 12, 2014 8:36 AM
Points: 822, Visits: 1,205
Not to beat the proverbial dead horse here but I thought I would point out one such situation where we do a regular shrink of database files. I am sure there will be more than a few disagreements to this but it works for us all the same.

We have a database that normally runs in the range of about 100 GB and it stays that way most of the month. Once a month however they run a process that increases the size to nearly a TB. Now I know what you are thinking it must be a poorly designed process but this process is for a Power Substation and it pulls in data from something like 90 different servers and runs some calculations that when I tried to understand them frankly made me feel slightly insecure about myself.

Now my belief with this situation is the DB is used 24/7/365 and I want to minimize impact. So after the process I reduce the size thus reducing the amount time it takes to run a full backup and other tasks. I am sure many will disagree but alas I do what works for the situation.


Dan

If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
Post #1126942
Posted Thursday, June 16, 2011 2:18 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, November 27, 2014 4:23 AM
Points: 20,584, Visits: 9,624
How does that reduce backup time?

And by how much?
Post #1126947
Posted Thursday, June 16, 2011 2:33 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, November 12, 2014 8:36 AM
Points: 822, Visits: 1,205
The Full DB Backup runs about an hour when it is at a TB but less then 10 minutes when it is reduced to the 100GB size.

Dan

If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
Post #1126954
Posted Thursday, June 16, 2011 3:02 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, December 12, 2014 12:03 PM
Points: 4,363, Visits: 9,547
Dan.Humphries (6/16/2011)
The Full DB Backup runs about an hour when it is at a TB but less then 10 minutes when it is reduced to the 100GB size.


I don't see how - backup only backs up what is used. It doesn't matter how large the file actually is.

So, if it only takes 10 minutes to backup 100GB - and an hour to back 1TB, it is probably backing up almost 1TB of data.



Jeffrey Williams
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #1126969
Posted Thursday, June 16, 2011 3:16 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, November 27, 2014 4:23 AM
Points: 20,584, Visits: 9,624
Ya but to pick only the used pages you must scan all pages (even if only the header). So that's an extra 200 - 900GB??? to extract from the san.


That's the only way I see to have such a huge difference in the times... for the same backup file size.
Post #1126972
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse