SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Shrinking Databases


Shrinking Databases

Author
Message
Steve Jones
Steve Jones
SSC Guru
SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)

Group: Administrators
Points: 151294 Visits: 19455
Comments posted to this topic are about the item Shrinking Databases

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Nakul Vachhrajani
Nakul Vachhrajani
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3360 Visits: 2149
The only time we use shrinking is when we are shipping databases around (by shipping, I mean moving databases to teams in different geographic locations, or even within the network). Other than that, we never use shrinking.
I would agree on making shrinking harder simply because without the knowledge and skill on when & how to use shrinking, it does more bad than good (as you rightly put it, an angel might loose some feathers).
I would propose that shrinking be made possible if the following is true:
1. A trace flag is enabled AND
2. Database is in a single-user mode

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Be courteous. Drive responsibly.

Follow me on
Twitter: @sqltwins
Google Plus: +Nakul
GilaMonster
GilaMonster
SSC Guru
SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)

Group: General Forum Members
Points: 234313 Visits: 46368
Should it be removed? No.

There are valid uses for it. Two weeks ago I sat running shrink against every database on a server (disclaimer: it was a dev server). I sometimes get (large) copies of databases for tuning work and I'll often truncate tables that I'm not interested in and shrink the database. I don't have space for 300+ GB databases on my laptop.

What I would like to see is perhaps a warning on the shrink database/file dialog that mentions side effects.

Should it be removed from the maintenance plan wizard/designer? Absolutely yes. There is just about no justification in existence for a maintenance plan that shrinks databases.

Should autoshrink be removed? Hell, yes! It's the option that should never be enabled (but often is)

Should it be fixed and made smarter? Would be nice, but somehow I don't see it happening. According to Paul, he wrote a smart shrink while he owned that code, and it still didn't go into the product.

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


Michael.Beeby
Michael.Beeby
SSC-Addicted
SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)

Group: General Forum Members
Points: 431 Visits: 680
If you need that space back, then you need that space back. A warning on the side effects and gentle background automated 'heal'.
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19215 Visits: 12426
I almost completely agree with Gail's comments.

Shrinking should not be removed. There are situations where a database has suffered exceptional growth, and there is no need to keep that space allocated. For instance if you forgot to schedule log backups, or after a huge data import job, the log file can be huge. Or if you accidentally populated a table with a cross join instead of an inner join, both the data and the log files will be enormous. That is a legitimate situation for reclaiming space.

Shrinking should not, under any circumstances, be part of regular maintenance. Neither scheduled, nor manually. So it should be deliberately hard to execute. The autoshirink option and the shrink database task in the maintenance wizard should never have been introduced, since, as Steve writes, it is natural to want to reclaim space. Both people with no database background and people with an Access background are bound to think that shrinking is good.

I don't see much need for optimizing the algorithm. Even with an optimal algorithm, shrinking is still a bad idea. The fragmentation is not the only, nor even the biggest problem. The biggest problem (in my opinion) is that after a shrink, the database will always grow back to its normal working size. That results in many autogrow events - and those are not only slow, but also cause fragmentation at the file system level, which no SQL Server maintenance can undo.
I am perfectly fine with using shrink when absolutely needed, then reorganizing or rebuilding my indexes to remove the fragmentation. Sure, that will cause some autogrow events (unless I manually grow the files first). So what? The database will ALWAYS grow after a shrink, better to do that while still in the maintenance window and not during business hours.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
bas de zwart
bas de zwart
Old Hand
Old Hand (376 reputation)Old Hand (376 reputation)Old Hand (376 reputation)Old Hand (376 reputation)Old Hand (376 reputation)Old Hand (376 reputation)Old Hand (376 reputation)Old Hand (376 reputation)

Group: General Forum Members
Points: 376 Visits: 293
Wow. I think I have to disagree for a part.. But that might have to do with the fact that we have different sort of environment than most. Once a month we do a huge transaction in which about a million and a half records are updated. This causes the DB to temporarily grow. No issues there.

Except that this transaction is only about 1% of the normal use of the DB. During the transaction (which is run during the weekend and is not suffering from time constraints) the DB grows with about 10 GB. We cannot change this as it is a third-party tool.

Now I could leave the DB running the rest of the month with 65% free space but that wouldn't be very effective in terms of fragmentation and ad-hoc query performance, not to mention the 10GB of space on the SAN that is unused.
In our case, we shrink the DB after this big transaction so the rest of the month the DB runs with better access times.

Like with all statements, procedures and features of SQL Server: You need to think before you act, not take out features just because some people abuse them. Wink
george sibbald
george sibbald
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25726 Visits: 13701
It should be removed from maintenance plan options as it has no place in day to day database maintenance.

I can just about see a case for autoshrink in dev environments, so leave it as a configurable database option but do not include it in the gui options, and add warnings in the BOL documentation.

Shrink should not be removed as there are legitimate scenarios for using it such as shrinking a log file if a database is set read only, reducing a data file after an archive operation. An ability to be able to reduce file sizes is a must.

Make it more intelligent? Yes would be nice. Perhaps just add a warning on completion informing you you should rebuild your indexes due to fragmentation caused.

In a nutshell, a shrink command is required but don't make it look like a standard practice option.

---------------------------------------------------------------------
GilaMonster
GilaMonster
SSC Guru
SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)

Group: General Forum Members
Points: 234313 Visits: 46368
bas de zwart (12/10/2010)
Now I could leave the DB running the rest of the month with 65% free space but that wouldn't be very effective in terms of fragmentation and ad-hoc query performance


Free space in a database affects neither query performance nor fragmentation.

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


bas de zwart
bas de zwart
Old Hand
Old Hand (376 reputation)Old Hand (376 reputation)Old Hand (376 reputation)Old Hand (376 reputation)Old Hand (376 reputation)Old Hand (376 reputation)Old Hand (376 reputation)Old Hand (376 reputation)

Group: General Forum Members
Points: 376 Visits: 293
GilaMonster (12/10/2010)
bas de zwart (12/10/2010)
Now I could leave the DB running the rest of the month with 65% free space but that wouldn't be very effective in terms of fragmentation and ad-hoc query performance


Free space in a database affects neither query performance nor fragmentation.

Perhaps not on the SQL server level, but surely on the OS level it does?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)

Group: General Forum Members
Points: 234313 Visits: 46368
bas de zwart (12/10/2010)
GilaMonster (12/10/2010)
bas de zwart (12/10/2010)
Now I could leave the DB running the rest of the month with 65% free space but that wouldn't be very effective in terms of fragmentation and ad-hoc query performance


Free space in a database affects neither query performance nor fragmentation.

Perhaps not on the SQL server level, but surely on the OS level it does?


Only in the sense of inefficient use of hard drive space and maybe file-level fragmentation (which shrink-grow cycles do tend to cause more than just grow and leave does)

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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search