일전에 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