4

I have the following query below that shows size of a database and when this data was collected. I am trying to create a new column that has "average daily growth" which would be determined by the size on the previous day.

If my query captures data from multiple databases/instances how can I do this for each database? As seen in herephoto

I am trying to calculate daily growth per database using the "DB Size mb" column and would like a new column called "LastDayGrowth"

This is my query:

SELECT DISTINCT
        c.instance_name ,
        a.database_name ,
        CONVERT (DATE, SWITCHOFFSET(CAST (a.collection_time AS DATETIMEOFFSET),
                                    '+00:00')) AS 'Collection Time' ,
        'LDF mb' = ( CONVERT(DEC(15, 2), a.logsize) * 8192 / 1048576.0 ) ,
        'MDF mb' = ( CONVERT(DEC(15, 2), a.dbsize) * 8192 / 1048576.0 ) ,
        ( ( CONVERT(DEC(15, 2), a.dbsize) + CONVERT(DEC(15, 2), a.logsize) )
          * 8192 / 1048576.0 ) AS 'DB Size mb' ,
        'Reserved mb' = ( a.reservedpages * 8192 / 1048576.0 ) ,
        'Data mb' = CONVERT(DEC(15, 2), a.pages) * 8192 / 1048576.0 ,
        'Index mb' = ( a.usedpages - a.pages ) * 8192 / 1048576.0 ,
        'Unused mb' = ( ( CONVERT(DEC(15, 2), a.reservedpages)
                          - CONVERT(DEC(15, 2), a.usedpages) ) * 8192
                        / 1048576.0 ) ,
        'Unallocated mb' = ( CASE WHEN a.dbsize >= a.reservedpages
                                  THEN ( CONVERT (DEC(15, 2), a.dbsize)
                                         - CONVERT (DEC(15, 2), a.reservedpages) )
                                       * 8192 / 1048576.0
                                  ELSE 0
                             END )
FROM    MDWDB.snapshots.disk_usage a
        INNER JOIN MDWDB.core.snapshots_internal b ON a.snapshot_id = b.snapshot_id
        INNER JOIN MDWDB.core.source_info_internal c ON b.source_id = c.source_id
ORDER BY c.instance_name ,
        a.database_name ASC;

I have tried this using the "LAG" function but did not succeed. I tried it like this:

LastDayGrowth = DATEDIFF(DAY,
                         LAG(( CONVERT(DEC(15, 2), a.dbsize)
                               + CONVERT(DEC(15, 2), a.logsize) )
                             * 8192 / 1048576.0, 1, NULL) OVER ( ORDER BY a.database_name, ( ( CONVERT(DEC(15,
                                                      2), a.dbsize)
                                                      + CONVERT(DEC(15,
                                                      2), a.logsize) )
                                                      * 8192
                                                      / 1048576.0 ) ),
                         ( ( CONVERT(DEC(15, 2), a.dbsize)
                             + CONVERT(DEC(15, 2), a.logsize) ) * 8192
                           / 1048576.0 ))

Any tips, or advice would be greatly appreciated!

2

I just read this article adressing this topic and I suppose you might be interested, because it uses the lag function to solve exactly your Task:

https://www.mssqltips.com/sqlservertip/3690/identify-sql-server-database-growth-rates/

The Author created the following Script using the LAG Window function:

SELECT DISTINCT
    A.[database_name]
,   AVG( A.[Backup Size (MB)] - A.[Previous Backup Size (MB)] ) OVER ( PARTITION BY A.[database_name] ) AS [Avg Size Diff From Previous (MB)]
,   MAX( A.[Backup Size (MB)] - A.[Previous Backup Size (MB)] ) OVER ( PARTITION BY A.[database_name] ) AS [Max Size Diff From Previous (MB)]
,   MIN( A.[Backup Size (MB)] - A.[Previous Backup Size (MB)] ) OVER ( PARTITION BY A.[database_name] ) AS [Min Size Diff From Previous (MB)]
,   A.[Sample Size]
FROM 
(
    SELECT
        s.[database_name]
    --, s.[backup_start_date]
    ,   COUNT(*) OVER ( PARTITION BY s.[database_name] ) AS [Sample Size]
    ,   CAST ( ( s.[backup_size] / 1024 / 1024 ) AS INT ) AS [Backup Size (MB)]
    ,   CAST ( ( LAG(s.[backup_size] ) 
            OVER ( PARTITION BY s.[database_name] ORDER BY s.[backup_start_date] ) / 1024 / 1024 ) AS INT ) AS [Previous Backup Size (MB)]
    FROM 
        [msdb]..[backupset] s
    WHERE
        s.[type] = 'D' --full backup
    --ORDER BY
    --  s.[database_name]
    --, s.[backup_start_date]
) AS A
ORDER BY
    [Avg Size Diff From Previous (MB)] DESC;
GO
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.