July 27, 2023

Autogrow All Files, Manually

SQL Server has a setting which will grow all database files in a filegroup any time that one file grows.  This setting is great for databases that are new, or otherwise can have the setting enabled.

However exclusive access is required, therefore it's not practical to enable this setting on some databases.  You could entertain using single user mode or connecting via the DAC, but the downtime might not be worth it.  Remember, resizing files manually does pause I/O, but it's less intrusive than no connections at all.

If downtime is a concern, consider enabling the Instant File Initialization setting for your SQL Service before running this.  However, if you don't want to enable that, running this more frequently could result in shorter, less noticed pauses in I/O.

This script was built to be run as a SQL Agent job.