Database Growth Information in SQL Server

DBA’s and Developers often have a need to query the current state of database file size and growth information. This script allows you to obtain file-growth information from single or all databases. It provides the following information:

1. Local Server Name
2. Database Name
3. Logical File Name
4. Initial File Size
5. File Growth Value/Percentage
6. Any Growth Restrictions

Please create and run the below stored procedure without any parameters to obtain help on how to execute this stored procedure.

CREATE Procedure [dbo].[SQLOPS_FileGrowthREPORT] (@Cmd Varchar(20)=null, @DBName Varchar(255)='ALL') as
SET NOCOUNT ON
 
--Author: Saleem Hakani (http://sqlcommunity.com)
--Desc: This SPROC provides you with filegrowth information for all databases of your local SQL Server instance.
 
If (@Cmd is null or @Cmd <> 'Execute')
Begin
   Print 'Get file growth details of all databases for all files from your local SQL Server instance.'
   Print ' ========================================================================='
   Print 'Author: Saleem Hakani (http://sqlcommunity.com)'
   Print ' '
   Print 'Syntax: Exec SQLOPS_FileGrowthREPORT <@cmd>'
   Print '@Cmd can be '
   Print ' '
   Print 'To obtain help for this stored procedure:'
   Print 'Example: Exec SQLOPS_FileGrowthREPORT ''Help'';'
   Print ' '
   Print 'To obtain file growth information for all data and log files for all databases:'
   Print 'Exec SQLOPS_FileGrowthREPORT ''Execute'';'
   Print ' '
   Return
End
If (@Cmd='Execute' and @DBName='ALL')
   Begin
      Select
         UPPER(@@ServerName) as 'SERVER_NAME',
         UPPER(DB_Name(Database_ID)) as 'DATABASE_NAME',
         UPPER(Name) as LOGICAL_FILENAME,
         SIZE/128 as 'CURRENT/INITIAL_FILESIZE_IN_MB',
         (
            CASE is_percent_Growth
               WHEN '1' THEN convert(Varchar,Growth) +' %'
               ELSE Convert(Varchar,Growth/128) +' MB' End) as 'FILE_GROWTH',
        (
           CASE max_size
              WHEN 0 THEN 'RESTRICTED'
              WHEN -1 THEN 'UNRESTRICTED'
              WHEN 268435456 THEN 'UPTO 2 TB'
              ELSE 'RESTRICTED ('+convert(varchar,Max_Size/128)+' MB)'
          END
        ) as 'RESTRICTIONS'
        FROM sys.master_files
   RETURN
   End
   If (@Cmd='Execute' and @DBName<>'ALL')
   Begin
 
      IF Not Exists (Select Name from Sys.Databases Where Name=@DBName)
         Begin
            Print 'Database ['+@DBName+'] does not exist. Please enter a valid database name.'
            RETURN
         End
 
     If Exists (Select Name from Sys.Databases Where Name=@DBName)
        Begin
           Select
              UPPER(@@ServerName) as 'SERVER_NAME',
              UPPER(DB_Name(Database_ID)) as 'DATABASE_NAME',
              UPPER(Name) as LOGICAL_FILENAME,
              SIZE/128 as 'CURRENT/INITIAL_FILESIZE_IN_MB',
         (
         CASE is_percent_Growth
            WHEN '1' THEN convert(Varchar,Growth) +' %'
            ELSE Convert(Varchar,Growth/128) +' MB' End) as 'FILE_GROWTH',
           (
           CASE max_size
              WHEN 0 THEN 'RESTRICTED'
              WHEN -1 THEN 'UNRESTRICTED'
              WHEN 268435456 THEN 'UPTO 2 TB'
              ELSE 'RESTRICTED ('+convert(varchar,Max_Size/128)+' MB)'
           END
           ) as 'RESTRICTIONS'
           from sys.master_files Where Database_ID=db_ID(@DBName)
--where Max_Size not in (-1, 268435456)
       RETURN
       END
END
GO