August 8, 2007 by pinaldave
We can use SSMS to Enable Autogrowth property of the Database.
Right-click on Database click on Properties and click on Files. There will be column of Autogrowth, click on small box with three (…) dots.
Reference : Pinal Dave (http://blog.SQLAuthority.com)
Pinalkumar Dave is a Microsoft SQL Server MVP and a Mentor for Solid Quality India. He has written over 1100 articles on the subject on his blog at http://blog.sqlauthority.com. He is a dynamic and proficient Principal Database Architect, Corporate Trainer and Project Manager, who specializes in SQL Server Programming and has 7 years of hands-on experience. He holds a Masters of Science degree and a number of certifications, including MCDBA and MCAD (.NET). He was awarded Regional Mentor for PASS Asia.
pinaldave
That’s a usefull info, thanks Dave.
Hi Dave,
Good article. On one of my DB instances, SQL Server always reset this to “Restricted File Growth”. I tried few times to define Unrestricted File Growth, and server always reverts it bac to restricted. Any idea what could be wrong?
Even though I change the autogrowth with method above, its still 1280% in sysfiles like when I do:
select * from sysfiles
I have a problem with the auto growth, for some reason sql 2005 changes the auto growth to grow by either 6400 or 1440 %. i cant disable it because there is more inserting then selecting.
if anyway could could give me a solution it will really be appreciated.
regarads gavin
please mail it to me.
hi dave
how can i set the growth file size ……..?
is it will create any problem during DB transaction??
I am trying to figure out code to run every time a database is created, so this would all be part of a trigger on create of any database. My idea is to change the autogrowth for that database if it meets certain requirements. Right now when i execute the code it executes fine, but will not actually change the autogrowth for the database :(.
DELCARE @dbname nvarchar(100), @File1Growth nvarchar(10), @File2Growth nvarchar(10)
SET @bdname = ‘TempDatabase’
SET @SQLSTRING =
(
‘ USE ‘ + @dbname +
‘ SET ‘ + @File1Growth + ‘ = ‘ +
‘ ( ‘ +
‘ SELECT sysfiles.growth ‘ +
‘ FROM sysfiles ‘ +
‘ WHERE sysfiles.FileId = 1 ‘ +
‘ ); ‘ +
‘ SET ‘ + @File2Growth + ‘ = ‘ +
‘ ( ‘ +
‘ SELECT sysfiles.growth ‘ +
‘ FROM sysfiles ‘ +
‘ WHERE sysfiles.FileId = 2 ‘ +
‘ ); ‘ +
‘ IF ‘ + @File1Growth + ‘ <= 1 ' +
' ALTER DATABASE ' + @dbname +
' MODIFY FILE (NAME = ' + @dbname + ',FILEGROWTH = 4MB);' +
' IF ' + @File2Growth + ' <= 10 ' +
' ALTER DATABASE ' + @dbname +
' MODIFY FILE (NAME = ' + @dbname + '_Log,FILEGROWTH = 4MB);'
);
EXEC SP_EXECUTESQL @SQLSTRING
I am wondering if anyone has any ideas that may work?