2014. 2. 3. 18:34
일전에 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에서도 무리 없이 출력이 될 것이다.
2009/10/07 - MS-SQL Database File 용량 저장하기 - 2000버전 보러가기
-- 미리 작성을 해야 하는 부분
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] asc) WITH (FILLFACTOR = 100)
)
CREATE NONCLUSTERED INDEX nx_db_diskSize_logical_name ON tbl_db_diskSize([logical_name])
GO
-- =============================================
-- Author : 이승연
-- Create date : 2009. 10. 07
-- Description : 데이터베이스 MDF, LDF 파일 크기 저장
-- =============================================
CREATE PROCEDURE dbo.usp_set_db_diskSize
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
BEGIN TRAN
/***************************************************************************
일일 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 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
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
INSERT INTO ErrorTable
(ErrorNumber, ErrorMessage, ErrorState, ErrorSeverity, ErrorLine, ErrorProcedure)
VALUES
(ERROR_NUMBER(), ERROR_MESSAGE(), ERROR_STATE(), ERROR_SEVERITY(), ERROR_LINE(), ERROR_PROCEDURE())
END CATCH
END
