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.

Solarwinds

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
,FILE_ID
,'size' = CONVERT(NVARCHAR(15), CONVERT(BIGINT, size) * 8) + N' KB'
,'maxsize' = (
CASE max_size
WHEN - 1
THEN N'Unlimited'
ELSE CONVERT(NVARCHAR(15), CONVERT(BIGINT, max_size) * 8) + N' KB'
END
)
,
'growth' = (
CASE is_percent_growth
WHEN 1
THEN CONVERT(NVARCHAR(15), growth) + N'%'
ELSE CONVERT(NVARCHAR(15), CONVERT(BIGINT, growth) * 8) + N' KB'
END
)
,
'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)

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

7 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
    ORDER BY DB_NAME([database_id]) OPTION (RECOMPILE);

    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.

    Thanks,
    John

    Reply
  • same her, not working

    Reply
    • What do you mean by not working?

      Reply
      • 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:


    SELECT
    --[file_id],
    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]
    FROM
    sys.master_files WITH (NOLOCK)
    --WHERE
    -- [database_id] > 4
    ORDER BY
    DB_NAME([database_id]) OPTION (RECOMPILE);

    Reply
  • Asutosha Sarangi
    April 28, 2020 2:56 pm

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

    Reply

Leave a Reply

Menu