예약된 작업 시간에 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
