1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
| WITH diskspace AS
(SELECT volume_mount_point AS Volume
, CAST(MIN(VolumeTotalMo) AS DECIMAL(18, 2)) AS [Volume Space]
, CAST(SUM(FileSizeMo) AS DECIMAL(18, 2)) AS [DB Files]
, CAST(MIN(VolumeTotalMo)-SUM(FileSizeMo)-MIN(VolumeAvailableMo) AS DECIMAL(18,2)) AS [Other Files]
, CAST(MIN(VolumeAvailableMo) AS DECIMAL(18, 2)) AS [Free Space]
, CAST(SUM(FileGrowthMo) AS DECIMAL(18, 2)) AS [Space for DB growth]
, CAST(MIN(VolumeAvailableMo) - SUM(FileGrowthMo) AS DECIMAL(18, 2)) AS [Free space after growth]
, CAST(MIN(VolumeAvailableMo) / MIN(VolumeTotalMo) * 100 AS DECIMAL(18, 2)) AS [Percent free space]
, CAST(( MIN(VolumeAvailableMo) - SUM(FileGrowthMo) )
/ MIN(VolumeTotalMo) * 100 AS DECIMAL(18, 2)) AS [Percent free space after growth]
, CASE WHEN CAST(( MIN(VolumeAvailableMo) - SUM(FileGrowthMo) )
/ MIN(VolumeTotalMo) * 100 AS DECIMAL(18, 2)) > 10 THEN CAST(0 AS DECIMAL(18,2)) -- 0 green 10% after growth
WHEN CAST(MIN(VolumeAvailableMo) / MIN(VolumeTotalMo) * 100 AS DECIMAL(18, 2)) > 10 THEN CAST(1 AS DECIMAL(18,2)) -- 1 orange 10% available
ELSE CAST(2 AS DECIMAL(18,2)) -- 2 red less than 10%
END AS Threshold
FROM ( SELECT DB_NAME(f.database_id) AS [DatabaseName]
, f.file_id
, CAST(f.size AS FLOAT) * 8 / 1024 AS FileSizeMo
, CASE WHEN f.is_percent_growth = 1
THEN ( CAST(f.size AS FLOAT) * f.growth / 100 ) * 8
/ 1024
ELSE CAST(f.growth AS FLOAT) * 8 / 1024
END AS FileGrowthMo
, vs.volume_mount_point
, CAST(vs.total_bytes AS FLOAT) / 1024 / 1024 AS VolumeTotalMo
, CAST(vs.available_bytes AS FLOAT) / 1024 / 1024 AS VolumeAvailableMo
, ( CAST(vs.available_bytes AS FLOAT)
/ CAST(vs.total_bytes AS FLOAT) ) * 100 AS [Space Free %]
FROM sys.master_files AS f
CROSS APPLY sys.dm_os_volume_stats(f.database_id,
f.file_id) AS vs
) AS FileSizes
GROUP BY volume_mount_point)
SELECT Volume, dstatus,
CASE dstatus WHEN 'DB Files' THEN 2 WHEN 'Other Files' THEN 3 WHEN 'Free Space' THEN 4
WHEN 'Volume Space' THEN 1 WHEN 'Space for DB growth' THEN 5 WHEN 'Free space after growth' THEN 6
WHEN 'Percent free space' THEN 7 WHEN 'Percent free space after growth' THEN 8
WHEN 'Threshold' THEN 9
END AS SortOrder,
value FROM
(SELECT Volume, [Volume Space],[DB Files],[Other Files],[Free Space],[Space for DB growth],[Free space after growth],[Percent free space],[Percent free space after growth],[Threshold] FROM diskspace) p
UNPIVOT
(value FOR dstatus IN ([Volume Space],[DB Files],[Other Files],[Free Space],[Space for DB growth],[Free space after growth],[Percent free space],[Percent free space after growth],[Threshold]) ) AS pv
ORDER BY Volume,SortOrder |
