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