USE [msdb]
;WITH [BackupSize] AS (
SELECT [bs].[database_name] [Name], DATEDIFF(DD, DATEADD(DD, -45, GETDATE()), [bs].[backup_start_date]) [Date], SUM([bs].[backup_size])/1024/1024/1024 [Size]
FROM [backupmediafamily] [bmf]
INNER JOIN [backupset] [bs] ON [bmf].[media_set_id] = [bs].[media_set_id]
WHERE [bs].[backup_start_date] >= DATEADD(DD, -45, GETDATE())
AND [bs].[type] = 'D'
GROUP BY [bs].[database_name], DATEDIFF(DD, DATEADD(DD, -45, GETDATE()), [bs].[backup_start_date]))
SELECT [Name], [Date], [Size],
PERCENTILE_CONT(0.25) WITHIN GROUP(ORDER BY [Size]) OVER(PARTITION BY [Name]) [Q1],
PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY [Size]) OVER(PARTITION BY [Name]) [Q2],
PERCENTILE_CONT(0.75) WITHIN GROUP(ORDER BY [Size]) OVER(PARTITION BY [Name]) [Q3]
INTO #Info FROM [BackupSize]
UPDATE #Info SET [Size] = [Q2] WHERE [Size] < 2*[Q1]-[Q3] OR [Size] > 2*[Q3]-[Q1]
;WITH [slope] AS (
SELECT [Name], MAX([DateAvg]) [DateAvg], MAX([SizeAvg]) [SizeAvg],
CASE WHEN SUM(([Date]-[DateAvg])*([Date]-[DateAvg]))=0 THEN 0 ELSE SUM(([Date]-[DateAvg])*([Size]-[SizeAvg]))/SUM(([Date]-[DateAvg])*([Date]-[DateAvg])) END [m]
FROM (
SELECT [Name], [Date], AVG([Date]) OVER(PARTITION BY [Name]) [DateAvg], [Size], AVG([Size]) OVER(PARTITION BY [Name]) [SizeAvg]
FROM #Info) x
GROUP BY [Name]),
[lr] AS (SELECT [Name], [m], [SizeAvg]-[DateAvg]*[m] [b] FROM [slope])
SELECT [Name], GEOMETRY::STGeomFromText('LINESTRING(0 '+CAST([b] AS VARCHAR(53))+',90 '+CAST([m]*90+[b] AS VARCHAR(53))+')',0) [Geom]
FROM [lr] GROUP BY [Name], [m], [b]
UNION ALL
SELECT [Name], GEOMETRY::STGeomFromText('LINESTRING('+STRING_AGG(CONCAT([Date],' ',[Size]),',') WITHIN GROUP(ORDER BY [Date])+')',0)
FROM #Info GROUP BY [Name]
DROP TABLE #Info |
