SQL Server: Check existing data file size

with fs
as
(
    select database_id, type, size * 8.0 / 1024 size
    from sys.master_files
)
select
    name,
    (select sum(size) from fs where type = 0 and fs.database_id = db.database_id) DataFileSizeMB,
    (select sum(size) from fs where type = 1 and fs.database_id = db.database_id) LogFileSizeMB
from sys.databases db
order by 3 desc

SQL server database growth rate (single DB & more detail)

WITH CTE AS
(
SELECT
DatabaseName = bs.database_name,
backupDate = cast(bs.backup_start_date as date),
DBSizeGB = CONVERT(numeric(10, 1), bf.file_size / 1048576.0 / 1024.0),
--bs.backup_size,
bf.file_size
FROM msdb.dbo.backupset as bs
JOIN msdb.dbo.backupfile as bf ON bs.backup_set_id = bf.backup_set_id
WHERE NOT bs.database_name IN ('master', 'msdb', 'model', 'tempdb')
and bf.file_type = 'D'
and bs.[Type] = 'D'
and bs.database_name in ('ABC')
),
cte_Detail as
(
select
CTE.databaseName,
cte.backupDate,
cte.DBSizeGB,
IncreasedByGB = DBSizeGB - (select top 1 DBSizeGB from cte t2
where t2.backupDate < cte.backupDate
and t2.DatabaseName = cte.DatabaseName
order by t2.backupdate desc),
DaysSince = DateDiff(dd, (select top 1 backupDate from cte t2
where t2.backupDate < cte.backupDate
and t2.DatabaseName = cte.DatabaseName
order by t2.backupdate desc), backupDate)
from CTE
)
select
databaseName,
backupDate,
DBSizeGB,
IncreasedByGB,
DaysSince,
DailyIncrease = cast(round(IncreasedByGB / DaysSince, 2) as decimal(8, 2))
from cte_Detail
order by 2 desc

SQL Server database size growth using backup history

DECLARE @endDate datetime, @months smallint;
SET @endDate = GetDate();
SET @months = 6;          

WITH cte AS
(
SELECT
DatabaseName = bs.database_name,
YearMonth = YEAR(bs.backup_start_date) * 100 + MONTH(bs.backup_start_date),
MinSizeMB = CONVERT(numeric(10, 1), MIN(bf.file_size / 1048576.0)),
MaxSizeMB = CONVERT(numeric(10, 1), MAX(bf.file_size / 1048576.0)),
AvgSizeMB = CONVERT(numeric(10, 1), AVG(bf.file_size / 1048576.0))
FROM msdb.dbo.backupset AS bs
JOIN msdb.dbo.backupfile AS bf ON bs.backup_set_id = bf.backup_set_id
WHERE not bs.database_name IN ('master', 'msdb', 'model', 'tempdb')
and bf.file_type = 'D'
and bs.[Type] = 'D'
and bs.backup_start_date BETWEEN DATEADD(mm, - @months, @endDate) and @endDate
GROUP BY bs.database_name, YEAR(bs.backup_start_date), MONTH(bs.backup_start_date))
SELECT
main.DatabaseName,
main.YearMonth,
main.MinSizeMB,
main.MaxSizeMB,
GrowthMB =
main.AvgSizeMB
- (SELECT TOP 1 sub.AvgSizeMB
FROM cte AS sub
WHERE sub.DatabaseName = main.DatabaseName
and sub.YearMonth < main.YearMonth
ORDER BY sub.YearMonth DESC)
FROM cte AS main
ORDER BY 5 desc, main.DatabaseName, main.YearMonth