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

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.