SQL SERVER – Find the Growth Size for All files in All Databases

I am a big time advocate of some common best practices that I see in the industry. These have been ingrained into every session that I do be it SQL Server, MySQL or any other databases. Though I work extensively on SQL Server and from time-to-time on other databases, the concepts and basics have remained the same across all these variants. One of the practices that I always advocate is the use of “Auto Growth” settings inside SQL Server. The setting is good but I always say, it is there as a safety net. In reality this setting should never be triggered and as a DBA, it is important that we know when the files are getting expanded. As a DBA, you need to build capacity in your environment whenever these settings hit the server. For example, a 10% growth doesn’t look alarming at initial but can be dangerous because 10% of a 1GB database file grows 100MB while for a 100GB database that will grow by 10GB. As a DBA, we are required to be cognizant of the setting and know when the growth happens.

Not very long ago, one of my blog readers sent me below email

Hi Pinal,
My apologies to send you direct email, but this was really urgent. Do you know a quick way to find the growth size set for all the databases on a SQL instance. I was able to figure out that I can use sp_helpfile. Unfortunately it gives details about the files of the database in which I’m running the stored procedure sp_helpfile.

Any quick reply would be appreciated. Thanks in advance!

As always, I searched for it at search.sqlauthority.com and was surprised to see that there were no results. Here is the query which I came up with.

SELECT   'Database Name' = DB_NAME(database_id)
'FileName' = NAME
,'size' = CONVERT(NVARCHAR(15), CONVERT(BIGINT, size) * 8) + N' KB'
,'maxsize' = (
CASE max_size
WHEN - 1
THEN N'Unlimited'
'growth' = (
CASE is_percent_growth
THEN CONVERT(NVARCHAR(15), growth) + N'%'
'type_desc' = type_desc
FROM sys.master_files
ORDER BY database_id


If you find a better way, please share it via comments. The best way to learn is to learn from each other. Do let me know have you ever encountered this situation in your life?

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

Previous Post
SQL SERVER – Mistake to Avoid: Duplicate and Overlapping Indexes – Notes from the Field #073
Next Post
SQL SERVER – System procedures to know SQL Server Version

Related Posts

No results found.

9 Comments. Leave new

  • Hi Pinal,

    I’ve read a lot of your blog entries and I’ve learned a lot from you. When I see new scripts presented in a blog I always check to see if I have an equivalent script in my own library. In this case, I discovered that the, very excellent, diagnostic information queries written by Glenn Barry include a similar query to the one you provided. Here is the specific query:
    SELECT DB_NAME([database_id]) AS [Database Name],
    [file_id], name, physical_name, type_desc, state_desc,
    is_percent_growth, growth,
    CONVERT(bigint, growth/128.0) AS [Growth in MB],
    CONVERT(bigint, size/128.0) AS [Total Size in MB]
    FROM sys.master_files WITH (NOLOCK)
    WHERE [database_id] > 4
    AND [database_id] 32767
    OR [database_id] = 2

    I compared the output of both queries and discovered that the growth column in your query may be incorrect. You list it as a value in KB, but I believe it may be pages. In Glenn’s query he divides that number by 128 to get a value in MB. Please review and update your post as you feel necessary.


  • same her, not working

    • What do you mean by not working?

      • Dominic Wirth
        June 20, 2017 5:15 pm

        I think he’s referring to John’s script where the second argument of the WHERE clause is missing an equal Symbol. Your’s is working Pinal

  • Thanks for posting this. Here’s a merged version of Pinal’s and John Fowler’s queries:

    DB_NAME([database_id]) AS [Database],
    --state_desc AS [Status],
    type_desc AS [File Type],
    name AS [Logical Name],
    CONVERT(bigint, size/128.0) AS [Initial Size (MB)],
    CASE is_percent_growth
    WHEN 1
    THEN CONVERT(nvarchar(16), growth) + N'%'
    ELSE CONVERT(nvarchar(16), CONVERT(bigint, growth/128.0)) + N' MB'
    END AS [Autogrowth],
    CASE max_size
    WHEN -1
    THEN N'Unlimited'
    ELSE CONVERT(NVARCHAR(16), CONVERT(bigint, max_size/128.0))
    END AS [Maxsize (MB)],
    physical_name AS [File Path]
    sys.master_files WITH (NOLOCK)
    -- [database_id] > 4
    DB_NAME([database_id]) OPTION (RECOMPILE);

  • Asutosha Sarangi
    April 28, 2020 2:56 pm

    Hi I know how much space my DB needs day to day ?

  • sathyanarayanan
    April 26, 2022 3:59 pm

    Can any one help me with the database growth to know for last 6 months ?

  • sathyanarayanan
    April 26, 2022 4:09 pm

    Below script will help you to get the month wise details of database growth.

    [database_name] AS “Database”,
    DATEPART(month,[backup_start_date]) AS “Month”,
    AVG([backup_size]/1024/1024) AS “Backup Size MB”,
    AVG([compressed_backup_size]/1024/1024) AS “Compressed Backup Size MB”,
    AVG([backup_size]/[compressed_backup_size]) AS “Compression Ratio”
    FROM msdb.dbo.backupset
    WHERE [database_name] = N’DLBDN222′
    AND [type] = ‘D’
    GROUP BY [database_name],DATEPART(mm,[backup_start_date]);


Leave a Reply