|
Home > Archive > microsoft.public.sqlserver.server > November 2002 > What causes DB to grow so rapidly
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
What causes DB to grow so rapidly
|
|
| Danny Presse 2002-11-20, 3:23 pm |
| HI,
I just realize that one of our DB has grown form 4 GIG to 6 GIG since 3
weeks.
I have no explanation except this:
Since 3 weeks, I experimented differents FILLFACTOR (50 %, 10% , 80%)
Do you think that changing the FILLFACTOR can grow the DB like that ?
Any other idea ?
Thank you
Danny
| |
| Mike John 2002-11-20, 4:23 pm |
| Danny,
I expect your fillfactor experiments causedthe growth If you have say a
table of 100Mb and you created a clusterd index and specified 10% ff then it
will take arounf 1Gb for the table. Thus the dnb files will (if configured
to grow) have expanded to allow the table to grow. By default (on standard
and ent editions) autoshrink is NOT configured on so once grown the file
stays grown. if you need to shrink it again have a look at DBCC
SHRINKDATABASE or DBCC SHRINKFILE
Mike John
"Danny Presse" <dpresse@congresmtl.com> wrote in message
news:ewndqqNkCHA.1368@tkmsftngp09...
> HI,
>
> I just realize that one of our DB has grown form 4 GIG to 6 GIG since 3
> weeks.
>
> I have no explanation except this:
> Since 3 weeks, I experimented differents FILLFACTOR (50 %, 10% , 80%)
>
> Do you think that changing the FILLFACTOR can grow the DB like that ?
>
> Any other idea ?
>
> Thank you
>
> Danny
>
>
>
>
| |
| Russell Fields 2002-11-20, 4:23 pm |
| Danny,
It depends on what you did while the FILLFACTOR was set. Did you drop the
tables or indexes and recreate them? If you did, then a FILLFACTOR of 10%
would have caused the database to expand when you loaded data into a table
that was originally loaded at 50%.
You might run DBCC SHOWCONTIG to examine how dense your data is now. If the
data is packed as densely as you wish, then you may want to run a DBCC
SHRINKDATABASE to reclaim some space.
Another hint: If you have large tables that have no clustered index, you
may be getting some unnecessary fragmentation in those tables. Especially
if they are much inserted and updated.
Russell Fields
"Danny Presse" <dpresse@congresmtl.com> wrote in message
news:ewndqqNkCHA.1368@tkmsftngp09...
> HI,
>
> I just realize that one of our DB has grown form 4 GIG to 6 GIG since 3
> weeks.
>
> I have no explanation except this:
> Since 3 weeks, I experimented differents FILLFACTOR (50 %, 10% , 80%)
>
> Do you think that changing the FILLFACTOR can grow the DB like that ?
>
> Any other idea ?
>
> Thank you
>
> Danny
>
>
>
>
| |
| Danny Presse 2002-11-21, 11:24 am |
| Thank you Mike and Russel
I know my mistake now:
Database Maintenance Plan / Optimisation / Change Free space per page
percent to:
Last week I changed this value from 50 % to 80%.
I realize now that this is not the FILLFACTOR. This is the "EMPTYFACTOR"
My DB grew from 4 GIG to 6 GIG.
I would like to go back to 4 GIG.
First if I execute DBCC SHRINKDATABASE (MyDB,50)
Second if I execute DBCC SHRINKDATABASE (MyDB,TRUNCATEONLY)
Is this the best way to go back to 4 GIG ?
Thank you
Danny
"Mike John" <Mike.John@knowledgepool.com> a écrit dans le message de news:
#jfJK5NkCHA.2772@tkmsftngp10...
> Danny,
>
> I expect your fillfactor experiments causedthe growth If you have say a
> table of 100Mb and you created a clusterd index and specified 10% ff then
it
> will take arounf 1Gb for the table. Thus the dnb files will (if configured
> to grow) have expanded to allow the table to grow. By default (on standard
> and ent editions) autoshrink is NOT configured on so once grown the file
> stays grown. if you need to shrink it again have a look at DBCC
> SHRINKDATABASE or DBCC SHRINKFILE
>
> Mike John
>
> "Danny Presse" <dpresse@congresmtl.com> wrote in message
> news:ewndqqNkCHA.1368@tkmsftngp09...
> > HI,
> >
> > I just realize that one of our DB has grown form 4 GIG to 6 GIG since 3
> > weeks.
> >
> > I have no explanation except this:
> > Since 3 weeks, I experimented differents FILLFACTOR (50 %, 10% , 80%)
> >
> > Do you think that changing the FILLFACTOR can grow the DB like that ?
> >
> > Any other idea ?
> >
> > Thank you
> >
> > Danny
> >
> >
> >
> >
>
>
|
|
|
|
|