MS-SQL File 사용량 보기

일전에 database disk사용량을 보고 싶어서 만들었던 Transact-SQL이 있지만, mdf file 하고 ldf file하고 나누어서 table에 저장을 하여 보았기 때문에 display상 살짝 마음에 안드는 부분도 있고 , 다시 한번 정리하자는 의미에서 새로 만들어 보았다.

이번 버전은 tbl_db_diskSize라는 table에 mdf 와 ldf 파일을 저장하기 때문에 기본 버전보다 보기가 훨씬 좋다. 물론 filegroup 또한, 나누어서 저장하였기 때문에 따로 보지 않아도 된다. 이번 버전은 MS-SQL 버전이며 MS-SQL 2000에서는 Error를 발생하며, MS-SQL 2008에서도 무리 없이 출력이 될 것이다.

-- 미리작성을해야하는부분

CREATE TABLE tbl_db_diskSize(

                   [idx] int identity(1,1) NOT NULL

,                 [database_name] varchar(100) NOT NULL -- 데이터베이스이름

,                 [logical_name] varchar(100) NOT NULL -- 데이터베이스논리적이름

,                 [physical_file_name] varchar(100) NOT NULL -- 데이터베이스물리적파일이름

,                 [type_desc] varchar(50) -- 데이터베이스파일타입

,                 [filegroup_name] varchar(50) NOT NULL -- 데이터베이스파일그룹이름

,                 [page_size] int NOT NULL -- 데이터베이스페이지크기

,                 [page_size(MB)] float NOT NULL -- 데이터베이스파일크기(MB) 공식:page_size*8)/1024

,                 [extents] int NOT NULL -- 데이터베이스extents 크기

,                 [extents_size(MB)] float NULL -- 데이터베이스extents 크기공식:(extents_size*8*8)/1024

,                 [used_extents] int NOT NULL -- 데이터베이스에서현재사용하고있는extents 크기

,                 [used_extents_size(MB)] float NULL -- 데이터베이스에서현재사용하고있는크기(MB)

,                 [used_percent] float NULL -- 데이터베이스가현재파일사용하고있는사용율(%)

,                 [physical_name] varchar(400) NOT NULL -- 데이터베이스가저장되어있는운영체제경로

,                 [date] varchar(10) NOT NULL -- 저장일

                  CONSTRAINT [PK_tbl_db_diskSize_1] PRIMARY KEY CLUSTERED

                  ([date] DESC, [idx] DESC) WITH (FILLFACTOR = 100)

)

CREATE NONCLUSTERED INDEX nx_db_diskSize_logical_name ON tbl_db_diskSize([logical_name])

 

 

/***************************************************************************

                  일일Agent로돌림

***************************************************************************/

-- 데이터베이스MDF 사용량임시테이블

IF EXISTS (Select * From tempdb..sysobjects Where [name] Like '%#size_tmp_MDF_dbcc%')

DROP TABLE #size_tmp_MDF_dbcc

 

CREATE TABLE #size_tmp_MDF_dbcc (

[Fileid]    int

, [Filegroup]   int

, [TotalExtents] Float

, [UsedExtents]  Float

, [Name]    Varchar(1024) COLLATE Korean_Wansung_CI_AS_KS

, [FileName]   Varchar(1024) COLLATE Korean_Wansung_CI_AS_KS

)

 

-- 데이터베이스LDF 사용량임시테이블

IF EXISTS (Select * From tempdb..sysobjects Where [name] Like '%#size_tmp_LDF_dbcc%')

DROP TABLE #size_tmp_LDF_dbcc

 

CREATE TABLE #size_tmp_LDF_dbcc (

[Database]    Varchar(1024) COLLATE Korean_Wansung_CI_AS_KS

, [Log Size]    Float

, [Log Space Used] Float

, [Status]     Bit

)

 

-- 데이터베이스총합MDF

IF EXISTS (Select * From tempdb..sysobjects Where [name] Like '%#Total_MDF%')

