--DISK SPACE
DROP TABLE #tmp
CREATE TABLE #tmp (COL1 VARCHAR(20),COL2 INT)
INSERT INTO #tmp
EXEC master..xp_fixeddrives
SELECT col1 AS drive, col2/1000 AS free_spce_in_gb FROM #tmp ORDER BY 2
DROP TABLE #output
create table #output (output nvarchar(255) null)
insert #output (output) EXEC master..xp_cmdshell 'PowerShell.exe -noprofile -command "get-WmiObject Win32_LogicalDisk | Format-Table -Property DeviceID,FreeSpace"'
select LEFT(output,3) AS DRIVE, LTRIM(RTRIM(RIGHT(OUTPUT,LEN(OUTPUT)-3))) AS FREE_SPACE from #output where output LIKE '%C:%'
--SERVER SPACE
SELECT DISTINCT
dovs.volume_mount_point AS Drive,
DB_NAME(dovs.database_id) DBName,
dovs.logical_volume_name AS LogicalName,
(CONVERT(INT,dovs.available_bytes/1048576.0) / 1000) AS FreeSpaceInGB
FROM
sys.master_files mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs
ORDER BY
FreeSpaceInGB ASC;
--DB SPACE
SELECT DB_NAME() AS DbName,
name AS FileName,
type_desc,
size/128.0 AS CurrentSizeMB,
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB
FROM sys.database_files
WHERE type IN (0,1);