select distinct @@SERVERNAME,volume_mount_point,cast(available_bytes/1024.0/1024.0/1024.0 as int) FreeSpace, cast(total_bytes/1024.0/1024.0/1024.0 as int) TotalSpace from sys.master_files a cross apply sys.dm_os_volume_stats(a.database_id,a.file_id)
该命令只能查看有数据库文件的磁盘,对于没有数据库文件的磁盘是看不到的..
下面是数据库所在磁盘空间
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'
if object_id('tempdb..#tmp_filesize') is not null drop table #tmp_filesize GO create table #tmp_filesize ( server_name varchar(256), database_name varchar(256), file_group varchar(256), logical_name varchar(256), physical_name varchar(1024), type_desc varchar(128), used_size_mb float, allocated_size_mb float, max_size_mb float, disk_free_mb float, free_space_percent float, growth int, is_percent_growth int ) GO exec sp_msforeachdb 'use [?] insert into #tmp_filesize 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''' select * from #tmp_filesize