DROP TABLE #Total_MDF

 

CREATE TABLE #Total_MDF(

                  [databaseName] varchar(50)  COLLATE Korean_Wansung_CI_AS_KS

,                 [name] varchar(50)  COLLATE Korean_Wansung_CI_AS_KS

,                 [type_desc] varchar(50)   COLLATE Korean_Wansung_CI_AS_KS

,                 [filegroups] varchar(50)  COLLATE Korean_Wansung_CI_AS_KS

,                 [physical_name] varchar(1000)  COLLATE Korean_Wansung_CI_AS_KS

,                 [size] int

,                 [filesize(MB)] int

)

 

 

 

declare @Query nvarchar(50)

set @Query = 'dbcc sqlperf(logspace)'

 

INSERT INTO #size_tmp_LDF_dbcc execute(@Query)

INSERT INTO #size_tmp_MDF_dbcc EXEC sp_MSforeachdb 'USE [?] ; DBCC SHOWFILESTATS'

INSERT INTO #total_mdf

exec sp_msforeachdb 'select    ''?'' as databaseName

                                   ,                 sdf.name

                                   ,                 sdf.type_desc

                                   ,                 sf.name as filegroups

                                   ,                 sdf.Physical_name

                                   ,                 sdf.size

                                   ,                 (sdf.size * 8) / 1024 as [filesize(MB)]                        

from [?].sys.database_files as sdf left join [?].sys.filegroups as sf

on sdf.data_space_id = sf.data_space_id '

 

 

INSERT INTO master..tbl_db_diskSize

SELECT      tm.databaseName -- 데이터베이스이름

,                 tm.name -- 테이터베이스논리이름

,                 RIGHT(tm.physical_name,CHARINDEX('\',REVERSE(tm.physical_name))-1) as physical_file_name

,                 tm.type_desc -- 데이터베이스파일타입

,                 IsNULL(tm.filegroups, '-') -- 데이터베이스파이그룹이름

,                 tm.size -- 8KB 페이지단위로나타낸파일의현재크기

,                 tm.[filesize(MB)]

,                 IsNULL(tmd.totalExtents, 0) as mdTotalExtents -- 데이터베이스파일

,                 case

                      when tm.type_desc = 'rows' then (tmd.totalExtents * 8 * 8) / 1024

                      when tm.type_desc = 'log' then tld.[Log Size]

                  end as [현재크기(MB)]

,                 IsNULL(tmd.usedextents, 0) as mdusedextents -- 데이터베이스파일이현재사용중인크기

,                 case

                      when tm.type_desc = 'rows' then (tmd.usedextents * 8 * 8) /1024

                      when tm.type_desc = 'Log' then (tld.[Log Space Used] * tld.[Log Size]) / 100

                  end as [현재사용량(MB)]

,                 case

                      when tm.type_desc = 'rows' then ((tmd.usedextents * 8 * 8) /1024) / ((tmd.totalExtents * 8 * 8) / 1024) * 100

                       when tm.type_desc = 'Log' then tld.[Log Space Used]

                  end as [현재사용량(%)]

,                 tm.physical_name

,                 CONVERT(varchar(10), getdate(), 120)

from #Total_MDF as tm inner join #size_tmp_LDF_dbcc as tld

on tm.databasename = tld.[database] left join #size_tmp_MDF_dbcc as tmd

on tm.name = tmd.name

MS-SQL에서 해당 Transact-SQL을 쓰고 싶다면, sys.database_files을 sysfiles로 바꾸고 sys.filegroups을 sysfilegroups로 바꾸면 된다. 해당 필드 이름이나 목록도 살펴봐야 할 것이다.

--------------------------------------------------------

DBA 이승연

Microsoft Certified Trainer (MCT) SQL Server

GRAVITY co.,Ltd.

Global Business Dept / Tech Support

Tel 02-2132-7351 / Fax 02-2132-7330

MOBILE 010-9138-7789

--------------------------------------------------------