How to Check Autogrowth in SQL Server Using Query?

by

In this SQL Server tutorial, I will show you how to check autogrowth in SQL Server using query.

In this tutorial, you will learn how to use the sys.master_files system view to check the autogrowth of a database and all the databases on the SQL Server. Also, use the sp_MSforeachdb and sp_helpfile system procedures to check the database growth.

You will see the database autogrowth in MB, KB, and percentage.

Check Autogrowth in SQL Server Using Query

The term autogrowth in the database is the increase of the data and log file size of the database when they reach their predefined size limits.

Suppose you have predefined the data size and log the file to 1 GB when it reaches 1 GB or runs out of available space and if you have enabled autogrowth in SQL Server.

The server automatically increases the file size to store the additional data or transaction logs. So autogrowth is a feature in SQL Server that stops database space-related errors by dynamically expanding the file’s size as needed.

So here you will understand how to check autogrowth in SQL Server using query.

Check Autogrowth in SQL Server Using Query for All Database

SQL Server has a file called sys.master_files system view that contains information about all the database files and their properties on SQL Server. Let’s use this system view and check the autogrowth of the database.

For that, use the below query.

SELECT 
DB_NAME(database_id) AS [Database Name], name AS [File Name],
growth AS [Growth Value],
CASE WHEN is_percent_growth = 1 THEN 'Percentage Growth' ELSE 'MB Growth'
END AS [Growth Type]
FROM sys.master_files
WHERE is_percent_growth = 1 OR growth > 128;
GO
Check Autogrowth in SQL Server Using Query for All Database

The output shows the data and log file with their growth value and growth type. It shows the growth type in MB and percentage growth.

Let’s understand the query part, the DB_NAME(database_id) returns the name of the data and the log file.

  • Then the growth AS [Growth Value] returns the autogrowth setting for the data and the log file.
  • The growth value indicates the file’s autogrowth increment, represented in megabytes (MB) and percentage based on the current size.
  • After that, within the CASE and END block, It checks the is_percent_growth column. If the is_percent_growth equals 1, it assigns the value ‘Percentage Growth’ to the Growth Type column. Otherwise, it assigns the value ‘MB Growth’ to that column.
  • Finally, in the WHERE clause, the query part is_percent_growth = 1 OR growth > 128 filter the results where it includes files with autogrowth defined as a percentage or the autogrowth value is greater than 128 MB.

Well, now you know the workings of the above query and how to check autogrowth in SQL Server of all the databases.

Check Autogrowth in SQL Server Using Query for a Database

You can also check autogrowth in SQL Server using query of the specific database. For that, you need to change the above query slightly.

For that, you can use the below syntax.

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

The above syntax highlights the changes part, where you must add this code (AND DB_NAME(database_id) = ‘Database_name’). In place of the ‘Database_name’, specify the actual name of your database whose autogrowth you want to check.

Let’s say you want to check the autogrowth of the E_commerce database, so use the below query.

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

The above query only shows the autogrowth of the E_Commerce database where the growth type is in MB Growth with a value of 8192. This is how to check autogrowth in SQL Server using query of the specific database in SQL Server.

Check Autogrowth in SQL Server Using Query with sp_MSforeachdb and sp_helpfile

The sp_MSforeachdb is a system procedure in SQL Server that loops through all the databases. If you want to perform some actions on the databases, you can specify that action with the sp_MSforeachdb procedure.

Next, the file sp_helpfile is also a system procedure that returns the information related to the database, such as logical file name, filename, size, maxsize, growth, etc.

Let’s use both procedures in combination to check the autogrowth of the databases using the below query.

EXEC sp_MSforeachdb 'use [?]; EXEC sp_helpfile'
Check Autogrowth in SQL Server Using Query with sp_MSforeachdb and sp_helpfile

Look at the above picture, which shows the growth of each database in the growth column, which is indicated through the red rectangle. It shows the growth in percentage and KB.

This is how you can check the autogrowth of the database in SQL Server using the sp_MSforeachdb and sp_helpfile.

Conclusion

In this SQL Server tutorial, you learned how to use the sys.master_files view to check autogrowth in SQL Server using query of the database—you also learned how to use the sp_MSforeachdb and sp_helpfile together to retrieve the autogrowth of all the databases on the SQL Server.

You may also like: