Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm creating a database for data warehousing purposes. The database will grow about 10 000 - 100 000 rows in a week (this is a guess). I think that I'll have to go with database file autogrowth, what would be the best options for file growth? 1 megabyte is bad, maybe 100 mb or so? How do you estimate the best options for file growth?

share|improve this question
up vote 3 down vote accepted

For autogrowth set the amount to a defined size and not a percentage. You need to figure out the size of the rows and calculate how much the data will grow. For example, if your row size is the maximum of 8KB then 10,000 rows of growth will amount to 80MB. This setting can be used in production but there are many other considerations. See this article for more details.

share|improve this answer

Find how much space your tables have by issuing: sp_spaceused 'dbo.TABLE_NAME'

If the cumulated row number is 200,000 rows and 5 MB of space then set up the auto-growth for a month: meaning 100,000 rows * 30 days = 2,5 MB * 30 days = 75 MB. A value rounded to 100 MB up to 300 - 400 is not a huge activity for SQL to manage when allocating the disk space. Check out also if you have instant file initialization. Log file auto-growth needs also to be set to a fixed value because DW processes are Log space consuming even if the Database is in Simple recovery model. 50 - 100 MB would be a reasonable size.

In any case don't leave the default options for both data and log file. This is not very good.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.