1. 查詢整個instance的檔案資訊
 
set transaction isolation level read uncommitted
select db_name(f.database_id) [database], f.name, f.physical_name,
    f.type_desc, f.state_desc, f.size*8 [size(KB)], f.growth,
    f.is_media_read_only, f.is_read_only, f.is_percent_growth
from sys.master_files f
go
set transaction isolation level read committed
 
 
2. 查詢當下資料庫的所有檔案大小與使用配置情形(適用SQL 2012以上的環境)
 
set transaction isolation level read uncommitted
select db_name(f.database_id) [資料庫],
    filegroup_name(f.filegroup_id) [檔案群組],
    file_name(f.file_id) [檔案],
    f.total_page_count*8 [檔案大小(kb)],
    f.allocated_extent_page_count*8 [已配置(KB)],
    f.unallocated_extent_page_count*100.0/f.total_page_count as numeric(5,2)) [使用率]
from sys.dm_db_file_space_usage f
union all
select db_name(l.database_id)
    , 'log file', null
    , cast(l.total_log_size_in_bytes/1024 as numeric(28,0)) [檔案大小(KB)]
    , cast(l.used_log_space_in_bytes/1024 as numeric(28,0)) [已配置(KB)]
    , cast(l.total_log_size_in_bytes/1024 as numeric(28,0))
    - cast(l.used_log_space_in_bytes/1024 as numeric(28,0)) [未配置(KB)]
    , cast(l.used_log_space_in_percent as numeric(5,2)) [使用率]
from sys.dm_db_log_space_usage 1
go
set transaction isolation level read committed
arrow
arrow
    文章標籤
    SQL Server 檔案大小
    全站熱搜

    SuSu 發表在 痞客邦 留言(0) 人氣()