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?
2 Answers
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.
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.
