SQL Server 2005 auto growth by size

Question

I have been looking at the new database server we are setting up for a client and note the database files are set to grow by 1 meg everytime the file is full and the initial size is 100 MB.

I have been considering this breifly and it doesn't sound right. I've checked a few sites on DB considerations and they didn't properly explain these values.

I would probably only want the database files to extend once a month shall we say?

So if i was to calculate the amount of data I expect to be inserted per day in megabytes and just multiply by 30 I should find a suitable figure?

i.e. I do know approximately the size of 1 row and approx how many rows will be inserted in an average week per table. I know these are estimates from the ground up so you think once a month is a suitable approximation for the file to extend or is it preferable to extend every hour>? or never?

We are using full back so we can recover to a point in time and transaction log backups are occuring and the recovery procedure seems to be 100% effective. Are these types of changes going to impact the backup and recovery in any way at all?

Thanks.

Answer 1

What you've suggested is pretty much spot on. You want the autogrowth to be based on what you expect to see.

A database that has autogrowth of 1Mb everytime it is full will experience huge performance issues, as every time the database is full, whatever transaction is in progress will have to pause until it has grown.

I'm trying to find an article I read on the subject earlier, so when I find it, I'll add a link...

EDIT: http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1330922,00.html The article is about shrinking your database, but it details what happens when a database auto-grows, and shows the performance impact it can have.

You definitely don't want your database to be growing as often as it would at 1Mb a pop!

Answer 2

You're generally correct about trying to minimize the number of times when your database has to grow.

I could not give you any exact values but it's always better to have the database file size so big you would only need to grow it occasionally and then it has to grow considerably so you would not have frequent file size changes.

Answer 3

Every time that the data file has to grow, it takes up some amount of resources as it grabs the extra disk space and extends the data file. So, ideally you want to limit the number of growths.

Personally, I try to make sure that my databases don't have to auto grow at all. I try to proactively grow them during off hours. This also lets me monitor disk space better, since that can't easily "auto-grow" ;)

If they auto grow once per month it should be fine. Every minute and you will likely see a performance impact.

Other than the backup potentially taking up more space and filling up your backup location I can't think of any reason that it would cause an issue with your backup process. I'm more of a SQL developer than a DBA, so I can't swear to that though.

Answer 4

Apart from some redundant allocated space there is little or no downside to a large size increment. Setting the increment to a size that will cause it to grow every month or few months is about right.

In any event, you should have a regular job that monitors free space on both the disk volumes and within the files and produces a report so you can see imminent disk shortages.

Answer 5

I just checked in SQL Server Management Studio 2008 and the default growth when you create a new database is by 1MB...that's probably where your 1MB setting came from (I bet it was the same in 2005).

I remember reading a long time ago that one could consider setting their database file to double everytime it needed to grow. Now, if you had a 5TB database you probably wouldn't want it to just double one day, but for a database 1GB in size it's probably cool if it doubles when it needs to (you would have a decaying rate of growth events if you have a constant rate of data input).

Just wanted to present what I think could be a workable strategy, depending on your circumstances, of course.

Answer 6

It is suggestible to set the file growth in percentages (5% to 10% in optimal) when we are not sure about the actual size of data that might be added in a day or week. In this case the file growth will be in proportion to the size of the database.

Answer 7

In my opinion, I would NOT set a database to grow in percentages, rather let the database grow for a week at 100 MB, and then change the growth setting to one week worth of growth, let's say 5 GB. We have systems that we have done this for.

Otherwise, you can get really technical and see how many records are being added to the system every week, take into account records that are archived or deleted, then calculate the amount of space required for each record, and set your autogrowth based on that times the number of records.

The reason I would steer a person away from percentage growth is that when the system is 1000 MB, it will grow by 100 MB. Then, the next time, the system is 1100 MB and will grow my 110 MB. The size will then be 1210 and the database will grow at 121 MB. Then the size will be 1331, and growth will be 133 MB. With this uneven growth, it will make it very difficult to calculate how much disk space you have remaining and when you need to re-size your maximum setting.

Just my 2 cents away.

Also, the default growth mentioned above is set within the MODEL database. What ever settings your MODEL database has when you create a new database is what is defaulted in your new database.