Script to Alter File Growth

I recently inherited a large estate of SQL Servers. Over the years, before I started, it grew organically and was managed by an insufficient number of accidental DBAs. As a result many SQL server instances were built and left with their default settings.

One such setting is file growth, which, tends to be set either at a growth rate of 10% or 1MB. Given the number of databases that can be on a single server I wrote a little script to aid me in making the changes.

I realise that every server and database has different requirements. I am using this script to get my databases and servers to a basic level of "best practice" and avoid any performance issues that may arise.


The script below sets the database file growth as follows:

• Finds databases with files set to grow by either a percentage or 1MB or less
• Calculates the file growth rate as follows:
  • 100MB for files < 1GB
  • 1024MB for files > 10GB
  • File growth value set to 10% of the file's size when file is between 1GB and 10GB (rounded to the nearest 100)

Run the script then copy and paste the results into another SSMS window. It is best to review the resulting scripts to be sure they will do what you expect them to.

As with all my scripts, this comes with no guarantees. Any scripts should be tested before put into a production environment.