SQL SERVER – Monitoring Database Autogrowth Settings

Monitoring Database Autogrowth Settings is important and is what I often end up discussing in Comprehensive Database Performance Health Check; Autogrowth allows databases to automatically increase file size when more space is needed. However, excessive autogrowth events can cause performance issues. In this post, we’ll look at a script to check autogrowth settings for all databases in SQL Server.

Databases are typically configured to increase in size by 10% increments when they reach total capacity. However, this default setting can have some negative effects. For instance, busy databases may require frequent expansions, which can slow down the execution of queries. Additionally, the 10% growth increments can result in the allocation of large amounts of unused space.

To mitigate these issues, experts recommend adjusting the auto-growth setting to match the database’s weekly file growth. It is also advisable to pre-size the files appropriately to minimize the need for expansions. By following these best practices, database administrators can optimize the performance of their systems and avoid unnecessary complications.

Monitoring Database Autogrowth

This T-SQL script queries sys.master_files to check auto growth for each database:

SELECT DB_NAME(mf.database_id) AS [Database Name],  
mf.name AS [File Name],
mf.growth AS [Growth Value],
CASE WHEN mf.is_percent_growth = 1 
   THEN 'Percentage Growth'
   ELSE 'MB Growth'
   END AS [Growth Type]
FROM sys.master_files mf 
WHERE mf.is_percent_growth = 1
   OR mf.growth > 128;

This allows spotting databases needing adjustment.

To change autogrowth for a database:

ALTER DATABASE databasename  
MODIFY FILE (NAME = filename, FILEGROWTH = 256MB);

Best Practices for Auto Growth

Here are a few topics I have recently discussed with my clients during Performance Tuning Consulting.

Set Reasonable Initial Sizes: The initial size of a database should be set to an appropriate value based on the expected data size. This can help prevent frequent auto-growth events, which can impact performance.

Use Appropriate Autogrowth Increments: Rather than allowing the database to grow by a small, fixed-size each time, consider setting autogrowth to occur in percentages. This can help prevent the creation of too many virtual log files (VLFs) in transaction log files. However, be cautious about setting the percentage too high, as this can lead to the database suddenly consuming a lot of disk space.

Monitor Free Space and Growth Events: Regularly monitor the free space in your database files and the number of autogrowth events. Frequent autogrowth events can impact performance and indicate that the initial file size or growth increment is too small.

Consider Instant File Initialization (IFI): IFI is a feature in SQL Server that allows data files to be instantly initialized. This can greatly reduce the time it takes for autogrowth events to occur, but it doesn’t apply to log files. Note that IFI has security implications that should be considered.

Regularly Review and Adjust Settings: As your database usage changes, so should your autogrowth settings. Regularly review these settings and make adjustments as necessary.

Ensure Enough Disk Space: Autogrowth can only work if there is sufficient disk space. Always monitor your disk space usage and ensure enough space for your databases to grow.

Avoid Using ‘Autoshrink’: While it might seem like a good counterpart to autogrowth, autoshrink can lead to fragmented databases and should generally be avoided.

Pre-grow Your Databases: If you know you will be importing a large amount of data, pre-grow your databases to accommodate this data. This can help avoid performance hits from autogrowth events.

Well, let me know if you have any questions or suggestions.

Do not forget to subscribe to my youtube channel.

Reference: Pinal Dave (https://blog.sqlauthority.com)