Saturday, February 25, 2012

Managing database growth

Hi
I've a DB in SQL Server 2000 which allows the
database to grow by a percentage (defualt=10%).
Now,anfter any action(for example insert ing a row)
does it calculate the DB size and allocate the
accurrate size of space by defined percentage?
does it take long time and has overhead?
is it a good way to define database grow by
percentage?
Thanks for any help.
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/> Now,anfter any action(for example insert ing a row)
quote:

> does it calculate the DB size and allocate the
> accurrate size of space by defined percentage?

Yes
quote:

> does it take long time and has overhead?

No
quote:

> is it a good way to define database grow by
> percentage?

Yes
Otherwise, when the database grow and somebody forget to give more space, it
will happen some ugly error.
I think if the database it some, you should let the database grow by fix
size, otherwise grow by percentage|||SQL Server doesn't do the grow after an insert. It happens whenever SQL Serv
er need space for an
insert, update etc and the file is full. The user is blocked by the grow ope
ration during the grow.
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"RM" <m_r1824@.yahoo.co.uk> wrote in message news:opr2g3ovl8hqligo@.msnews.microsoft.com...
quote:
[c
olor=darkred]
> Hi
> I've a DB in SQL Server 2000 which allows the
> database to grow by a percentage (defualt=10%).
> Now,anfter any action(for example insert ing a row)
> does it calculate the DB size and allocate the
> accurrate size of space by defined percentage?
> does it take long time and has overhead?
> is it a good way to define database grow by
> percentage?
> Thanks for any help.
> --
> Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/[/color]
|||RM,
My personal preference is for a fixed growth unit.
Say, blocks of 1024MB for data and 512MB for log.
For some databases, excessive numbers of small growth will give you
fragmentation on the physical files.
I have seen some databases with the primary file in 300+ fragments. This
cannot be good.
Much better is a scheme something like this:
Initial size: 30GB Data / 3GB Log
Data growth: 3072MB
Log growth: 1024MB
This will minimize the number of 'expands' that need to run.
Planning is key here.
What you want to avoid is the 'ever-larger' growth syndrome.
I.e. Initial Size 30GB Data / 3GB Log.
Data growth: 10%.
Log growth 10%.
This percentage grows ever larger each time the DB expands.
Again, this is just personal preference.
James Hokes
"RM" <m_r1824@.yahoo.co.uk> wrote in message
news:opr2g3ovl8hqligo@.msnews.microsoft.com...
quote:

> Hi
> I've a DB in SQL Server 2000 which allows the
> database to grow by a percentage (defualt=10%).
> Now,anfter any action(for example insert ing a row)
> does it calculate the DB size and allocate the
> accurrate size of space by defined percentage?
> does it take long time and has overhead?
> is it a good way to define database grow by
> percentage?
> Thanks for any help.
> --
> Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/

No comments:

Post a Comment