How to Set SQL Server Autogrowth for All Databases (and Why You Should)

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.

I should say up front that the script below sets everything to 1024MB, including log files, and on SQL Server 2022 that’s the wrong answer for logs specifically. I explain why further down. If you’re on 2022 and you only read this far, filter the script to data files with AND mf.type = 0.

In my earlier post on safely shrinking SQL Server transaction logs, I touched on why autogrowth matters: uncontrolled growth leads to excessive VLF counts, which in turn hurts performance.

Now, 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: 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 just makes future growth happen in predictable, healthy chunks.

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. But for legacy systems where entropy has taken over? It’s a solid default until proven otherwise.

One Note on Log Files and IFI

If you’re on SQL Server 2022 or newer, there’s something worth knowing here.

2022 added instant file initialization for log file growth. Data files have had IFI for a long time, but log files always had to be zeroed out first. The catch is the size. Microsoft’s instant file initialization page says “transaction log autogrowth events up to 64 MB can benefit from instant file initialization” and that events “larger than 64 MB can’t.” So setting a log to grow in 1024MB steps means every one of those growths gets zeroed out first, the same as it always did.

That’s the opposite of the advice I opened with, and it took me a while to accept it. Bigger growth increments are right for data files because they keep the file from growing constantly. For logs on 2022, 64MB growths that are instant beat 1024MB growths that block, and the VLF argument doesn’t override that because 64MB is also a reasonable VLF size.

Aaron Bertrand wrote this up in Log File Instant File Initialization, with a test comparing 64MB and 1GB log autogrowth on 2022.

So the honest version of my blanket rule is that it isn’t blanket anymore. Data files get 1024MB. Log files on 2022 and newer get 64MB, which happens to be the default for new databases anyway. Log files on 2019 and older don’t get IFI either way, so the old reasoning still holds there and I leave those larger. Add AND mf.type = 0 to the script to handle data files, and do the logs separately.