Today, I want to discuss a topic that has been a bit of a headache for database administrators and consultants: the changes to SQL Server's autogrowth settings since 2016. If you've been managing databases for a while, you might remember how things used to be and how they've evolved. Let's break it down.
The Pre-2016 Era: Simplicity with Trace Flag T1117
Before SQL Server 2016, managing autogrowth for all files in a SQL Server database was pretty simple. You could just turn on the trace flag T1117 globally in SQL Server and add it to the startup parameters so that the next time SQL Server restarted, the setting would stick, and all files in a filegroup would grow together.
DBCC TRACEON (1117, -1);
Post-2016: A Shift in Approach
Fast-forward to 2016, and Microsoft decided to change the process. They deprecated trace flag T1117, and now, you have to configure autogrowth settings individually for each filegroup within each database. The idea was to give us more granular control, but it came with challenges.
The biggest issue is that you need to kick everyone out of the database to apply these settings. This can be a real pain, especially in existing environments that have performance issues.
The Regression
To illustrate the impact, let's revisit our example in a post-2016 context. In 2014, you could just enable the trace flag T1117 globally and add the startup parameter for future restarts.
Now, after 2016, you have to set the autogrowth settings individually for each filegroup and database on the server, and to make this change, you have to bring the app down and disconnect all users.
This was a one-minute fix. Now, it's turned into a scheduled maintenance window with server and application downtime!
This regression in functionality is quite frustrating, and I don't see the benefit of the change.
Conclusion
The changes to SQL Server's autogrowth settings post-2016 have introduced more granular control but at the cost of increased complexity and downtime. I would love to know the intention behind these changes and suggest that Microsoft return to the previous simple process.
Comments