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