Home > SQL and .NET Blog > High growth rate of file groups in SQL Server Database

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

Advertisements
Categories: SQL and .NET Blog
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: