5 07 2014
Quick Post: SQL File Growth
When 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.
'ALTER DATABASE ' + d.name + ' MODIFY FILE (NAME=' + f.NAME + ',FILEGROWTH=32MB)'
FROM SYS.SYSALTFILES f
JOIN SYS.DATABASES d ON d.database_id = f.dbid
WHERE f.status & 0x100000 = 1048576
AND d.name NOT IN ( 'master' , 'model' , 'msdb' , 'tempdb' )
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!