I'm looking for a way to manually perform the same functionality that
the "auto grow" provides. I would like to be able to run a nightly
script that can determine the unallocated space in a file (eg.
sp_spaceused) and if it falls below a certain percent, say 15% then
have it grow the file by say 25GB. I want to prevent the files from
growing in the middle of the day because of performance and
fragmentation.
Any help would be great.
-Will
Will
ALTER DATABASE dbname
MODIFY FILE
(NAME = logical file name,
SIZE = 25GB)
GO
Note , it is going to take pretty long time, what is your SQL Server
version?
"Will" <WillCWirtz@.yahoo.com> wrote in message
news:8422a1b9-6c8a-4a8e-8c55-c163dba38b71@.i12g2000prf.googlegroups.com...
> I'm looking for a way to manually perform the same functionality that
> the "auto grow" provides. I would like to be able to run a nightly
> script that can determine the unallocated space in a file (eg.
> sp_spaceused) and if it falls below a certain percent, say 15% then
> have it grow the file by say 25GB. I want to prevent the files from
> growing in the middle of the day because of performance and
> fragmentation.
> Any help would be great.
> -Will
|||Uri, thanks for your help. We're using SQL 2005.
I was hoping to avoid the ALTER DATABASE command if possible. It just
seems a little risky, but I don't know why.
It looks like it doesn't exist, but I was hoping that SQL may provide
a API for doing that in a more controlled way like DBCC(mydb.mdf,
20GB) or something.
You mention that it will be slow. Slower than an Auto Grow of the
same size? If so, Why? This would back up my desire to call the same
code that runs when the "auto grow" is initiated.
Will
|||Will
It is considered a good practice to allocate ( get on target) the size for
db and manually gwoing it.
Please read up this article
http://www.sqlskills.com/blogs/kimberly/2007/03/04/InstantInitializationWhatWhyAndHow.aspx
"Will" <WillCWirtz@.yahoo.com> wrote in message
news:9277d876-a967-4907-bc4a-c424a4a1fcae@.s19g2000prg.googlegroups.com...
> Uri, thanks for your help. We're using SQL 2005.
> I was hoping to avoid the ALTER DATABASE command if possible. It just
> seems a little risky, but I don't know why.
> It looks like it doesn't exist, but I was hoping that SQL may provide
> a API for doing that in a more controlled way like DBCC(mydb.mdf,
> 20GB) or something.
> You mention that it will be slow. Slower than an Auto Grow of the
> same size? If so, Why? This would back up my desire to call the same
> code that runs when the "auto grow" is initiated.
> Will
Showing posts with label grow. Show all posts
Showing posts with label grow. Show all posts
Monday, March 19, 2012
Manually Grow database files
I'm looking for a way to manually perform the same functionality that
the "auto grow" provides. I would like to be able to run a nightly
script that can determine the unallocated space in a file (eg.
sp_spaceused) and if it falls below a certain percent, say 15% then
have it grow the file by say 25GB. I want to prevent the files from
growing in the middle of the day because of performance and
fragmentation.
Any help would be great.
-WillWill
ALTER DATABASE dbname
MODIFY FILE
(NAME = logical file name,
SIZE = 25GB)
GO
Note , it is going to take pretty long time, what is your SQL Server
version?
"Will" <WillCWirtz@.yahoo.com> wrote in message
news:8422a1b9-6c8a-4a8e-8c55-c163dba38b71@.i12g2000prf.googlegroups.com...
> I'm looking for a way to manually perform the same functionality that
> the "auto grow" provides. I would like to be able to run a nightly
> script that can determine the unallocated space in a file (eg.
> sp_spaceused) and if it falls below a certain percent, say 15% then
> have it grow the file by say 25GB. I want to prevent the files from
> growing in the middle of the day because of performance and
> fragmentation.
> Any help would be great.
> -Will|||Uri, thanks for your help. We're using SQL 2005.
I was hoping to avoid the ALTER DATABASE command if possible. It just
seems a little risky, but I don't know why.
It looks like it doesn't exist, but I was hoping that SQL may provide
a API for doing that in a more controlled way like DBCC(mydb.mdf,
20GB) or something.
You mention that it will be slow. Slower than an Auto Grow of the
same size? If so, Why? This would back up my desire to call the same
code that runs when the "auto grow" is initiated.
Will|||Will
It is considered a good practice to allocate ( get on target) the size for
db and manually gwoing it.
Please read up this article
http://www.sqlskills.com/blogs/kimberly/2007/03/04/InstantInitializationWhatWhyAndHow.aspx
"Will" <WillCWirtz@.yahoo.com> wrote in message
news:9277d876-a967-4907-bc4a-c424a4a1fcae@.s19g2000prg.googlegroups.com...
> Uri, thanks for your help. We're using SQL 2005.
> I was hoping to avoid the ALTER DATABASE command if possible. It just
> seems a little risky, but I don't know why.
> It looks like it doesn't exist, but I was hoping that SQL may provide
> a API for doing that in a more controlled way like DBCC(mydb.mdf,
> 20GB) or something.
> You mention that it will be slow. Slower than an Auto Grow of the
> same size? If so, Why? This would back up my desire to call the same
> code that runs when the "auto grow" is initiated.
> Will|||> I was hoping to avoid the ALTER DATABASE command if possible. It just
> seems a little risky, but I don't know why.
There's noting inherently "risky" with ALTER DATABASE.
> It looks like it doesn't exist, but I was hoping that SQL may provide
> a API for doing that in a more controlled way like DBCC(mydb.mdf,
> 20GB) or something.
Imagine you are working in the SQL Server dev team for MS. You have implemented code that expend the
size of a database file. You now have to determine the TSQL command which will invoke your command.
Should it be some DBCC command? Or some ALTER DATABASE? What I'm trying to say is that the command
is just an interface to the functionality within SQL Server. MS are in fact moving away from DBCC
and system stored procedures in favor of DDL.
> You mention that it will be slow. Slower than an Auto Grow of the
> same size?
No, it is the same functionality in the engine in the end. It will actually be perceieved quicker
because you don't have one or several persons waiting for the grow (because you grow before it is
full).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Will" <WillCWirtz@.yahoo.com> wrote in message
news:9277d876-a967-4907-bc4a-c424a4a1fcae@.s19g2000prg.googlegroups.com...
> Uri, thanks for your help. We're using SQL 2005.
> I was hoping to avoid the ALTER DATABASE command if possible. It just
> seems a little risky, but I don't know why.
> It looks like it doesn't exist, but I was hoping that SQL may provide
> a API for doing that in a more controlled way like DBCC(mydb.mdf,
> 20GB) or something.
> You mention that it will be slow. Slower than an Auto Grow of the
> same size? If so, Why? This would back up my desire to call the same
> code that runs when the "auto grow" is initiated.
> Will|||> have it grow the file by say 25GB. I want to prevent the files from
> growing in the middle of the day because of performance and
> fragmentation.
If you are using sql 2005 on a windows server 2003, then you should read up
on Windows Instant File Initialization. Basically it means, that sql server
data files can be created instantly, without zeroing out all bytes in the
file. The account under which the sql server service runs, needs to be added
to the "Perform Volume Maintenance Tasks" security policy in windows. It
should be easy to find blog postings about this subject.
If you ARE running sql 2005 on a windows 2k3 server, and if you have set up
the user account for the "perform volume maintenance tasks", then you should
not worry about the performance hit when creating new data files og growing
them, since the growth happens instantly.
BUT! This only applies to data files. The log files will still need to zero
out all bytes on creation or growth, and that will have some performance
impact.
/Sjang|||> have it grow the file by say 25GB. I want to prevent the files from
> growing in the middle of the day because of performance and
> fragmentation.
If you are using sql 2005 on a windows server 2003, then you should read up
on Windows Instant File Initialization. Basically it means, that sql server
data files can be created instantly, without zeroing out all bytes in the
file. The account under which the sql server service runs, needs to be added
to the "Perform Volume Maintenance Tasks" security policy in windows. It
should be easy to find blog postings about this subject.
If you ARE running sql 2005 on a windows 2k3 server, and if you have set up
the user account for the "perform volume maintenance tasks", then you should
not worry about the performance hit when creating new data files og growing
them, since the growth happens instantly.
BUT! This only applies to data files. The log files will still need to zero
out all bytes on creation or growth, and that will have some performance
impact.
/Sjang
the "auto grow" provides. I would like to be able to run a nightly
script that can determine the unallocated space in a file (eg.
sp_spaceused) and if it falls below a certain percent, say 15% then
have it grow the file by say 25GB. I want to prevent the files from
growing in the middle of the day because of performance and
fragmentation.
Any help would be great.
-WillWill
ALTER DATABASE dbname
MODIFY FILE
(NAME = logical file name,
SIZE = 25GB)
GO
Note , it is going to take pretty long time, what is your SQL Server
version?
"Will" <WillCWirtz@.yahoo.com> wrote in message
news:8422a1b9-6c8a-4a8e-8c55-c163dba38b71@.i12g2000prf.googlegroups.com...
> I'm looking for a way to manually perform the same functionality that
> the "auto grow" provides. I would like to be able to run a nightly
> script that can determine the unallocated space in a file (eg.
> sp_spaceused) and if it falls below a certain percent, say 15% then
> have it grow the file by say 25GB. I want to prevent the files from
> growing in the middle of the day because of performance and
> fragmentation.
> Any help would be great.
> -Will|||Uri, thanks for your help. We're using SQL 2005.
I was hoping to avoid the ALTER DATABASE command if possible. It just
seems a little risky, but I don't know why.
It looks like it doesn't exist, but I was hoping that SQL may provide
a API for doing that in a more controlled way like DBCC(mydb.mdf,
20GB) or something.
You mention that it will be slow. Slower than an Auto Grow of the
same size? If so, Why? This would back up my desire to call the same
code that runs when the "auto grow" is initiated.
Will|||Will
It is considered a good practice to allocate ( get on target) the size for
db and manually gwoing it.
Please read up this article
http://www.sqlskills.com/blogs/kimberly/2007/03/04/InstantInitializationWhatWhyAndHow.aspx
"Will" <WillCWirtz@.yahoo.com> wrote in message
news:9277d876-a967-4907-bc4a-c424a4a1fcae@.s19g2000prg.googlegroups.com...
> Uri, thanks for your help. We're using SQL 2005.
> I was hoping to avoid the ALTER DATABASE command if possible. It just
> seems a little risky, but I don't know why.
> It looks like it doesn't exist, but I was hoping that SQL may provide
> a API for doing that in a more controlled way like DBCC(mydb.mdf,
> 20GB) or something.
> You mention that it will be slow. Slower than an Auto Grow of the
> same size? If so, Why? This would back up my desire to call the same
> code that runs when the "auto grow" is initiated.
> Will|||> I was hoping to avoid the ALTER DATABASE command if possible. It just
> seems a little risky, but I don't know why.
There's noting inherently "risky" with ALTER DATABASE.
> It looks like it doesn't exist, but I was hoping that SQL may provide
> a API for doing that in a more controlled way like DBCC(mydb.mdf,
> 20GB) or something.
Imagine you are working in the SQL Server dev team for MS. You have implemented code that expend the
size of a database file. You now have to determine the TSQL command which will invoke your command.
Should it be some DBCC command? Or some ALTER DATABASE? What I'm trying to say is that the command
is just an interface to the functionality within SQL Server. MS are in fact moving away from DBCC
and system stored procedures in favor of DDL.
> You mention that it will be slow. Slower than an Auto Grow of the
> same size?
No, it is the same functionality in the engine in the end. It will actually be perceieved quicker
because you don't have one or several persons waiting for the grow (because you grow before it is
full).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Will" <WillCWirtz@.yahoo.com> wrote in message
news:9277d876-a967-4907-bc4a-c424a4a1fcae@.s19g2000prg.googlegroups.com...
> Uri, thanks for your help. We're using SQL 2005.
> I was hoping to avoid the ALTER DATABASE command if possible. It just
> seems a little risky, but I don't know why.
> It looks like it doesn't exist, but I was hoping that SQL may provide
> a API for doing that in a more controlled way like DBCC(mydb.mdf,
> 20GB) or something.
> You mention that it will be slow. Slower than an Auto Grow of the
> same size? If so, Why? This would back up my desire to call the same
> code that runs when the "auto grow" is initiated.
> Will|||> have it grow the file by say 25GB. I want to prevent the files from
> growing in the middle of the day because of performance and
> fragmentation.
If you are using sql 2005 on a windows server 2003, then you should read up
on Windows Instant File Initialization. Basically it means, that sql server
data files can be created instantly, without zeroing out all bytes in the
file. The account under which the sql server service runs, needs to be added
to the "Perform Volume Maintenance Tasks" security policy in windows. It
should be easy to find blog postings about this subject.
If you ARE running sql 2005 on a windows 2k3 server, and if you have set up
the user account for the "perform volume maintenance tasks", then you should
not worry about the performance hit when creating new data files og growing
them, since the growth happens instantly.
BUT! This only applies to data files. The log files will still need to zero
out all bytes on creation or growth, and that will have some performance
impact.
/Sjang|||> have it grow the file by say 25GB. I want to prevent the files from
> growing in the middle of the day because of performance and
> fragmentation.
If you are using sql 2005 on a windows server 2003, then you should read up
on Windows Instant File Initialization. Basically it means, that sql server
data files can be created instantly, without zeroing out all bytes in the
file. The account under which the sql server service runs, needs to be added
to the "Perform Volume Maintenance Tasks" security policy in windows. It
should be easy to find blog postings about this subject.
If you ARE running sql 2005 on a windows 2k3 server, and if you have set up
the user account for the "perform volume maintenance tasks", then you should
not worry about the performance hit when creating new data files og growing
them, since the growth happens instantly.
BUT! This only applies to data files. The log files will still need to zero
out all bytes on creation or growth, and that will have some performance
impact.
/Sjang
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)
Yes
No
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...
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...
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:|||RM,
[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]
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/
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)
> does it calculate the DB size and allocate the
> accurrate size of space by defined percentage?
Yes
> does it take long time and has overhead?
No
> 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 Server need space for an
insert, update etc and the file is full. The user is blocked by the grow operation during the grow.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"RM" <m_r1824@.yahoo.co.uk> wrote in message news:opr2g3ovl8hqligo@.msnews.microsoft.com...
> 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/|||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...
> 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/
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)
> does it calculate the DB size and allocate the
> accurrate size of space by defined percentage?
Yes
> does it take long time and has overhead?
No
> 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 Server need space for an
insert, update etc and the file is full. The user is blocked by the grow operation during the grow.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"RM" <m_r1824@.yahoo.co.uk> wrote in message news:opr2g3ovl8hqligo@.msnews.microsoft.com...
> 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/|||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...
> 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/
Subscribe to:
Posts (Atom)