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 function app azure openai azure sign-in azure site recovery azure sql database azure sql db azure subscription azure vm base64 certificate change data capture change tracking chrome clr container cte data api builder data conversion data gateway 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 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 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 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 tips troubleshooting unicode view visual studio visual studio code vmware wait statistics wi-fi connection issue windows settings