Tara Kizer Blog

Tara Kizer

File Growth Settings for SQL Server Databases

Autogrowth is enabled by default for the SQL Server database files.  The default setting of 10% for file growth on the database files can often times lead to poor performance, especially if you don't properly size your files for the near future. 

An example of when you'll see poor performance is when you have a database that is 500GB in size.  When it runs out of free space, it'll autogrow by 50GB.  Depending upon your disk performance, this could take a bit of time to allocate the space which could impact your end-users.  To minimize the impact, change the file growth setting to be based upon megabytes rather than a percentage.  What you specify for the megabytes option will be based upon your database size and how much data is changing on a regular basis.  For larger databases though, you should be somewhere between 100MB and 1024MB. 

Kimberly Tripp discusses this topic a bit as well as some other things that DBAs should be aware of.  Check this out for more information.

To change the default settings for future databases, make the change on the model database.

What file growth settings do you use when creating a database that is expected to be "large"?

Legacy Comments


Eli Leiba
2008-09-13
File Growth settings

I use filegrowth in MB (not percentage)

I usually enter a growth which is half the size of initial

for example if initial size is 1000MB then the file growth entered by me will be 500mb

Eli

ehsan khosravi
2008-09-28
re: File Growth Settings for SQL Server Databases
altert database xxx
(file=
maxsize=
filegrowth=)
you with usage alter database can change size filegrowth if you want full code plz send mail to my email

www.san_fazo@yahoo.com