Check database autogrowth settings - AWS Prescriptive Guidance

Check database autogrowth settings

Any transaction that needs the data or the log file to grow includes the time taken by the file growth operation. The file grows by the increment size defined by the FILEGROWTH option. You can look for file growth events in SQL Server profiler traces. If file growth takes a long time, you might see wait types like ASYNC_IO_COMPLETION, which occurs when data processing is very slow. Such wait types not only affect performance but might also result in transaction timeouts. If that transaction holds locks on resources sought by other transactions, the timeout would lead to severe server blocking issues.

For this reason, we recommend that you configure autogrowth settings very carefully. Also keep in mind that:

  • File growth is one of the costliest operations in SQL Server.

  • Frequent autogrowth in small chunks can lead to disk fragmentation.

  • Frequent autogrowth in log files results in a large number of virtual log files (VLFs) and affects performance, as discussed in the previous section.

All these reasons could lead to slow database startup and increased backup and recovery time.

Ideally, you should pre-grow files proactively, based on regular monitoring. Choose carefully between setting autogrowth as a percentage or as a static value (in MB). Typically, setting autogrowth to one eighth of the file size is a good starting point, but this might not be the right choice. (For example, this percentage would be too high if your data file is several TBs in size.)

In most cases, an autogrowth value of 1024 MB works well for data files in most large databases. For log files, 512 MB is a good starting point. For contingency measures, we strongly recommend that you set the autogrowth value, but grow the files manually for a few months based on past trends.

Note

Setting autogrowth should be a contingency measure, so you should set it after you pre-allocate storage to a file.

You can change autogrowth settings by using SQL Server Management Studio (SSMS) or Transact-SQL. The following screen illustration shows autogrowth settings in SSMS.

Changing autogrowth settings

When you use the FILEGROWTH option for data and log files, choose carefully between setting it as a percentage or as a static value (in MB). Setting a percentage results in ever-increasing file growth, so you might prefer to use a static size for better control over the growth ratio.

  • In versions before SQL Server 2022 (16.x), transaction logs cannot use instant file initialization, so extended log growth times are especially critical.

  • Starting with SQL Server 2022 (16.x, all editions), instant file initialization can benefit transaction log growth events up to 64 MB. The default autogrowth size increment for new databases is 64 MB. Transaction log file autogrowth events that are larger than 64 MB cannot benefit from instant file initialization.