[MSSQL] Database 파일 정보 조회 Script

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