Post reply

NEED HELP= Monthly SQL database growth calculation script

  • April 25, 2011 at 11:23 am

    #1315521

    I'm using Chad Miller's Powershell-based solution here for capacity planning.

    http://www.sqlservercentral.com/articles/powershell/68011/

  • April 25, 2011 at 11:53 am

    #1315526

    At a guess, something like this

    WITH cte ([ServerName],[Database Name],[Month],[DB-Size])

    AS (

    SELECT [ServerName],[Database Name],DATEDIFF(month,GETDATE(),[Execution date]) AS [Month],[DB-Size]

    FROM

    WHERE [Execution date] >= DATEADD(month,DATEDIFF(month,0,GETDATE())-12,0)

    AND [Execution date] = DATEADD(month,DATEDIFF(month,0,GETDATE()),0)

    AND DAY([Execution date]) = 1)

    SELECT [ServerName],[Database Name],

    MAX(CASE WHEN [Month]= 0 THEN a.[DB-Size] - b.[DB-Size] ELSE 0 END) AS [0],

    MAX(CASE WHEN [Month]= -1 THEN a.[DB-Size] - b.[DB-Size] ELSE 0 END) AS [-1],

    MAX(CASE WHEN [Month]= -2 THEN a.[DB-Size] - b.[DB-Size] ELSE 0 END) AS [-2],

    MAX(CASE WHEN [Month]= -3 THEN a.[DB-Size] - b.[DB-Size] ELSE 0 END) AS [-3],

    MAX(CASE WHEN [Month]= -4 THEN a.[DB-Size] - b.[DB-Size] ELSE 0 END) AS [-4],

    MAX(CASE WHEN [Month]= -5 THEN a.[DB-Size] - b.[DB-Size] ELSE 0 END) AS [-5],

    MAX(CASE WHEN [Month]= -6 THEN a.[DB-Size] - b.[DB-Size] ELSE 0 END) AS [-6],

    MAX(CASE WHEN [Month]= -7 THEN a.[DB-Size] - b.[DB-Size] ELSE 0 END) AS [-7],

    MAX(CASE WHEN [Month]= -8 THEN a.[DB-Size] - b.[DB-Size] ELSE 0 END) AS [-8],

    MAX(CASE WHEN [Month]= -9 THEN a.[DB-Size] - b.[DB-Size] ELSE 0 END) AS [-9],

    MAX(CASE WHEN [Month]=-10 THEN a.[DB-Size] - b.[DB-Size] ELSE 0 END) AS [-10],

    MAX(CASE WHEN [Month]=-11 THEN a.[DB-Size] - b.[DB-Size] ELSE 0 END) AS [-11]

    FROM cte a

    JOIN cte b ON b.[Month]=a.[Month]-1

    GROUP BY [ServerName],[Database Name]

    ORDER BY [ServerName],[Database Name] ASC

    This will show last 12 months DB file growth, substitute DB-Used for data growth.

    ----------------------

    I ran the above script. I am not getting any information. not even with 0 value.

  • April 25, 2011 at 1:26 pm

    #1315562

    I used this script to get the quarterly growth, but I have to modify to get the monthly growth. Can you please help.

    SELECT

    [SQLName]as ServerName

    ,[DBName]as DatabaseName

    ,FileStatus

    ,[FileType]=case when [FileType]='ROWS' then 'DATA' else 'LOG'end

    ,SUM(a.[Size(MB)])as [CurrentSize(MB)]

    ,[UsedSpace(MB)]

    ,[AvailableSpace(MB)]as [AvailableFreeSpace(MB)]

    ,CONVERT(INTEGER, ((a.[UsedSpace(MB)]*1.0)/(a.[Size(MB)]*1.0))*100.0) AS [Percent Used]

    ,[ExecuteTime]

    ,ISNULL((select B.[UsedSpace(MB)]

    from [TESTDB].[dbo].DB_USAGE B

    where B.[SQLName] = a.[SQLName]

    and B.[DBName] = a.[DBName]

    and DATEPART(yyyy, B.[ExecuteTime]) = DATEPART(yyyy, DATEADD(mm, -3, a.[ExecuteTime]))

    and DATEPART(MM, B.[ExecuteTime]) = DATEPART(MM, DATEADD(mm, -3, a.[ExecuteTime]))),0)/3.0 AS Data_Usage_Growth

    from [TESTDB].[dbo].DB_USAGE a

    where DATEPART(mm, [ExecuteTime]) in (1,4,7,10)and [Size(MB)] is not null

    group by a.[ExecuteTime]

    ,a.[SQLName]

    ,a.[DBName]

    ,FileStatus

    ,a.[FileType],a.[Size(MB)],a.[UsedSpace(MB)]

    ,a.[AvailableSpace(MB)]

    order by a.[DBName], a.[ExecuteTime]

  • April 25, 2011 at 4:38 pm

    #1315647

    Did you actually run my script? It gives you the date you're going to runout of space on EACH drive on the server + the next autogrowth event.

    Also it would be real easy to modify to get the monthly average (I personnally use daily).

  • April 26, 2011 at 4:37 am

    #1315744

    hydbadrose (4/25/2011)

    I used this script to get the quarterly growth, but I have to modify to get the monthly growth. Can you please help.

    Remove the month selection in the WHERE clause and change -3 to -1 in the DATEADD in the correlated query.

    Far away is close at hand in the images of elsewhere.
    Anon.

  • April 26, 2011 at 8:32 am

    #1315911

    How about this statement? we were deviding by 3 to get the quarterly data.

    and DATEPART(MM, B.[ExecuteTime]) = DATEPART(MM, DATEADD(mm, -3, a.[ExecuteTime]))),0)/3.0 AS Data_Usage_Growth

  • April 26, 2011 at 8:51 am

    #1315932

    hydbadrose (4/26/2011)

    How about this statement? we were deviding by 3 to get the quarterly data.

    and DATEPART(MM, B.[ExecuteTime]) = DATEPART(MM, DATEADD(mm, -3, a.[ExecuteTime]))),0)/3.0 AS Data_Usage_Growth

    That would give you an AVERAGE monthly growth not quarterly.

    Remove the divide by 3

    and DATEPART(MM, B.[ExecuteTime]) = DATEPART(MM, DATEADD(mm, -3, a.[ExecuteTime]))),0) AS Data_Usage_Growth

    would give you a quarterly growth

    and DATEPART(MM, B.[ExecuteTime]) = DATEPART(MM, DATEADD(mm, -1, a.[ExecuteTime]))),0) AS Data_Usage_Growth

    will give you a monthly growth

    Personally I would rewrite the query but would require the following

    Create table DDL including indexes

    Samply data to test with (important especially for ExecuteTime, ie does it contain time and only exists once per month?)

    Far away is close at hand in the images of elsewhere.
    Anon.

  • April 26, 2011 at 9:32 am

    #1315961

    Ok so for the following script how would you change in whole;

    ,ISNULL((select B.[UsedSpace(MB)]

    from [TESTDB].[dbo].DB_USAGE B

    where B.[SQLName] = a.[SQLName]

    and B.[DBName] = a.[DBName]

    and DATEPART(yyyy, B.[ExecuteTime]) = DATEPART(yyyy, DATEADD(mm, -3, a.[ExecuteTime]))

    and DATEPART(MM, B.[ExecuteTime]) = DATEPART(MM, DATEADD(mm, -3, a.[ExecuteTime]))),0)/3.0 AS Data_Usage_Growth

  • April 26, 2011 at 9:36 am

    #1315966

    hydbadrose (4/26/2011)

    Ok so for the following script how would you change in whole;

    ,ISNULL((select B.[UsedSpace(MB)]

    from [TESTDB].[dbo].DB_USAGE B

    where B.[SQLName] = a.[SQLName]

    and B.[DBName] = a.[DBName]

    and DATEPART(yyyy, B.[ExecuteTime]) = DATEPART(yyyy, DATEADD(mm, -3, a.[ExecuteTime]))

    and DATEPART(MM, B.[ExecuteTime]) = DATEPART(MM, DATEADD(mm, -3, a.[ExecuteTime]))),0)/3.0 AS Data_Usage_Growth

    Hello, read english? I gave you a full complete solution. USE IT. If it's not what you need, past back and we'll help.

  • April 26, 2011 at 9:41 am

    #1315971

    hydbadrose (4/26/2011)

    Ok so for the following script how would you change in whole;

    As I already stated

  • DDL to create the table (including indexes)
  • Sample data to test with (important especially for ExecuteTime, ie does it contain time and only exists once per month?)

    • without this there is not much we can do

    Far away is close at hand in the images of elsewhere.
    Anon.

  • April 26, 2011 at 10:17 am

    #1316001

    I am sorry for the confusion. I used the following script and I am getting this error with only one database information is displaying instead 6 database including systems db's.

    ,ISNULL((select B.[UsedSpace(MB)]

    from [TESTDB].[dbo].DB_USAGE B

    where B.[SQLName] = a.[SQLName]

    and B.[DBName] = a.[DBName]

    and DATEPART(yyyy, B.[ExecuteTime]) = DATEPART(yyyy, DATEADD(mm, -1, a.[ExecuteTime]))

    and DATEPART(MM, B.[ExecuteTime]) = DATEPART(MM, DATEADD(mm, -1, a.[ExecuteTime]))),0) AS Data_Usage_Growth

    ***subquery returned more than 1 value. This is not permitted when the subquery follows=,!=,<=,>= or when subquery is used as an expression.***

  • April 26, 2011 at 10:20 am

    #1316004

    I asked very early on for your process, and then Dave has asked again in a more clear way for the same info.

    We really need to see the script to create the table you are using to hold this data. We need to see the script you are using to put data into that table. And we need sample data to give you a better solution that any that have been offered thus far.

    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

  • April 26, 2011 at 10:24 am

    #1316008

    Syed Jahanzaib Bin hassan (4/22/2011)

    ...

    Check this script if you have any problem or modification required then tell me

    Regards,

    Syed Jahanzaib Bin Hassan

    MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA

    My Blog

    http://www.aureus-salah.com[/quote%5D

    Here's a modification:

    DECLARE @StartTime DATETIME, @EndTime DATETIME

    SET @StartTime = DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)

    SET @EndTime = DATEADD(dd,1+DATEDIFF(dd,0,GETDATE()),0)

    SELECT

    DBNAME,

    CURRENT_LOG_SIZE = SUM(CASE WHEN TYPE_DESC = 'LOG' AND SIZE_DATE >= @StartTime AND SIZE_DATE < @EndTime THEN FILE_SIZE ELSE 0 END) / 128,

    TOTAL_DATABASE_SIZE = (SUM(CASE WHEN TYPE_DESC = 'LOG' AND SIZE_DATE >= @StartTime AND SIZE_DATE < @EndTime THEN FILE_SIZE ELSE 0 END) / 128)

    + (SUM(CASE WHEN TYPE_DESC = 'ROWS' AND SIZE_DATE >= @StartTime AND SIZE_DATE < @EndTime THEN FILE_SIZE ELSE 0 END) / 128),

    CURRENT_DATA_SIZE = SUM(CASE WHEN TYPE_DESC = 'ROWS' AND SIZE_DATE >= @StartTime AND SIZE_DATE < @EndTime THEN FILE_SIZE ELSE 0 END) / 128,

    LAST_DAY_DATA_SIZE = SUM(CASE WHEN TYPE_DESC = 'ROWS' AND SIZE_DATE >= DATEADD(dd,-1,@StartTime) AND SIZE_DATE < DATEADD(dd,-1,@EndTime) THEN FILE_SIZE ELSE 0 END) / 128,

    PER_DAY_INCREMENT = (SUM(CASE WHEN TYPE_DESC = 'ROWS' AND SIZE_DATE >= @StartTime AND SIZE_DATE < @EndTime THEN FILE_SIZE ELSE 0 END) / 128)

    - (SUM(CASE WHEN TYPE_DESC = 'ROWS' AND SIZE_DATE >= DATEADD(dd,-1,@StartTime) AND SIZE_DATE < DATEADD(dd,-1,@EndTime) THEN FILE_SIZE ELSE 0 END) / 128),

    PER_MONTH_INCREMENT = (SUM(CASE WHEN TYPE_DESC = 'ROWS' AND SIZE_DATE >= DATEADD(dd,-30,@StartTime) AND SIZE_DATE < @EndTime THEN FILE_SIZE ELSE 0 END) / 128)

    - (SUM(CASE WHEN TYPE_DESC = 'ROWS' AND SIZE_DATE >= DATEADD(dd,-60,@StartTime) AND SIZE_DATE < DATEADD(dd,-30,@EndTime) THEN FILE_SIZE ELSE 0 END) / 128)

    FROM DBINFO

    WHERE TYPE_DESC IN ('LOG','ROWS')

    AND SIZE_DATE >= DATEADD(dd,-60,@StartTime)

    GROUP BY DBNAME

    The original version has an uncomfortable proportion of hobby code in it.

    My DBA would have a bigger problem if I saw him using this:

    FROM DBINFO TMP1

    WHERE DBID IN(SELECT DISTINCT DBID FROM DBINFO)

    He'd be returned to HR.

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • April 26, 2011 at 10:27 am

    #1316011

    David Burrows (4/26/2011)

    hydbadrose (4/26/2011)

    Ok so for the following script how would you change in whole;

    As I already stated

  • DDL to create the table (including indexes)
  • Sample data to test with (important especially for ExecuteTime, ie does it contain time and only exists once per month?)

    • without this there is not much we can do

    Stop panicking for 1 second. Get an english interpreter if you need to. READ WHAT WE SAY. Stop focussing on your darn problem and let us help you.

  • April 26, 2011 at 2:49 pm

    #1316242

    I gave up- I am not getting any friendly reply...

    • Viewing 15 posts - 16 through 30 (of 37 total)

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

    Cookies on SQLServerCentral

    This website stores cookies on your computer.

    These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

    To find out more about the cookies we use, see our Privacy Policy