Check SQL DB size and free disk space-John Liu Blog

Following script will list the db size for all user databases and the free disk space of the driver.

SELECT
	DBName
	,Owner
	,Create_Date
	,DBSizeGB = SUM(DBSizeGB)
	,Drive
	,DriveFreeSpaceGB = MAX(available_bytes)/1024/1024/1024
FROM
	(SELECT
		DBName = D.name
		,Owner = P.name
		,Create_Date = CONVERT(DATE,D.create_date)
		,DBSizeGB = F.size * 8 / 1024 / 1024
		,Drive = LEFT(F.physical_name,2)
		,DR.available_bytes
	FROM
		sys.databases D
		INNER JOIN
			sys.server_principals P
				ON
					D.owner_sid = P.sid
		INNER JOIN
			sys.master_files F
				ON
					D.database_id = F.database_id --AND F.type = 0
		OUTER APPLY
			sys.dm_os_volume_stats(D.database_id,1) DR
	WHERE
		D.name NOT IN ('tempdb','model','master','msdb')
		AND D.name NOT LIKE 'ReportServer$%'
		AND DR.volume_mount_point LIKE LEFT(F.physical_name,2) + '%'
	) R
GROUP BY
	DBName,Owner,create_date,Drive
ORDER BY
	--create_date,SizeGB DESC,Owner
	DBSizeGB DESC

FEATURED TAGS

ai api automation azure azure cosmos db azure openai azure vm base64 dax docker excel execution plan hyper-v infrastructure m machine learning machine learning services network network card performance power automate power bi power bi report tricks power query powershell python qgis query performance regex replication snowflake sql sql script sql server sql server admin sql server config ssl ssms troubleshooting unicode vmware