question

ThomasRushton avatar image
ThomasRushton 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?
sql-server-2005dbccshrink-database
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

"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.
0 Likes 0 ·
Sorry. It's the MDF that's oversized.
0 Likes 0 ·
Whichever methodology you choose... re-size the file at the end to prevent this possibility next time :)
0 Likes 0 ·
@Blackhawk-17 assuming, that is, that re-sizing the file back up to its original size doesn't introduce more fragmentation... ;-)
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
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.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

That is good to know that a series could add to fragmentation.
0 Likes 0 ·
@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.
0 Likes 0 ·
Fatherjack avatar image
Fatherjack answered
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
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Also, just to make sure Paul Randall never sleeps again you could write a cursor to do it all :D
1 Like 1 ·
Is Paul likely to read this? Can we summon him, just to make sure? ;-)
0 Likes 0 ·
Tim avatar image
Tim answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WilliamD avatar image
WilliamD answered
*... (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.
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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? ;-)
0 Likes 0 ·
Have you looked at offloading some data while you're at it? old log tables or some such that are filling up but no longer needed? think of the backup space savings.....
0 Likes 0 ·
That's a discussion for another day. I think next Tuesday, to be specific.
0 Likes 0 ·
@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.
0 Likes 0 ·
Blackhawk-17 avatar image
Blackhawk-17 answered
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.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

24/7 environment - downtime? Hah! Chance would be a fine thing... Nice idea, though.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.