주언형님 4번은 제가 올려보겠습니다.

이와중에 열심히 작성하시고 계신거 아닌가 모르겠네요. *^^*

해당 T-SQL은 MS-SQL 2005 이상버전에서 실행됩니다. 참고해주세요.

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

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 : -

-- 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