db growth tsql help

Question

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

Answer 1

Hi，@Bob sql

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.

Answer 2

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?