记录一下:查看磁盘空间 http://www.cnblogs.com/seusoftware/p/3939501.html

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