I inherit a lot of SQL Server databases. Different teams, different eras, different ideas of what “good enough” looks like. One thing that always surprises me is how inconsistent the autogrowth settings are. Filegrowths in megabytes, percentages, tiny increments… it’s all over the place.
In my earlier post on safely shrinking SQL Server transaction logs, I touched on why autogrowth matters. Each growth of the log adds more virtual log files (VLFs), and a log that grows in many small steps ends up with a lot of them, which slows down recovery and restores.
I’m often taking over databases that are 10+ years old, with no history, no documentation, and no log monitoring to speak of. There’s nobody around to ask why things are set the way they are. So, barring any evidence to the contrary, I take the same approach every time and standardize autogrowth to 1024MB for every user database.
The Script
This is the script I use:
SELECT
db.name AS DatabaseName
,db.database_id AS DatabaseId
,mf.type AS FileType
,mf.name AS FileName
,'USE ' + db.name + '; ALTER DATABASE [' + db.name + '] MODIFY FILE (NAME = [' + mf.name + '], FILEGROWTH = 1024MB);' AS Script
FROM sys.databases db
JOIN sys.master_files mf ON db.database_id = mf.database_id
WHERE db.database_id > 4;Run it, get your ALTER DATABASE commands, and apply them. This doesn’t change current file sizes. It only changes future growth, so it happens in predictable chunks.
As written, the script covers log files as well as data files. On SQL Server 2022 and later, there’s a reason to handle logs differently, which is covered below.
Why This Might Not Work for You
There’s no one-size-fits-all in database management. This approach assumes:
- You care more about reducing VLF fragmentation than squeezing every gigabyte.
- You have the disk space to absorb larger growth steps.
- You’re okay with uniformity until you get better data.
If you’re managing a system with tight disk constraints, high-churn databases, or specific growth patterns (think OLAP vs OLTP workloads), this blanket setting might not be right. For legacy systems where nobody knows why the settings are what they are, it’s a solid default until you have evidence otherwise.
One Note on Log Files and IFI
SQL Server 2022 added instant file initialization (IFI) for log file growth. Normally, SQL Server fills new file space with zeros before using it. Data files have been able to skip that for a long time, but log files always had to be zeroed first. Microsoft’s instant file initialization page says that starting with SQL Server 2022, “transaction log autogrowth events up to 64 MB can benefit from instant file initialization,” and that growth events “larger than 64 MB can’t.” Log IFI also doesn’t need the Perform Volume Maintenance Tasks privilege that data file IFI does. The same page notes that the default autogrowth for new databases is already 64 MB.
A log set to grow by 1024MB still gets zeroed on every growth. Aaron Bertrand tested this in Log File Instant File Initialization, comparing 64MB and 1GB log autogrowth on SQL Server 2019 and 2022. On 2022, the 64MB setting took roughly 20% off his workload’s duration by getting rid of the PREEMPTIVE_OS_WRITEFILEGATHER waits spent zeroing the file.
On SQL Server 2022, a 64MB log growth also creates a single VLF, according to Microsoft’s transaction log architecture guide, so keeping log growth at 64MB doesn’t work against the VLF count the way small growths did on older versions.
This only affects log files, and only on SQL Server 2022 and later. Data files still want the bigger growth, in my opinion. To apply the script to data files only and handle logs separately, add AND mf.type = 0 to the WHERE clause.