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
| SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO
;WITH
CTE AS
(
SELECT DB_ID() AS database_id
, DS.name AS filegroup_name
, DF.file_id
, DF.name AS logical_name
, DF.type_desc AS file_type_desc
, CASE DF.is_percent_growth
WHEN 0 THEN CASE DF.growth WHEN 0 THEN 'DISABLED' ELSE CAST(DF.growth / 128 AS varchar(20)) + ' MB' END
ELSE CAST(DF.growth AS varchar(3)) + ' %'
END AS growth
, CAST(DF.size / 128.0 AS decimal(14, 2)) AS file_size_MB
, CAST(FILEPROPERTY(DF.name, 'SpaceUsed') / 128.0 AS decimal(14, 2)) AS occupied_size_MB
, CASE DF.max_size
WHEN -1 THEN 'UNLIMITED'
WHEN 0 THEN 'DISABLED'
ELSE CAST(CAST(DF.max_size / 128.0 AS bigint) AS varchar(20))
END AS max_size_MB
FROM sys.database_files AS DF
LEFT JOIN sys.data_spaces AS DS
ON DF.data_space_id = DS.data_space_id
)
SELECT COALESCE(C.filegroup_name, 'TRANSACTION_LOG') AS filegroup_name
, C.logical_name
, C.file_type_desc
, C.growth
, C.max_size_MB
, C.file_size_MB
, C.occupied_size_MB
, C.file_size_MB - C.occupied_size_MB AS free_space_MB
, CAST((CAST(C.occupied_size_MB AS numeric(14, 2)) / C.file_size_MB) * 100 AS numeric(14, 2)) AS [%occupied]
, CAST((CAST(C.file_size_MB - C.occupied_size_MB AS numeric(14, 2)) / C.file_size_MB) * 100 AS numeric(14, 2)) AS [%free]
FROM CTE AS C
ORDER BY logical_name |
