SQL Script Database size Disk space

Check SQL DB size and free disk space

Posted by John Liu on Thursday, October 27, 2022

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 availability availability-sets availability-zones aws-vm azure azure-automation-runbook azure-blob azure-cosmos-db azure-data-lake azure-deployment azure-function-app azure-functions azure-openai azure-sign-in azure-site-recovery azure-sql-database azure-subscription azure-vm base64 certificate change-data-capture change-tracking chrome clr container cte data-api-builder data-conversion data-gateway database-mail database-role database-size date-table dax db-config derived-table diagram direct-query disk-management disk-space docker downtime dtc dynamic-m-parameter embedding encrypted-connection excel excel-online excel-online-for-business execution-plan extended-events external-data fabric fabric-capacity failover-cluster fk geometry hierarchy httpwebrequest hugo hyper-v incognito-mode index infrastructure inline-tvf json kql lakehouse linked-server live-query-statistics locking m machine-learning machine-learning-model machine-learning-services master-key mcp mdx memory memory-grant mermaid mirrored-sql-server network network-card network-category office-script onedrive onnx-runtime openrowset p2v parquet performance polybase power-automate power-bi power-bi-report-tricks power-platform power-query powershell printer public-ip-address pyspark python qgis qt-designer query-performance query-plan query-troubleshooting r regex replication route s3 schema-design scripting self-signed-certificate server-role sharepoint snowflake software-development sofware-development spark sql sql-2025 sql-agent sql-availability-group sql-error sql-failover-cluster-instance sql-index sql-openjson sql-permission sql-recovery sql-script sql-security sql-server sql-server-admin sql-server-config sql-statistics ssis ssisdb ssl ssl/tls-error ssms table-expression tempdb terraform tips troubleshooting unicode view visual-studio visual-studio-code vmware wait-statistics wi-fi-connection-issue windows-settings