Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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
add comment

2 Answers 2

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.

http://support.microsoft.com/kb/315512

share|improve this answer
add comment

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
add comment

Your Answer

 
discard

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