Monday, September 17, 2012

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

 

3 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. This comment has been removed by the author.

    ReplyDelete
  3. Looking at the output it tells you the dbfilesize, would be handy if it compared the actual data content within the file, this would give a more accurate measurement of real growth imo....anyone else comment on this?

    ReplyDelete