????xp_cmdshell??????в??????????У???ν????fsutil volume diskfree?????26??????????????????飬???????????wmic????Щ?????￡?
????EXEC xp_cmdshell 'wmic logicaldisk get caption??freespace??size';
????(2) ????洢????xp_fixeddrives
--exec xp_fixeddrives
IF object_id('tempdb..#drivefreespace') IS NOT NULL
DROP TABLE #drivefreespace
CREATE TABLE #drivefreespace(Drive CHAR(1)?? FreeMb bigint)
INSERT #drivefreespace EXEC ('exec xp_fixeddrives')
SELECT * FROM #drivefreespace
Drive
FreeMb
C
9316
D
180013
???????????????????????????????????洢????????????????????д???????
????(3) DMV/DMF: sys.dm_os_volume_stats
SELECT DISTINCT
@@SERVERNAME as [server]
??volume_mount_point as drive
??cast(available_bytes/ 1024.0 / 1024.0 / 1024.0 AS INT) as free_gb
??cast(total_bytes / 1024.0 / 1024.0 / 1024.0 AS INT) as total_gb
FROM sys.master_files AS f
CROSS APPLY sys.dm_os_volume_stats(f.database_id?? f.file_id)
ORDER BY @@SERVERNAME?? volume_mount_point
server
drive
free_gb
total_gb
…
C:
9
59
…
D:
175
237
??????SQL Server 2008 R2 SP1??????????????????DMF: sys.dm_os_volume_stats?????????xp_fixeddrives??д???????????
????????????????????????????????κ???????????????????????????xp_fixeddrives???д????????
??????. ??????????
????1. ??????????
????(1) ??????????????С(???????)??????????￡?
select @@SERVERNAME as server_name
??DB_NAME() as database_name
??case when data_space_id = 0 then 'LOG'
else FILEGROUP_NAME(data_space_id)
end as file_group
??name as logical_name
??physical_name
??type_desc
??FILEPROPERTY(name??'SpaceUsed')/128.0 as used_size_Mb
??size/128.0 as allocated_size_mb
??case when max_size = -1 then max_size
else max_size/128.0
end as max_size_Mb
??growth
??is_percent_growth
from sys.database_files
where state_desc = 'ONLINE'
????(2) ????????????п????????￡?
select @@SERVERNAME as server_name
??DB_NAME() as database_name
??case when data_space_id = 0 then 'LOG'
else FILEGROUP_NAME(data_space_id)
end as file_group
??name as logical_name
??physical_name
??type_desc
??FILEPROPERTY(name??'SpaceUsed')/128.0 as used_size_mb
??size/128.0 as allocated_size_mb
??case when max_size = -1 then max_size
else max_size/128.0
end as max_size_mb
??vs.available_bytes/1024.0/1024 as disk_free_mb
??growth
??CAST(is_percent_growth as int) as is_percent_growth
from sys.database_files df
cross apply sys.dm_os_volume_stats(DB_ID()??df.file_id) vs
where state_desc = 'ONLINE'
?????????SQL Server 2008 SP1?????汾??????xp_fixeddrives?????????п????????й?????
????(3) ?????????????????????????????????????????????????????￡?
select @@SERVERNAME as server_name
??DB_NAME() as database_name
??case when data_space_id = 0 then 'LOG'
else FILEGROUP_NAME(data_space_id)
end as file_group
??name as logical_name
??physical_name
??type_desc
??FILEPROPERTY(name??'SpaceUsed')/128.0 as used_size_mb
??size/128.0 as allocated_size_mb
??case when max_size = -1 then max_size
else max_size/128.0
end as max_size_mb
??vs.available_bytes/1024.0/1024 as disk_free_mb
??case when growth = 0 then  (size - FILEPROPERTY(name??'SpaceUsed'))*1.0/size
when growth > 0 and max_size = -1 then ((size/128.0 + vs.available_bytes/1024.0/1024) - FILEPROPERTY(name??'SpaceUsed')/128.0)/(size/128.0 + vs.available_bytes/1024.0/1024)
when growth > 0 and max_size <> -1 and (max_size/128.0 - vs.available_bytes/1024.0/1024) >= 0 then ((size/128.0 + vs.available_bytes/1024.0/1024) - FILEPROPERTY(name??'SpaceUsed')/128.0)/(size/128.0 + vs.available_bytes/1024.0/1024)
when growth > 0 and max_size <> -1 and (max_size/128.0 - vs.available_bytes/1024.0/1024) <  0 then (max_size - FILEPROPERTY(name??'SpaceUsed'))*1.0/max_size
else null
end as free_space_percent
??growth
??CAST(is_percent_growth as int) as is_percent_growth
from sys.database_files df
cross apply sys.dm_os_volume_stats(DB_ID()??df.file_id) vs
where state_desc = 'ONLINE'