SELECT FROM CTE TO GET DB GROWTH DATE AND AMOUNT

  • Budd

    Hall of Fame

    Points: 3659

    I was going to post this as a HOW-TO question, but I think I've figured it out and now I just want to know Could it be done better?

    Here is a sample of the data I had to work with and what I did.

    IF OBJECT_ID('tempdb..#SQL_DbGrowthTracking') IS NOT NULL

    DROP TABLE #SQL_DbGrowthTracking

    CREATE TABLE #SQL_DbGrowthTracking(

    DatabaseName VARCHAR(100) NOT NULL,

    FileNamePath VARCHAR(512) NOT NULL,

    TotalSize_in_MB BIGINT NOT NULL,

    CrtDate DATE NOT NULL

    )

    GO

    -------INSERT A COLLECTION OF SAMPLE DATA

    INSERT INTO #SQL_DbGrowthTracking --DatabaseName FileNamePath TotalSize_in_MB CrtDate

    VALUES ('master', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\master.mdf', 4 ,'2019-11-01'),

    ('master', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\master.mdf', 4 ,'2019-10-01'),

    ('master', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\master.mdf', 4 ,'2019-09-01'),

    ('master', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\master.mdf', 4 ,'2019-08-29'),

    ('master', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\master.mdf', 4 ,'2019-06-01'),

    ('master', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\master.mdf', 4 ,'2019-05-01'),

    ('master', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\master.mdf', 4 ,'2019-04-01'),

    ('master', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\master.mdf', 4 ,'2019-03-27'),

    ('master', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\mastlog.ldf', 3 ,'2019-11-01'),

    ('master', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\mastlog.ldf', 3 ,'2019-10-01'),

    ('master', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\mastlog.ldf', 3 ,'2019-09-01'),

    ('master', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\mastlog.ldf', 3 ,'2019-08-29'),

    ('master', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\mastlog.ldf', 3 ,'2019-06-01'),

    ('master', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\mastlog.ldf', 3 ,'2019-05-01'),

    ('master', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\mastlog.ldf', 2 ,'2019-04-01'),

    ('master', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\mastlog.ldf', 2 ,'2019-03-27'),

    ('model', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\model.mdf', 8 ,'2019-11-01'),

    ('model', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\model.mdf', 8 ,'2019-10-01'),

    ('model', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\model.mdf', 8 ,'2019-09-01'),

    ('model', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\model.mdf', 8 ,'2019-08-29'),

    ('model', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\model.mdf', 8 ,'2019-06-01'),

    ('model', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\model.mdf', 8 ,'2019-05-01'),

    ('model', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\model.mdf', 8 ,'2019-04-01'),

    ('model', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\model.mdf', 8 ,'2019-03-27'),

    ('model', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\modellog.ldf', 8 ,'2019-11-01'),

    ('model', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\modellog.ldf', 8 ,'2019-10-01'),

    ('model', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\modellog.ldf', 8 ,'2019-09-01'),

    ('model', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\modellog.ldf', 8 ,'2019-08-29'),

    ('model', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\modellog.ldf', 8 ,'2019-06-01'),

    ('model', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\modellog.ldf', 8 ,'2019-05-01'),

    ('model', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\modellog.ldf', 8 ,'2019-04-01'),

    ('model', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\modellog.ldf', 8 ,'2019-03-27'),

    ('msdb', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\MSDBData.mdf', 162 ,'2019-11-01'),

    ('msdb', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\MSDBData.mdf', 134 ,'2019-10-01'),

    ('msdb', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\MSDBData.mdf', 122 ,'2019-09-01'),

    ('msdb', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\MSDBData.mdf', 122 ,'2019-08-29'),

    ('msdb', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\MSDBData.mdf', 56 ,'2019-06-01'),

    ('msdb', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\MSDBData.mdf', 38 ,'2019-05-01'),

    ('msdb', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\MSDBData.mdf', 17 ,'2019-04-01'),

    ('msdb', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\MSDBData.mdf', 17 ,'2019-03-27'),

    ('msdb', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\MSDBLog.ldf', 28 ,'2019-11-01'),

    ('msdb', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\MSDBLog.ldf', 28 ,'2019-10-01'),

    ('msdb', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\MSDBLog.ldf', 28 ,'2019-09-01'),

    ('msdb', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\MSDBLog.ldf', 28 ,'2019-08-29'),

    ('msdb', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\MSDBLog.ldf', 28 ,'2019-06-01'),

    ('msdb', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\MSDBLog.ldf', 28 ,'2019-05-01'),

    ('msdb', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\MSDBLog.ldf', 28 ,'2019-04-01'),

    ('msdb', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\MSDBLog.ldf', 28 ,'2019-03-27'),

    ('tempdb', 'F:\SQLtempDB2016\tempdb.mdf', 512 ,'2019-11-01'),

    ('tempdb', 'F:\SQLtempDB2016\tempdb.mdf', 512 ,'2019-10-01'),

    ('tempdb', 'F:\SQLtempDB2016\tempdb.mdf', 512 ,'2019-09-01'),

    ('tempdb', 'F:\SQLtempDB2016\tempdb.mdf', 512 ,'2019-08-29'),

    ('tempdb', 'F:\SQLtempDB2016\tempdb.mdf', 512 ,'2019-06-01'),

    ('tempdb', 'F:\SQLtempDB2016\tempdb.mdf', 512 ,'2019-05-01'),

    ('tempdb', 'F:\SQLtempDB2016\tempdb.mdf', 512 ,'2019-04-01'),

    ('tempdb', 'F:\SQLtempDB2016\tempdb.mdf', 512 ,'2019-03-27'),

    ('tempdb', 'F:\SQLtempDB2016\tempdb2.ndf', 512 ,'2019-11-01'),

    ('tempdb', 'F:\SQLtempDB2016\tempdb2.ndf', 512 ,'2019-10-01'),

    ('tempdb', 'F:\SQLtempDB2016\tempdb2.ndf', 512 ,'2019-09-01'),

    ('tempdb', 'F:\SQLtempDB2016\tempdb2.ndf', 512 ,'2019-08-29'),

    ('tempdb', 'F:\SQLtempDB2016\tempdb2.ndf', 512 ,'2019-06-01'),

    ('tempdb', 'F:\SQLtempDB2016\tempdb2.ndf', 512 ,'2019-05-01'),

    ('tempdb', 'F:\SQLtempDB2016\tempdb2.ndf', 512 ,'2019-04-01'),

    ('tempdb', 'F:\SQLtempDB2016\tempdb2.ndf', 512 ,'2019-03-27'),

    ('tempdb', 'G:\SQLTEMPLOGS2016\tempdb.ldf', 128 ,'2019-11-01'),

    ('tempdb', 'G:\SQLTEMPLOGS2016\tempdb.ldf', 128 ,'2019-10-01'),

    ('tempdb', 'G:\SQLTEMPLOGS2016\tempdb.ldf', 128 ,'2019-09-01'),

    ('tempdb', 'G:\SQLTEMPLOGS2016\tempdb.ldf', 128 ,'2019-08-29'),

    ('tempdb', 'G:\SQLTEMPLOGS2016\tempdb.ldf', 128 ,'2019-06-01'),

    ('tempdb', 'G:\SQLTEMPLOGS2016\tempdb.ldf', 128 ,'2019-05-01'),

    ('tempdb', 'G:\SQLTEMPLOGS2016\tempdb.ldf', 128 ,'2019-04-01'),

    ('tempdb', 'G:\SQLTEMPLOGS2016\tempdb.ldf', 128 ,'2019-03-27')

    ------------ 20191129 QUERY THAT SAMPLE DATA FOR RESULTS THAT SHOW DB GROWTH DATES, AND AMOUNTS

    ;WITH a AS (

    SELECT

    -- RowNmr = ROW_NUMBER() OVER (PARTITION BY FileNamePath ORDER BY CrtDateTime),

    DatabaseName,

    FileNamePath,

    PreviousSize = LAG(TotalSize_in_MB) OVER (PARTITION BY FileNamePath ORDER BY CrtDate),

    TotalSize_in_MB,

    CrtDate

    FROM #SQL_DbGrowthTracking

    WHERE CrtDate > GETDATE()-280

    )

    SELECT

    FirstDate = (SELECT MIN(CONVERT(DATE, CrtDate)) FROM #SQL_DbGrowthTracking WITH (NOLOCK)),

    -- a.RowNmr,

    a.DatabaseName,

    a.FileNamePath,

    -- a.PreviousSize,

    a.TotalSize_in_MB,

    FileGrowthInMB = CASE WHEN a.TotalSize_in_MB > a.PreviousSize THEN a.TotalSize_in_MB - a.PreviousSize

    ELSE a.PreviousSize - a.TotalSize_in_MB

    END,

    a.CrtDate

    FROM a

    WHERE TotalSize_in_MB <> PreviousSize

    ORDER BY

    FileNamePath,

    CrtDate DESC

    --

     

  • Site Owners

    SSC Guru

    Points: 80379

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • pietlinden

    SSC Guru

    Points: 62611

    WHy are you doing this part?

    FileGrowthInMB = CASE WHEN a.TotalSize_in_MB > a.PreviousSize THEN a.TotalSize_in_MB - a.PreviousSizeELSE a.PreviousSize - a.TotalSize_in_MB

    You're reporting the size change as growth whether or not it's actually growth. Say for the sake of argument, you use SHRINKDB (I'm not saying it's a good idea, but sometimes it's necessary), the file size of your shrunk database should decrease, not increase. I don't see the need for the CASE statement at all. Why not just do the simple subtraction, TotalSize_in_MB - PreviousSize?

  • Budd

    Hall of Fame

    Points: 3659

    This is a DEV server and sometimes DBs are recreated from TFS.

    You are correct though.  My initial thought was to capture the change in size as a positive value, but it make much more sense to capture a decrease as a negative value, other wise I would need to add another column or add more code to change the result to varchar and identify whether it was growth or reduced.  That is not needed...

    Thanks.

  • ScottPletcher

    SSC Guru

    Points: 98206

    A few quick thoughts (numbered to make discussion of them easier):

    (1) You need to normalize this design, you're storing huge amounts of duplicate data.  Specifically, encode the database name with your own permanent id (not the system database_id, because that can change).  Also, encode the the path name and file name; yes, I'd at least split out the actual file name from the path.  Note that you can use a single table to store all the strings, since the id is meaningless except as a code for the string.  You can use a view to pre-code the lookups so every query doesn't have to specify them.

    (2) For best overall performance, you'll want to cluster the table first on Cr[ea]t[e]Date; in this case, I suggest adding an identity column to make the clustering key unique.

    (3) I also suggest using datetime and not just date for when the size is captured.  This prevents misleading results.  It also allows multiple captures per day, in case you want to do that later, or in case at some point you have a file that is growing unexpectedly for some reason, and you want to narrow the time window when it grows.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.

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

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