Are you trying to modify SQL Server database Autogrowth settings? 

This guide will help you.

SQL Database Auto-growth is a procedure due to which SQL Server engine expands its database size when all its space runs out. The amount due to which the size of database file grows is based on the settings, which is for the growth of file option for database.

Here at Ibmi Media, as part of our Server Management Services, we regularly help our Customers to perform SQL related queries.

In this context, we shall look into how to modify SQL server database Autogrowth settings.

More Information about 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 to consider to reduce the autogrow events in a database to improve performance.

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

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

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

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

How to perform Database Autogrowth Property efficiently ?

To improve the database performance and manage the disk space utilization, apply the following tips;

i. Analyze the database growth pattern and set the optimum size of the database with the analyzed growth settings.

ii. Proactively monitor database file sizes and their autogrowth events.

iii. Consider defragmenting the database file system if there is a lot of auto-growth events on the databases.

iv. Never leave the database autogrowth with default values.

v. Avoid using autogrowth in percentage, rather give a specific amount of size in MB/GB.

vi. Turn on the <MAXSIZE> setting for each file to prevent the file from taking up all available disk space.

vii. The growth increment of the transaction log must be large enough to stay ahead of the needs of the transaction units.

How to Modify SQL Server database Autogrowth settings ?

Here, we will consider two different ways to change SQL Server database Autogrowth settings:

1. Using SQL Server Management Studio

i. Connect to 'SQL Server Instance' using SQL Server Management Studio.

ii. Expand Databases and right-click the database and select 'Properties' to open up 'Database Properties' to change the AutoGrowth settings for a database.

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

iv. Then in the 'Change Autogrowth for Database' dialog box change the Autogrowth setting in 'MegaBytes'.

For instance, set the data file growth as 512 MB and Log File growth as 256 MB.

v. Once done, click 'OK' to save and return to the 'Database Properties' window.

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

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

[Having issues with SQL queries. We'd be happy to assist. ]