SQL Server 2005 file sizes and filegrowth

SQL Server’s physical files are set, by default, to auto grow at 1 Mb for data files and 10% for log files. I find 1 Mb number quite low for a normal data file: so I set it to some value between 10 and 100 Mb instead. This is done either in when creating the database (as I’ve shown in a previous entry) or by altering an existing database:

alter database turdus
modify file
(name = ‘AdventureWorks_Data’,
filegrowth = 100mb);
alter database turdus
modify file
(name = ‘AdventureWorks_Log’,
filegrowth = 100mb);

But how do you find out where an existing database stands? Run this script while using AdventureWorks as an example:

select file_id, name, physical_name, size*1.0/128 as [size in mbs], max_size, max_size*1.0/128 as [max_size in mbs],
growth, growth*1.0/128 as [growth in mbs], is_percent_growth
from sys.database_files

Notes:

  • you can set the database to grow until the disk is full, for this max_size is set to -1.
  • you can also set growth to a percentage, as indicated in the log file default above, the field ‘is_percent_growth’ indicates whether this is the case with your file

Setting your databases to grow automatically can have a performance hit. It usually doesn’t take very long for the autogrowth event to take place but if a user is in the database (why do they insist on changing things?) there could be a error. You can, of course, do the update manually on off hours and use the scheduling system to warn you when this is needed but this isn’t as easy to do on SQL Server Express. I prefer to take the performance hit and use the automatic upgrade but if your server holds sensitive transactions you’ll want to do it differently.
In any case it is useful to keep track of the db growth events. Tim Chapman uses a table to capture this information for logging (as SQL Server doesn’t hold on to the historical data for long). See his article here.

About these ads

2 thoughts on “SQL Server 2005 file sizes and filegrowth

  1. Pingback: 2010 in review « Golbing

  2. Pingback: SQL Server database file size « Golbing

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s