Find database growth size in SQL Server

All of a sudden I received an email from the database owner –

“pls share file growth if u have so that the sizing can be done better”

We don’t have any mechanism or 3rd party tool that monitored such historical data. Also, no trace was enabled previously on the server. I started searching on internet for a quick way to find the growth size set for all the databases on a SQL instance. Thanks to blog.sqlauthority.com I got my solution with two TSQL scripts that end up my search.

Here is the TSQL script

SELECT
    -- Retrieve the name of the database
    'Database Name' = DB_NAME(database_id),
    -- Retrieve the name of the file
    'FileName' = NAME,
    -- Retrieve the ID of the file
    FILE_ID,
    -- Convert the size to kilobytes and format it as a string
    'size' = CONVERT(NVARCHAR(15), CONVERT(BIGINT, size) * 8) + N' KB',
    -- Determine the maximum size of the file
    'maxsize' = (
        CASE max_size
            -- If the maximum size is -1, it is considered unlimited
            WHEN -1 THEN N'Unlimited'
            -- Convert the maximum size to kilobytes and format it as a string
            ELSE CONVERT(NVARCHAR(15), CONVERT(BIGINT, max_size) * 8) + N' KB'
        END
    ),
    -- Determine the growth rate of the file
    'growth' = (
        CASE is_percent_growth
            -- If the growth rate is in percentage, format it with a percent sign
            WHEN 1 THEN CONVERT(NVARCHAR(15), growth) + N'%'
            -- Convert the growth rate to kilobytes and format it as a string
            ELSE CONVERT(NVARCHAR(15), CONVERT(BIGINT, growth) * 8) + N' KB'
        END
    ),
    -- Retrieve the description of the file type
    'type_desc' = type_desc
FROM sys.master_files
ORDER BY database_id

Here is the result

Here is another TSQL script 

SELECT
    -- Retrieve the database name using the database ID
    DB_NAME([database_id]) AS [Database],
    -- Retrieve the file type
    type_desc AS [File Type],
    -- Retrieve the logical name of the file
    name AS [Logical Name],
    -- Convert the size to initial size in megabytes
    CONVERT(BIGINT, size / 128.0) AS [Initial Size (MB)],
    -- Determine the autogrowth rate of the file
    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],
    -- Determine the maximum size of the file in megabytes
    CASE max_size
        WHEN -1 THEN N'Unlimited'
        ELSE CONVERT(NVARCHAR(16), CONVERT(BIGINT, max_size / 128.0))
    END AS [Maxsize (MB)],
    -- Retrieve the physical file path
    physical_name AS [File Path]
FROM sys.master_files WITH (NOLOCK)
--WHERE
-- [database_id] > 4
ORDER BY DB_NAME([database_id])
OPTION (RECOMPILE);

Here is the result 

If you want to Calculate database size ‘Growth Rates’ on daily basis in SQL Server here is another post.