Wondering how to change SQL Server database Autogrowth settings? We can help you.

As part of our Server Management Services, we assist our customers with several SQL queries.

Today, let us see how our Support Engineers change SQL server database Autogrowth settings.

SQL Server database Autogrowth

An auto-growth event is a process by which the SQL Server engine expands the size of a database file when it runs out of space. Each database file that associate with the database has an auto-growth setting.

There are three different settings we can use to identify how the database files will grow. They can grow by a specific size, a percentage of the current size, or not grow at all.

Additionally, we can set the files to unrestricted growth, which means they will keep growing. Or we can restrict the growth of a database file to grow no larger than a specified size.

If we have too many autogrow events in the database, it can degrade the overall performance of the database.

Below are some points our Support Techs consider to reduce the autogrow events in a database to improve performance.

• Set Optimum Database Size

The database should have enough size. If it grows higher make sure to enable Instant file initialization on that SQL Server Instance.

Autogrowth is an option that should be used to avoid unexpected scenarios of space issues in database files. We should figure out how big the database will be over time and set the database size to that value.

Analyze database size every few months to adjust the size if it requires to increase. This way we can minimize autogrow events.

• Resize Autogrowth Settings

Autogrowth events slow down the performance of the database. Because, whenever an autogrowth event is performed, SQL Server holds up database processing. This equates to slower response time for those SQL commands processing against the database that is growing.

If the database is performing frequent autogrow events, then we have a good scope of performance improvement by properly resizing the database autogrowth settings that will reduce the number of autogrow events.

• Change Default Autogrowth Settings

The autogrowth default value for data files is 1MB and log files are 10% of the total size of the log file. These autogrowth values inherit from the model database.

However, we can change the autogrowth size of each database file that will apply to all databases we create on this SQL Server Instance.

For example, we can change autogrow settings from the default value that is 1MB to one-eighth of the database file size. This will reduce the total number of autogrow events in the database.

• Monitoring

If we know the database growth pattern, then set autogrowth per that growth. If we don’t have any idea about the future database growth, then we should proactively monitor database file sizes and their autogrowth events for a week or month and then set it per the growth pattern.

We should also proactively monitor database growth histories for all databases to avoid any future outages due to disk or database file full.

Best Practices for Database Autogrowth Property

Our Support Techs suggest that we can improve the database performance and manage the disk space utilization by focusing on the below points.

• Analyze the database growth pattern and set the optimum size of the database with the analyzed growth settings.
• Proactively monitor database file sizes and their autogrowth events.
• Consider defragmenting the database file system if there is a lot of auto-growth events on the databases.
• Never leave the database autogrowth with default values.
• Avoid using autogrowth in percentage, rather give a specific amount of size in MB/GB.
• Turn on the <MAXSIZE> setting for each file to prevent the file from taking up all available disk space.
• The growth increment of the transaction log must be large enough to stay ahead of the needs of the transaction units.

Change SQL Server database Autogrowth settings

Our Support Engineers employ two different ways to change SQL Server database Autogrowth settings:

• Using SQL Server Management Studio

1. Connect to SQL Server Instance using SQL Server Management Studio.
2. Expand Databases and right-click the database and select Properties to open up Database Properties to change the AutoGrowth settings for a database.
3. In Database Properties, select Files Page on the left side panel and then click on the “…” button to open up Change Autogrowth for the Database dialog box.
4. Then in the Change Autogrowth for Database dialog box change the Autogrowth setting in MegaBytes.
   For example, set the data file growth as 512 MB and Log File growth as 256 MB.
5. Once done, click OK to save and return to the Database Properties window.
6. In the Database Properties window, we can see that new values for Data and Log file Autogrowth are reflected. Click OK to make the changes.

• Using TSQL Script

With the below script we can change the database Autogrowth settings to grow data file at 512 MB and Log file at 256 MB.

Pass logical filename of the database file for under NAME section and replace DBName with the database name.

USE [master]
GO
ALTER DATABASE [DBName]
MODIFY FILE ( NAME = N’DBName’, FILEGROWTH = 512MB )
GO
ALTER DATABASE [DBName]
MODIFY FILE ( NAME = N’DBName_log’, FILEGROWTH = 256MB )
GO

How to View Total Number of Autogrow Events Occurred on a Database?

We can find the number of autogrowth events in the database by running the “Disk Usage” dashboard report. Right-click on the database and choose “Reports” and then select “Disk Usage”.

We will get the report for disk usage. Autogrowth events are also captured in the same report.

Once we expand the plus + sign corresponding to “Data/Log Files Autogrow/Autoshrink Events”, we can see the total number of autogrow events of the database. Based on this analysis, we can alter the autogrowth settings to the optimum value.

Taking these preventive and proactive measures will help improve the database performance and better manage the disk space utilization.

[Don’t let your Auto-growth Events Run Wild. We’d be happy to assist]

Conclusion

In short, we should not rely on the default SQL Server Data and Log file Autogrowth value and must always set it to more realistic values depending upon the usage of the database. Today, we saw how our Support Engineers perform this task.