SQL Server Admin SQL Script

SQL Server File Size

Posted by John Liu on Sunday, March 19, 2023

There are serveral ways to find out database size. Apart from using the SSMS GUI, you can using following scripts options.

--this return size info for all databases
--the size is in kilobytes
exec sp_databases

--this return size info for all databases
SELECT
	DBName = DB_NAME(database_id)
    ,FileName = name
	,physical_name
    ,type_desc
	,Allocated_Space_GB = CONVERT(DECIMAL(18,2),size * 8.0/1024/1024)
	--,max_size
	--,growth
	--,is_percent_growth
FROM
	sys.master_files
WHERE
	database_id > 6
	AND type IN (0,1)

--this return size info for current database with free space info
SELECT
	DBName = DB_NAME()
	,FileName = name
	,physical_name
    ,type_desc
	,Allocated_Space_GB = CONVERT(DECIMAL(18,2),size * 8.0/1024/1024)
	,Free_Space_GB = CONVERT(DECIMAL(18,2),(size - FILEPROPERTY(name,'SpaceUsed')) * 8.0/1024/1024)
	--,max_size
	--,growth
	--,is_percent_growth
FROM
	sys.database_files
WHERE
	type IN (0,1)

--this return size info for user databases with free space info
EXEC sp_MSforeachdb '
USE [?]
IF DB_ID() > 5	--Exclude system databases
SELECT
	DBName = DB_NAME()
	,FileName = name
	,physical_name
	,type
	,Allocated_Space_GB = CONVERT(DECIMAL(18,2),size * 8.0/1024/1024)
	,Free_Space_GB = CONVERT(DECIMAL(18,2),(size - FILEPROPERTY(name,''SpaceUsed'')) * 8.0/1024/1024)
	--,max_size
	--,growth
	--,is_percent_growth
FROM
	sys.database_files
WHERE
	type IN (0,1)
'

--find the used data size of all databases
IF OBJECT_ID('TEMPDB..#Result') IS NOT NULL DROP TABLE #Result
CREATE TABLE #Result(DBID INT,DBName sysname,FileName varchar(100),PhysicalName varchar(100),type varchar(10),Allocated_Space_GB decimal(18,2), Free_Space_GB decimal(18,2))
INSERT INTO #Result
EXEC sp_MSforeachdb '
USE [?]
IF DB_ID() > 5	--Exclude system databases
SELECT
	DB_ID()
	,DBName = DB_NAME()
	,FileName = name
	,physical_name
	,type
	,Allocated_Space_GB = CONVERT(DECIMAL(18,2),size * 8.0/1024/1024)
	,Free_Space_GB = CONVERT(DECIMAL(18,2),(size - FILEPROPERTY(name,''SpaceUsed'')) * 8.0/1024/1024)
	--,max_size
	--,growth
	--,is_percent_growth
FROM
	sys.database_files
WHERE
	type IN (0,1)
'

SELECT
	DBName
	,Data_Size_GB = SUM(Allocated_Space_GB) - SUM(Free_Space_GB)
FROM #Result
WHERE
	DBID > 4
	and type = 0
GROUP BY
	DBName
ORDER BY
	Data_Size_GB 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