2007~2011/SQL Server2009. 6. 28. 00:01
예약된 작업 시간에 SQL Profiler 를 수행하고 일정 시간이 지난 후 자동으로 Profiler 수행을 중지하고자 합니다. 하지만 예약된 작업을 설정할 때 종료 시점을 지정해 줄 수가 없습니다. sp_trace_setstatus 프로시저를 사용하여 종료시킬 수 있습니다만 sp_trace_create 프로시저의 @stoptime 파라미터를 활용하는 것도 괜찮을 것 같아 아래와 같이 만들어 봤습니다.


[T-SQL]
USE [master]
GO

CREATE PROC [dbo].[up_DBA_TSQL_MONITOR]
AS 
DECLARE @sufFix NVARCHAR(8)
, @TraceID         INT
, @TraceFile NVARCHAR(245)
, @MAXFILESIZE         BIGINT
, @StopTime DATETIME
, @FileCount INT
, @on BIT


-- Trace 생성 파라미터 설정
SET @sufFix = convert(char(8), getdate(), 112)
SET @TraceFile = N'D:\SQL\PROFILER\UserTrace' + @sufFix
SET @Maxfilesize = 5
SET @StopTime = DATEADD(hh, 4, getdate()) -- 4시간 동안 추적 수행 
SET @FileCount = 10
SET @on = 1



BEGIN TRY
    EXEC sp_trace_create @TraceID output, 0, @TraceFile, @Maxfilesize, @StopTime

-- 이벤트 설정 
        EXEC sp_trace_setevent @TraceID, 12, 1, @on 
        EXEC sp_trace_setevent @TraceID, 12, 6, @on 
        EXEC sp_trace_setevent @TraceID, 12, 8, @on
        EXEC sp_trace_setevent @TraceID, 12, 10, @on 
        EXEC sp_trace_setevent @TraceID, 12, 11, @on 
        EXEC sp_trace_setevent @TraceID, 12, 12, @on 
        EXEC sp_trace_setevent @TraceID, 12, 14, @on 
        EXEC sp_trace_setevent @TraceID, 12, 15, @on 
        EXEC sp_trace_setevent @TraceID, 12, 27, @on 
        EXEC sp_trace_setevent @TraceID, 12, 30, @on 
        EXEC sp_trace_setevent @TraceID, 12, 35, @on

-- 'laigo' 데이터베이스에서 발생하는 DELETE 쿼리를 찾아냅니다 
EXEC sp_trace_setfilter @TraceID, 1, 0, 6, N'%DELETE%'
EXEC sp_trace_setfilter @TraceID, 35, 0, 6, N'laigo'

        -- Trace 시작 
EXEC sp_trace_setstatus @TraceID, 1

END TRY
BEGIN CATCH

    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT 
        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();

    RAISERROR (@ErrorMessage, -- Message text.
               @ErrorSeverity, -- Severity.
               @ErrorState -- State.
               );

END CATCH

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



[실행결과]
-- 프로시저를 수행하고 결과를 확인합니다. 
EXEC MASTER..UP_DBA_TSQL_MONITOR
SELECT * FROM ::fn_trace_getinfo (default)

-- Trace 를 중지하고 제거합니다. 
EXEC sp_trace_setstatus 2, 0 
EXEC sp_trace_setstatus 2, 2



SQL Server 2005 에서 테스트 되었으며 SQL Server 2000 에서는 약간의 수정이 필요할 듯 합니다. (적! 당! 히!)


[참고자료]
SQL Server Trace Log 확인


작성자 : Lai Go / 작성일자 : 2009.06.26




Posted by 사용자 Lai Go

댓글을 달아 주세요