choosing database FILEGROWTH as a % or as Fixed size

Question

Does anyone know of any issues (performance or otherwise) associated with a FILEGROWTH setting as a % rather than as a fixed size? What advantage is there to % over fixed increments?

Answer 1

Using a percentage can mean big autogrows. When you are adding 10% to a 100GB DB, you're adding 10GB. If that takes longer than 30 seconds (it easily can) and you have a caller with a 30s timeout that will cause a rollback, you'll end up with a lot of disk activity for a long time if you have a retry loop. I might know someone a variant of this scenario happened to :P

In general, make sure to be very careful with autogrows - they should be there as a last resort, not as a primary growth management strategy. For any production database of consequence you should be managing growth by ensuring there is sufficient headroom during a maintenance window.

Answer 2

I would suggest using a fixed increment. Using a % on a small growing database cause the database to grow more often. Also using a % on a large database can cause excessive growth. The best thing to do here is monitor your database closely and set a fixed growth that matches your usage.