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: 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.