Database Growth

  • Hi,

    Just entered a new role in my company as junior DBA.

    I am looking for assistance or guidance with regards to a script to see the weekly growth of all our databases in the company for reporting purposes.

    At this stage I am looking at about +- 20 databases and we need to establish a trend for the growth in the company.

    Currently I am using the following query below but would like a more detailed query so that I can get a graph going in Excel. Also this query is only for one database at a time and I would like to execute one query for all the databases.

    SELECT

    [database_name] AS "Database",

    DATEPART(month,[backup_start_date]) AS "Month",

    AVG([backup_size]/1024/1024) AS "Backup Size MB",

    AVG([compressed_backup_size]/1024/1024) AS "Compressed Backup Size MB",

    AVG([backup_size]/[compressed_backup_size]) AS "Compression Ratio"

    FROM msdb.dbo.backupset

    WHERE [database_name] = N'XXX'

    AND [type] = 'D'

    GROUP BY [database_name],DATEPART(mm,[backup_start_date]);

    Kind Regards,

  • Hi,

    My first step would be to set up a Central Management Server. There is a great article on it here:

    http://www.brentozar.com/archive/2008/08/sql-server-2008s-new-central-management-server/

    Then I would execute sp_spaceused against your group of servers, see this forum post for more..

    http://social.msdn.microsoft.com/Forums/en-US/b9b08c8e-2bd6-40d0-a77a-eb30f213d7eb/tsql-for-database-size-free-space-or-used-space-in-database?forum=transactsql

    Ta

    David

    ============================================================
    David

    http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

  • You don't need to set up a central management server if you're the only DBA and you are only hitting one server.

    You can run something like this,

    CREATE TABLE #TEMP1 (

    DATABASE_NAME VARCHAR(500)

    , LOGICAL_NAME VARCHAR(500)

    , PHYSICAL_NAME VARCHAR(500)

    , FILE_SIZE INT

    )

    DECLARE @name VARCHAR(500)

    DECLARE database_cursor CURSOR

    FOR

    SELECT NAME

    FROM sysdatabases

    ORDER BY dbid ASC

    OPEN database_cursor;

    FETCH NEXT

    FROM database_cursor

    INTO @name;

    WHILE @@fetch_status = 0

    BEGIN

    INSERT INTO #TEMP1 (

    DATABASE_NAME

    , LOGICAL_NAME

    , PHYSICAL_NAME

    , FILE_SIZE

    )

    SELECT DB_NAME(database_id)

    , NAME

    , Physical_Name

    , (size * 8) / 1024

    FROM sys.master_files

    WHERE DB_NAME(database_id) = @name

    FETCH NEXT

    FROM DATABASE_CURSOR

    INTO @name;

    END

    CLOSE DATABASE_cursor;

    DEALLOCATE DATABASE_cursor;

    GO

    This will return db and log file sizes, you can modify it to only show db sizes if you want and you can also look at sys.master_files to get more data like max db size, growth amount, etc. If you need more data on top of that, you can simply join to the statements I provided to get more data. You can also mix this up a bit, put the data into a permanent table for you to review in the future, etc. If you put this into a permanent table, make sure to add a datetime variable so you know when this script was run. You can also do the same thing with while loops instead of a fetch if you'd like. Also, make sure to run this against the master DB.

  • pietero 48156 (2/26/2014)


    Hi,

    Just entered a new role in my company as junior DBA.

    I am looking for assistance or guidance with regards to a script to see the weekly growth of all our databases in the company for reporting purposes.

    At this stage I am looking at about +- 20 databases and we need to establish a trend for the growth in the company.

    Currently I am using the following query below but would like a more detailed query so that I can get a graph going in Excel. Also this query is only for one database at a time and I would like to execute one query for all the databases.

    SELECT

    [database_name] AS "Database",

    DATEPART(month,[backup_start_date]) AS "Month",

    AVG([backup_size]/1024/1024) AS "Backup Size MB",

    AVG([compressed_backup_size]/1024/1024) AS "Compressed Backup Size MB",

    AVG([backup_size]/[compressed_backup_size]) AS "Compression Ratio"

    FROM msdb.dbo.backupset

    WHERE [database_name] = N'XXX'

    AND [type] = 'D'

    GROUP BY [database_name],DATEPART(mm,[backup_start_date]);

    Kind Regards,

    So take the database Name out of the WHERE clause, format the month as YYYY_MM, and maybe throw in a ROLLUP or CUBE on the GROUP BY.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • JoshDBGuy (2/26/2014)


    You don't need to set up a central management server if you're the only DBA and you are only hitting one server.

    You can run something like this,

    CREATE TABLE #TEMP1 (

    DATABASE_NAME VARCHAR(500)

    , LOGICAL_NAME VARCHAR(500)

    , PHYSICAL_NAME VARCHAR(500)

    , FILE_SIZE INT

    )

    DECLARE @name VARCHAR(500)

    DECLARE database_cursor CURSOR

    FOR

    SELECT NAME

    FROM sysdatabases

    ORDER BY dbid ASC

    OPEN database_cursor;

    FETCH NEXT

    FROM database_cursor

    INTO @name;

    WHILE @@fetch_status = 0

    BEGIN

    INSERT INTO #TEMP1 (

    DATABASE_NAME

    , LOGICAL_NAME

    , PHYSICAL_NAME

    , FILE_SIZE

    )

    SELECT DB_NAME(database_id)

    , NAME

    , Physical_Name

    , (size * 8) / 1024

    FROM sys.master_files

    WHERE DB_NAME(database_id) = @name

    FETCH NEXT

    FROM DATABASE_CURSOR

    INTO @name;

    END

    CLOSE DATABASE_cursor;

    DEALLOCATE DATABASE_cursor;

    GO

    This will return db and log file sizes, you can modify it to only show db sizes if you want and you can also look at sys.master_files to get more data like max db size, growth amount, etc. If you need more data on top of that, you can simply join to the statements I provided to get more data. You can also mix this up a bit, put the data into a permanent table for you to review in the future, etc. If you put this into a permanent table, make sure to add a datetime variable so you know when this script was run. You can also do the same thing with while loops instead of a fetch if you'd like. Also, make sure to run this against the master DB.

    You could just run this

    SELECT DB_NAME(database_id)

    , NAME

    , Physical_Name

    , (size * 8) / 1024

    FROM sys.master_files

    instead of looping through the databases.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Here is an alternative script that could help with your trending report

    DECLARE @SQLVer SQL_VARIANT

    ,@DBName VARCHAR(128)

    ,@NumDaysSMALLINT

    ,@SQLvarchar(1024)

    ,@WhereClauseVARCHAR(256)

    SET @DBName = 'TFCCS_Transfers'

    ;

    SET @NumDays = 90

    ;

    SET @SQLVer = CONVERT(INTEGER, PARSENAME(CONVERT(VARCHAR(20),SERVERPROPERTY('ProductVersion')),4));

    SET @WhereClause = 'WHERE a.type IN (''D'',''I'')

    And a.backup_start_date > GETDATE()- ' + Cast(@NumDays as varchar)+''

    IF @DBName IS NOT NULL

    Begin

    SET @WhereClause = @WhereClause + '

    AND a.database_name = '''+ @DBName +''''

    END

    Set @SQL = '

    SELECT a.database_name,a.backup_start_date,a.backup_finish_date

    ,datediff(minute,a.backup_start_date,a.backup_finish_date) as Duration

    ,b.physical_device_name AS BackupPath

    ,a.position

    ,a.type

    ,a.backup_size/1024/1024 AS BackupSizeMB

    ,' + CASE

    WHEN @SQLVer < 10

    THEN '0'

    ELSE 'a.compressed_backup_size/1024/1024'

    END + ' AS CompressedBackMB

    FROM msdb.dbo.backupset a

    INNER JOIN msdb.dbo.backupmediafamily b

    ON a.media_set_id = b.media_set_id

    ' + @WhereClause + '

    ORDER BY a.database_name,a.backup_start_date;'

    --PRINT @SQL

    Execute (@SQL);

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

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

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