High growth rate of file groups in SQL Server Database

 

This below query can be used to find all the high growth rate file groups in a SQL server database and

also helps in fine tuning those file groups alone.

USE master

GO

CREATE PROC sp_track_db_growth(@dbnameParam sysname = NULL)

AS

BEGIN

DECLARE @dbname sysname

-- Set the current DB, if dbname is not given input

SET @dbname = COALESCE(@dbnameParam, DB_NAME())

SELECT    CONVERT(char, backup_start_date, 111) AS [Date], --yyyy/mm/dd format

    CONVERT(char, backup_start_date, 108) AS [Time],

    @dbname AS [Database Name], [filegroup_name] AS [Filegroup Name], logical_name AS [Logical Filename], 

    physical_name AS [Physical Filename], CONVERT(numeric(9,2),file_size/1048576) AS [File Size (MB)],

    Growth AS [Growth Percentage (%)]

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 = @dbname 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 = @dbname AND

        a.file_type = 'D' AND

        b.type = 'D'

) as Derived

WHERE (Growth <> 0.0) OR (Growth IS NULL)

ORDER BY [Growth Percentage (%)] desc,logical_name, [Date] desc

END

-Yuva

http://Yuvahere.com