Space

October 4th, 2016

Disk space

SELECT DISTINCT vs.volume_mount_point, vs.file_system_type, 
vs.logical_volume_name, CONVERT(DECIMAL(18,2),vs.total_bytes/1073741824.0) AS [Total Size (GB)],
CONVERT(DECIMAL(18,2), vs.available_bytes/1073741824.0) AS [Available Size (GB)],  
CONVERT(DECIMAL(18,2), vs.available_bytes * 1. / vs.total_bytes * 100.) AS [Space Free %]
FROM sys.master_files AS f WITH (NOLOCK)
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.[file_id]) AS vs 
ORDER BY vs.volume_mount_point OPTION (RECOMPILE);


Database files (detailed)

SELECT f.name AS [File Name] , f.physical_name AS [Physical Name], 
CAST((f.size/128.0) AS DECIMAL(15,2)) AS [Total Size in MB],
CAST(f.size/128.0 - CAST(FILEPROPERTY(f.name, 'SpaceUsed') AS int)/128.0 AS DECIMAL(15,2)) 
AS [Available Space In MB], 
CAST(f.size/128.0 - CAST(FILEPROPERTY(f.name, 'SpaceUsed') AS int)/128.0 AS DECIMAL(15,2))/CAST((f.size/128.0) AS DECIMAL(15,2))*100 as [Free space in pct],
f.[file_id], fg.name AS [Filegroup Name],
f.is_percent_growth, f.growth, 
fg.is_default, fg.is_read_only
FROM sys.database_files AS f WITH (NOLOCK) 
LEFT OUTER JOIN sys.filegroups AS fg WITH (NOLOCK)
ON f.data_space_id = fg.data_space_id
ORDER BY f.[file_id] OPTION (RECOMPILE);


Total DB space (all files combined)

SELECT cast(SUM(f.size/128.0) AS DECIMAL(15,2)) AS [Total Size in MB],
	CAST(CAST(sum(FILEPROPERTY(f.name, 'SpaceUsed')) as int)/128 AS DECIMAL(15,2)) as [Total space used],
	100-cast(CAST(CAST(sum(FILEPROPERTY(f.name, 'SpaceUsed')) as int)/128 AS DECIMAL(15,2)) / cast(SUM(f.size/128.0) AS DECIMAL(15,2))*100 as decimal(15,2)) as [Total free pct]
FROM sys.database_files AS f WITH (NOLOCK) 
LEFT OUTER JOIN sys.filegroups AS fg WITH (NOLOCK)
ON f.data_space_id = fg.data_space_id
where f.type = 0 OPTION (RECOMPILE);


All instance files (sorted by size)

SELECT
   mf.name, db.name, mf.physical_name, type_desc AS FileType, CAST(mf.size/128.0 AS DECIMAL(15,2)) as file_size_mb
FROM
    sys.master_files mf
INNER JOIN 
    sys.databases db ON db.database_id = mf.database_id
    order by mf.size desc;


Comments are closed.