Database 파일들의 정보를 조회하는 스크립트 입니다.
대충 대충 필요하겠다 싶은것을 정리했습니다.
/*
Database file 별상태점검
- by Koon
*/
create proc dbo.DBA_GetDBFilesInfo
as
set nocount on
if exists (select top 1 * from sys.tables where name = 'DBA_DBFileInfo')
truncate table DBA_DBFileInfo
else
create table DBA_DBFileInfo (db_name nvarchar(100), db_id int, file_id int, type_desc nvarchar(60), data_space_id int, name sysname, physical_name nvarchar(260), state_desc nvarchar(60), size int, max_size int, growth int, is_percent_growth bit)
insert into DBA_DBFileInfo (db_name , db_id, file_id, type_desc, data_space_id, name, physical_name, state_desc, size, max_size, growth, is_percent_growth)
exec sp_msforeachdb 'use ?; select db_name(), db_id(), file_id, type_desc, data_space_id, name, physical_name, state_desc, size, max_size, growth, is_percent_growth from sys.database_files'
select a.db_id, a.db_name, a.file_id, a.name, a.physical_name, state_desc, convert(varchar(20), (a.size * 8 / 1024)) + 'MB' as file_size, -- (BytesOnDisk / 1024 / 1024) as used_size,
max_size, (case when is_percent_growth = 1 then convert(varchar(3), a.growth) + '%' else convert(varchar(20), (a.growth * 8) / 1024) + 'MB' end) as file_growth,
convert(decimal(13, 2), (NumberReads * 1.00 / (NumberReads + NumberWrites * 1.00)) * 100) as ReadsPercent,
convert(decimal(13, 2), (NumberWrites * 1.00 / (NumberReads + NumberWrites * 1.00)) * 100) as WritesPercent,
NumberReads, (BytesRead / 1024 / 1024) as MBytesRead, (IoStallReadMS / 1000) as IoStallReadSec,
NumberWrites, (BytesWritten / 1024 / 1024) as MBytesWritten, (IoStallWriteMS / 1000) as IoStallWriteSec
from DBA_DBFileInfo a
cross apply sys.fn_virtualfilestats(a.db_id, a.file_id)
order by a.db_id, a.file_id
'Brain Trainning > DataBase' 카테고리의 다른 글
[MSSQL] 시스템 대기 상태 관련 글 [펌] (0) | 2012.04.05 |
---|---|
[MSSQL] Wait Stats Best References .. (0) | 2012.04.04 |
[MSSQL] 대기상태 확인 쿼리 (0) | 2012.03.27 |
[MSSQL] I/O 특성을 고려한 스토리지 운영 (0) | 2012.03.20 |
[DMV] 몇몇 알아야 하는 DMV (0) | 2012.03.14 |