track the growth of SQL Database..

  • In my previous company, i was asked to track the growth rate of our database because we had a daily transaction table that gets updated everyday..

    So i wrote the following procedure and it is also tested on SQL Server 7.0 & SQL Server 2000.

    CREATE PROC sp_track_db_growth

    (

    @dbnameParam sysname = NULL

    )

    AS

    BEGIN

    /***********************************************************************************************************

    Purpose: To calulate the file growth percentages for a given database and to show you the rate at which

    your databases are growing, so that you can plan ahead for your future storage needs.

    Tested on: SQL Server 7.0, SQL Server 2000

    Usage: Run this script in the master database to create the stored procedure. Once it is created,

    you could run it from any of your user databases. If the first parameter (database name) is

    not specified, the procedure will use the current database.

    Example 1:

    To see the file growth information of the current database:

    EXEC sp_track_db_growth

    Example 2:

    To see the file growth information for pubs database:

    EXEC sp_track_db_growth 'pubs'

    ***********************************************************************************************************/

    DECLARE @dbname sysname

    /* Work with current database if a database name is not specified */

    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 logical_name, [Date]

    END

  • Not sure if you knew this but you can actually post scripts to the Script section on this site for others to find and use long term so, you might want to check that out. Left hand side of the page, select Scripts and I think there is a link to post them there.

    Thanks for sharing!

    David

    @SQLTentmaker

    β€œHe is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • Nice job.

    But in 2005 and 2008 you could do it using CTE (Common table Expressions) instead of dierived tables, because the script costs much time if there is a great numberes of backups done.

    Thanks anyway

    Lucas Benevides

    DBA Cabuloso

    ________________
    DBA Cabuloso
    Lucas Benevides

  • Agree with all it is a nice script for 2K and under. Well done!

    Unfortunately we never do backups so it would be moot here πŸ˜€


    John Zacharkan

  • you can find the same SP here πŸ˜›

    http://vyaskn.tripod.com/code/sp_track_db_growth.txt

  • This is an effective way to find DB growth for sure, but if the backup job failed for 10 days and none had addressed it (rare case), then of no use.

    Thanks.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply