Graph multiple databases growth using SSMS only
The script below uses the backup information recorded automatically in msdb database for the last 45 days (no matter what your backup solution is) the same way as described here, then removes outliers using the instructions described here, and projects the storage needs for the next 45 days using the instructions described here, generating spatial results for SSMS to display them. SSMS allows you to select the database name column to put as label.
If you run this as a multi-server query, you can view the largest database needs by instance or by database. Only be cautious when, for whatever reason, your data is not linear.
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
