asked
DBCC ShrinkFile - better to do lots of little shrinks, or one big shrink?
Is it better to do one big shrink, or lots of little shrinks? The back story. A database was created a while ago and is currently sized at around 200GB; only 120GB is used. We have been advised by the suppliers of the software that's served by this that we need to defrag the hard disks (98% fragged, apparently). But in order to do that, I need to shrink the DB file by around 20GB (it's virtually the only thing on that drive...) So. Better to do it all at once, or in 20 1GB slices? Doing it in smaller chunks allows for a bit more control over server load; however, the server is rarely stressed. My concerns are more around what it's going to do to the data within the database (if you see what I mean). The data within the database is almost entirely unfragmented, which is nice. However, an initial attempt to shrink the file (by just reclaiming unused space) resulted in no change in size, so I assume that the data is currently spread throughout the DB file. I realise there are no hard & fast answers here (so no bonus points for saying "It depends"), but what is the general feeling on this? What would *you* do?
"Need to shrink the DB file..." - because we have a short list of software we're allowed to use to do this stuff, and it's refusing to attempt the job without 10% free disk space.
Whichever methodology you choose... re-size the file at the end to prevent this possibility next time :)
@Blackhawk-17 assuming, that is, that re-sizing the file back up to its original size doesn't introduce more fragmentation... ;-)
To arrive at that much fragmentation the growth must have been set to 1mb or something (might be worth checking), but if you are really going to do a shrink operation, I'd do one, not a series. A series is more likely to add to your fragmentation problems.
@Grant - I assume you're talking about the fragmentation of the data within the database, rather than the files... As @Trad says, good to know.
Is it ldf or mdf thats too big? Are you in simple mode? Is it all backed up so that the truncate can succeed? Personally I'd try a 1GB step, if that worked well then go for 2GB then 4GB just to take it easy on the system
Also, just to make sure Paul Randall never sleeps again you could write a cursor to do it all :D
Is Paul likely to read this? Can we summon him, just to make sure? ;-)
When the need arises for me to shrink a datafile (after a huge delete or some user reloaded the same 10 gig dozens of times) I use a loop where I put in my current size, destination size, size to shrink by (500 mb), name of the file, etc and have it loop through. That way I can see the progress as it shrinks. I can provide you a copy of the script when I get back to my office if you would like.
*... (98% fragged apparantly).....* Do you only see the DB side of things and cannot confirm their claims on fragmentation? I couldn't believe a claim like that without proof. If the MDF is 200GB, was it presized to that or has it grown? If you could afford the space and time to do it, create a new filegroup (presized to what you will need + breathing room) and file and move all the objects from FG1 to FG2. You can then empty FG1 and shrink that into oblivion. Otherwise I would do the same thing as @Fatherjack. Something I would leave to off hours though.
I confirmed their claims by looking over the Infrastructure guy's shoulder... MDF grown over the years. Not that I've got any proof of that, but the size & growth factor is a bit too weird for it not to have been "by design". Space? What's that then? ;-)
That's a discussion for another day. I think next Tuesday, to be specific.
@WilliamD - Good idea with the FG solution. @ThomasRushton - Can you set up a secondary FG and move a subset of the data to ease your space? Perhaps an active table or two? I would go with a single shrink personally.
How much time do you have to accomplish this and can the dB be taken down... and how much do you trust your backups? A radical approach may be to back up the dB, drop it, delete the MDF, de-fragment the drive, and restore from the back up. This might be overkill for your scenario but you can be assured of maximum de-fragmenting. Secondly, can SQL Server be stopped during de-fragmentation? You're more likely to achieve better results and in less time.
24/7 environment - downtime? Hah! Chance would be a fine thing... Nice idea, though.
