SQL Server Database Growth Rates

Script to find the Growth Size for All files in All Databases:

SELECT   'Database Name' = DB_NAME(database_id)
,'FileName' = NAME
,FILE_ID
,'size' = CONVERT(NVARCHAR(15), CONVERT(BIGINT, size) * 8) + N' KB'
,'maxsize' = (CASE max_size 
WHEN - 1 THEN N'Unlimited'
ELSE CONVERT(NVARCHAR(15), CONVERT(BIGINT, max_size) * 8) + N' KB'
END)
,'growth' = (CASE is_percent_growth
WHEN 1 THEN CONVERT(NVARCHAR(15), growth) + N'%'
ELSE CONVERT(NVARCHAR(15), CONVERT(BIGINT, growth) * 8) + N' KB'
END)
,'type_desc' = type_desc
FROM sys.master_files
ORDER BY database_id

SQL Server catalog stores informations about every single database backup in msdb..backupset.
If you don’t have other mechanism to collect historical database size then this can be achieve by calculating Backup size from msdb..backupset table. This is the best step to start for a capacity planning through Backup history.
Following points to remember before starting:
– msdb..backupset stores historical informations about backup size NOT database file size. This is good for you if you need to understand how your real stored data are growing day by day but obviously datafiles are typically larger: there is empty space inside for future data.
– Every full database backup contains a little part of logs used for recover. For this reason the size reported is not always exactly your data dimension but usually this is not relevant.

Script to find out current and previous size (in megabytes), as well as Increased_Size, for all databases:

SELECT
s.[database_name]
,s.[backup_start_date]
,CAST( ( s.[backup_size] / 1024 / 1024 ) AS INT ) AS [BackupSize(MB)]
,CAST( ( LAG( s.[backup_size] ) 
OVER (PARTITION BY s.[database_name] ORDER BY s.[backup_start_date])/1024/1024) AS INT ) 
AS [Previous_BackupSize(MB)]
,(CAST( ( s.[backup_size] / 1024 / 1024 ) AS INT )-CAST( ( LAG( s.[backup_size] ) 
OVER ( PARTITION BY s.[database_name] ORDER BY s.[backup_start_date] ) / 1024 / 1024 ) AS INT ))
AS [Increased_Size(MB)]
FROM [msdb]..[backupset] s
WHERE s.[type] = 'D' 
ORDER BY s.[database_name],s.[backup_start_date];
GO

To calculate the same for individual database and also by the no. of days:

Declare @dbname nvarchar(1024)    
Declare @days int              
          
--Configure HERE database name  
set @dbname ='YourDB_Name'  
--and number of days to analyse  
set @days   =365;  
  
--Daily Report  
WITH TempTable(Row,database_name,backup_start_date,Mb)   
as   
( SELECT   
ROW_NUMBER() OVER(order by backup_start_date) as Row, 
database_name,
backup_start_date,
cast(backup_size/1024/1024 as decimal(10,2)) MB
from msdb..backupset
where
type='D' and
database_name=@dbname and
backup_start_date>getdate()-@days )
select
A.database_name,
A.backup_start_date,
A.Mb as daily_backup
A.Mb - B.Mb as increment_mb
from TempTable A left join TempTable B on A.Row=B.Row+1  
order by database_name,backup_start_date