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 12345»»»

Shrinking Databases Expand / Collapse
Author
Message
Posted Thursday, December 09, 2010 9:58 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 4:56 PM
Points: 32,771, Visits: 14,935
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
Post #1032831
Posted Friday, December 10, 2010 12:18 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 10:17 PM
Points: 1,381, Visits: 1,774
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://beyondrelational.com/modules/2/blogs/77/nakuls-blog.aspx
Be courteous. Drive responsibly.

Follow me on
Twitter: @nakulv_sql
Google Plus: +Nakul
Post #1032855
Posted Friday, December 10, 2010 1:52 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 11:47 AM
Points: 41,525, Visits: 34,442
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 2008, MVP
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

Post #1032874
Posted Friday, December 10, 2010 2:07 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 10:26 AM
Points: 200, Visits: 611
If you need that space back, then you need that space back. A warning on the side effects and gentle background automated 'heal'.
Post #1032878
Posted Friday, December 10, 2010 2:17 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:32 PM
Points: 5,794, Visits: 8,006
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
Post #1032882
Posted Friday, December 10, 2010 3:32 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 19, 2013 7:44 AM
Points: 38, Visits: 162
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. ;)
Post #1032912
Posted Friday, December 10, 2010 4:05 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:24 PM
Points: 5,847, Visits: 12,580
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.


---------------------------------------------------------------------

Post #1032926
Posted Friday, December 10, 2010 4:10 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 11:47 AM
Points: 41,525, Visits: 34,442
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 2008, MVP
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

Post #1032930
Posted Friday, December 10, 2010 4:12 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 19, 2013 7:44 AM
Points: 38, Visits: 162
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?
Post #1032931
Posted Friday, December 10, 2010 4:20 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 11:47 AM
Points: 41,525, Visits: 34,442
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 2008, MVP
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

Post #1032934
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse