Sunday, March 11, 2012

Best practice for shrinking Databases

Is there a "best practice" for shrinking databases?
Daily, Weekly, Monthly? Daily do a normal 25% free and weekly Move pages to
the beginning before shrinking?
I already have Maintenance Plans to backup logs and the database etc. but
wondered about automating the shrink process as well. There seems to be
alot of free space 67%+ in the database and logfile.
Dan
In general you want to avoide shrinking databases on a regular basis.
If the database grows back every week, or month, then it obviously
needs the space, so leave the space there! It is a terrible
performance hit to wait while SQL Server allocates more space, you
really don't want it happening more than it must.
So shrinking should be on an as-needed basis.
As for how much elbow room to leave, one rule of thumb I have used is
at least 25% more free space than the largest table. That allows room
for rebuilding a clustered index.
Another rule of thumb is that DBA time costs a lot more than disk
space; managing a database server without plenty of extra disk space
will cost far more dollars in administration than it saves on
hardware.
Roy
On Wed, 22 Feb 2006 17:25:11 -0500, "Dan" <someone@.yahoo.com> wrote:

>Is there a "best practice" for shrinking databases?
>Daily, Weekly, Monthly? Daily do a normal 25% free and weekly Move pages to
>the beginning before shrinking?
>I already have Maintenance Plans to backup logs and the database etc. but
>wondered about automating the shrink process as well. There seems to be
>alot of free space 67%+ in the database and logfile.
>Dan
|||That's good advice Roy.
When I do shrink it, should I use the option to move the pages to the
beginning before shrinking?
Dan
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:234qv1h601176jknofkr8furr4lr7gc1o1@.4ax.com... [vbcol=seagreen]
> In general you want to avoide shrinking databases on a regular basis.
> If the database grows back every week, or month, then it obviously
> needs the space, so leave the space there! It is a terrible
> performance hit to wait while SQL Server allocates more space, you
> really don't want it happening more than it must.
> So shrinking should be on an as-needed basis.
> As for how much elbow room to leave, one rule of thumb I have used is
> at least 25% more free space than the largest table. That allows room
> for rebuilding a clustered index.
> Another rule of thumb is that DBA time costs a lot more than disk
> space; managing a database server without plenty of extra disk space
> will cost far more dollars in administration than it saves on
> hardware.
> Roy
>
> On Wed, 22 Feb 2006 17:25:11 -0500, "Dan" <someone@.yahoo.com> wrote:
|||Moving the pages is the only way you will be able to shrink to the
leve you want.
Roy
On Wed, 22 Feb 2006 20:48:23 -0500, "Dan" <someone@.yahoo.com> wrote:

>That's good advice Roy.
>When I do shrink it, should I use the option to move the pages to the
>beginning before shrinking?
>Dan

No comments:

Post a Comment