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
文章標籤
全站熱搜
留言列表