Script to get data file size, used space and free space

While troubleshooting the disk space issue, you can use below script to check the data file size, used space and free size.

select 
		DBName,
		name,
		[filename],
		size as 'Size(MB)',
		usedspace as 'UsedSpace(MB)',
		(size - usedspace) as 'AvailableFreeSpace(MB)'
from		
(	
SELECT
db_name(s.database_id) as DBName,
s.name AS [Name],
s.physical_name AS [FileName],
(s.size * CONVERT(float,8))/1024 AS [Size],
(CAST(CASE s.type WHEN 2 THEN 0 ELSE CAST(FILEPROPERTY(s.name, 'SpaceUsed') AS float)* CONVERT(float,8) END AS float))/1024 AS [UsedSpace],
s.file_id AS [ID]
FROM
sys.filegroups AS g
INNER JOIN sys.master_files AS s ON ((s.type = 2 or s.type = 0) and s.database_id = db_id() and (s.drop_lsn IS NULL)) AND (s.data_space_id=g.data_space_id)
) DBFileSizeInfo

5 thoughts on “Script to get data file size, used space and free space”

VV February 3, 2016 at 9:51 pm

If i have 3 differents database in a same server…??? I tried to run this scrpt per instances?

Reply ↓

Greg February 16, 2016 at 4:55 am

…The select should have capital letters for the variables to match the names in brackets.
select
DBName,
Name,
[Filename],
Size as ‘Size(MB)’,
Usedspace as ‘UsedSpace(MB)’,
(Size – Usedspace) as ‘AvailableFreeSpace(MB)’
from
(
SELECT
db_name(s.database_id) as DBName,
s.name AS [Name],
s.physical_name AS [FileName],
(s.size * CONVERT(float,8))/1024 AS [Size],
(CAST(CASE s.type WHEN 2 THEN 0 ELSE CAST(FILEPROPERTY(s.name, ‘SpaceUsed’) AS float)* CONVERT(float,8) END AS float))/1024 AS [UsedSpace],
s.file_id AS [ID]
FROM
sys.filegroups AS g
INNER JOIN sys.master_files AS s ON ((s.type = 2 or s.type = 0) and s.database_id = db_id() and (s.drop_lsn IS NULL)) AND (s.data_space_id=g.data_space_id)
) DBFileSizeInfo

Reply ↓

Aijaz Ali April 1, 2016 at 2:50 pm

Good one Jugal! I still need to use this query for my SQL 2000 databases. This is also handy if for some reason you are not able to see the Disk Usage reports

Reply ↓