A Bristol Geek

No trees were harmed in the creation of this blog; however, a significant number of electrons were slightly inconvenienced.

Quick Post: SQL File Growth

database spindleWhen you first take over a database system you often find that the little (but important) things have been forgotten about. There are 101 reasons why something like auto file growth can be a bad thing; the default is 10% so if you have a 10GB database you could end up seeing a 1GB autogrowth event. An event of that size is very expensive both in I/O and WAIT events.

You could run through each database on your inherited server and set the autogrowth sizes by hand. But sometimes there just isn’t the time. A quick script to alter the setting for each database can be very helpful.

This script picks up every database that has not already had it’s growth set to a fixed number; and returns a record set (one database per line) that you an copy and execute to change the database settings. Just remember that at a later date you should review your database sizes and settings to ensure they are correct for each databases use scenario.

And as always please be sure you know what a script is doing before you run it on your database; test on a non-production system first!

Thank you to [email protected] for releasing the image used in this post under the Creative Commons licence.

, ,

Leave a Reply