question

Bobsql-8788 avatar image
0 Votes"
Bobsql-8788 asked Samanthar-3682 commented

db growth tsql help

Hi All,

I am looking for some tsql help. I am from Oracle background. Not good at programming.
Got this query from mssqltips resources which gives me database growth in last 12 months. I am getting desired output, however want an extra output column which shows the growth in percentage.

  • query
    ;

WITH BackupsSize AS(
SELECT TOP 1000
rn = ROW_NUMBER() OVER (ORDER BY DATEPART(year,[backup_start_date]) ASC, DATEPART(month,[backup_start_date]) ASC)
, [Year] = DATEPART(year,[backup_start_date])
, [Month] = DATEPART(month,[backup_start_date])
, [Month Name] = DATENAME(month,[backup_start_date])
, [Backup Size GB] = CONVERT(DECIMAL(10,2),ROUND(AVG([backup_size]/1024/1024/1024),4))
, [Compressed Backup Size GB] = CONVERT(DECIMAL(10,2),ROUND(AVG([compressed_backup_size]/1024/1024/1024),4))
FROM
msdb.dbo.backupset
WHERE
[database_name] = N'dbname'
AND [type] = 'D'
AND backup_start_date BETWEEN DATEADD(mm, - 11, GETDATE()) AND GETDATE()
GROUP BY
[database_name]
, DATEPART(yyyy,[backup_start_date])
, DATEPART(mm, [backup_start_date])
,DATENAME(month,[backup_start_date])
ORDER BY [Year],[Month])

SELECT
b.Year,
b.Month,
b.[Month Name],
b.[Backup Size GB],
0 AS deltaNormal,
b.[Compressed Backup Size GB],
0 AS deltaCompressed
FROM BackupsSize b
WHERE b.rn = 1
UNION
SELECT
b.Year,
b.Month,
b.[Month Name],
b.[Backup Size GB],
b.[Backup Size GB] - d.[Backup Size GB] AS deltaNormal,
b.[Compressed Backup Size GB],
b.[Compressed Backup Size GB] - d.[Compressed Backup Size GB] AS deltaCompressed
FROM BackupsSize b
CROSS APPLY (
SELECT bs.[Backup Size GB],bs.[Compressed Backup Size GB]
FROM BackupsSize bs
WHERE bs.rn = b.rn - 1
) AS d
order by [Year],[Month]
go
-- its gives us output as below

203545-dbsize.jpg


sql-server-transact-sql
dbsize.jpg (49.7 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


Do you need a column like this: b.[Backup Size GB] / d.[Backup Size GB] * 100 AS [percent]?


1 Vote 1 ·
BertZhoumsft-7490 avatar image
2 Votes"
BertZhoumsft-7490 answered Samanthar-3682 commented

Hi,@Bobsql-8788

Welcome to Microsoft T-SQL Q&A Forum!

I agree with Viorel's approach , here you need to be clear about which column to calculate the percentage , I guess you want to calculate the ratio of compressed data compared to before backup , something like this (2021-7): -598.43 /3022.37=-19%.

If you want to compare the percentage before and after compression , you can use the lag function, use

 percent=(deltaCompressed-LAG(deltaCompressed)over(order by Month asc)/LAG(deltaCompressed)over(order by month asc))*100

About LAG can refer to this link . You might try this, as a hint that it is untested:

  WITH BackupsSize AS(
  SELECT TOP 1000
  rn = ROW_NUMBER() OVER (ORDER BY DATEPART(year,[backup_start_date]) ASC, DATEPART(month,[backup_start_date]) ASC)
  , [Year] = DATEPART(year,[backup_start_date])
  , [Month] = DATEPART(month,[backup_start_date])
  , [Month Name] = DATENAME(month,[backup_start_date])
  , [Backup Size GB] = CONVERT(DECIMAL(10,2),ROUND(AVG([backup_size]/1024/1024/1024),4))
  , [Compressed Backup Size GB] = CONVERT(DECIMAL(10,2),ROUND(AVG([compressed_backup_size]/1024/1024/1024),4))
  FROM
  msdb.dbo.backupset
  WHERE
  [database_name] = N'dbname'
  AND [type] = 'D'
  AND backup_start_date BETWEEN DATEADD(mm, - 11, GETDATE()) AND GETDATE()
  GROUP BY
  [database_name]
  , DATEPART(yyyy,[backup_start_date])
  , DATEPART(mm, [backup_start_date])
  ,DATENAME(month,[backup_start_date])
  )
  ,cte1 as
  (
  SELECT
  b.Year,
  b.Month,
  b.[Month Name],
  b.[Backup Size GB],
  0 AS deltaNormal,
  b.[Compressed Backup Size GB],
  0 AS deltaCompressed
  FROM BackupsSize b
  WHERE b.rn = 1
  UNION
  SELECT
  b.Year,
  b.Month,
  b.[Month Name],
  b.[Backup Size GB],
  b.[Backup Size GB] - d.[Backup Size GB] AS deltaNormal,
  b.[Compressed Backup Size GB],
  b.[Compressed Backup Size GB] - d.[Compressed Backup Size GB] AS deltaCompressed
  FROM BackupsSize b
  CROSS APPLY (
  SELECT bs.[Backup Size GB],bs.[Compressed Backup Size GB]
  FROM BackupsSize bs
  WHERE bs.rn = b.rn - 1
  ) AS d
  ),cte2 as
  (
   select Year,Month,[Month Name],[Backup Size GB],deltaNormal,[Compressed Backup Size GB]
        , deltaCompressed, 
      case when  deltaCompressed=0 then 0
   else (deltaCompressed-LAG(deltaCompressed)over(order by Month )/LAG(deltaCompressed)over(order by month ))*100 
   end as growth
   from cte1
  )
  select * from cte2
  order by [Year],[Month]

Best regards,
Bert Zhou


If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


· 7
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I want to consider "Backup Size GB" and "DeltaNormal" columns and calculate percentage.
I know the formula (difference/old_or_prev_val)*100.0 but I am stuck I am stuck how to compare the previous row value.
How to embed this formula in above query?

0 Votes 0 ·

I have updated the answer, please check it out.
Bert Zhou

0 Votes 0 ·
Bobsql-8788 avatar image Bobsql-8788 BertZhoumsft-7490 ·

Hi Bert,

Need a small favor. In month of January, there is no change in db size , so difference is 0. so, while executing the query , I am getting "Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered."

Is there a way to handle inside the query?

0 Votes 0 ·
Show more comments

Thank you Bert. Thanks a lot for the help.

Regards,
Bob

0 Votes 0 ·

You are welcome.)

0 Votes 0 ·
OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered

You get the actual absolute "Backup Size GB" and the "deltaNormal" = difference to previous; should be an easy calculation to get the percentage; isn't it?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.