How to Find Database Growth using T-SQL

In this post, I am going to share a stored procedure to calculate the percentage of file growth of a database. This growth rate can be handy to plan ahead for future storage needs.


IF OBJECT_ID(N'dbo.DatabaseGrowth', 'P') IS NOT NULL
BEGIN
    PRINT 'Dropping procedure dbo.DatabaseGrowth'
    DROP PROCEDURE dbo.DatabaseGrowth
END
GO

PRINT 'Creating procedure dbo.DatabaseGrowth'
GO

CREATE PROC dbo.DatabaseGrowth @pDBName sysname = NULL
AS
BEGIN
/*********************************************************************************
Description: Procedure to calulate the file growth %ages for a given database and
show the growth rate so that we can plan ahead for future storage needs.

How to use:
--------------
Example 1: To see the file growth of the current database:
EXEC dbo.DatabaseGrowth

Example 2: To see the file growth for [Test] database:
EXEC dbo.DatabaseGrowth 'Test'

--------------------------------------------------------------------------------
Change History
--------------------------------------------------------------------------------
Date Name Comments
---------- ----------- ---------------------------------------------------------
01/15/2012 Hari Sharma Created
********************************************************************************/
SET NOCOUNT ON;
DECLARE @DatabaseName SYSNAME

-- Use current database, if a database name is not specified in input parameter
SET @DatabaseName = ISNULL(@pDBName, DB_NAME())
SELECT  backup_start_date AS StartTime
        ,@DatabaseName AS DatabaseName
        ,filegroup_name AS FilegroupName
        ,logical_name AS LogicalFilename
        ,physical_name AS PhysicalFilename
        ,CONVERT(NUMERIC(9,2), file_size/1048576) AS FileSizeInMB
        ,Growth AS PercentageGrowth
FROM (
     SELECT b.backup_start_date
           ,a.backup_set_id
           ,a.file_size
           ,a.logical_name
           ,a.[filegroup_name]
           ,a.physical_name
           ,(SELECT CONVERT(numeric(5,2),((a.file_size * 100.00)/i1.file_size)-100)
             FROM msdb.dbo.backupfile i1
             WHERE i1.backup_set_id =
              (
              SELECT MAX(i2.backup_set_id)
              FROM msdb.dbo.backupfile i2 JOIN msdb.dbo.backupset i3
              ON i2.backup_set_id = i3.backup_set_id
              WHERE i2.backup_set_id < a.backup_set_id
              AND i2.file_type='D'
              AND i3.database_name = @DatabaseName
              AND i2.logical_name = a.logical_name
              AND i2.logical_name = i1.logical_name
              AND i3.type = 'D'
              )
              AND i1.file_type = 'D'
) AS Growth
FROM msdb.dbo.backupfile a
JOIN msdb.dbo.backupset b
ON a.backup_set_id = b.backup_set_id
WHERE b.database_name = @DatabaseName
AND a.file_type = 'D'
AND b.type = 'D'
) AS Derived
WHERE   ISNULL(Growth, 0.0) <> 0.0
ORDER BY logical_name, StartTime
END

GO