#title T-SQL Trace
[[TableOfContents]]

{{{

if object_id('dbo.TraceID') is not null
	drop table dbo.TraceID

create table dbo.TraceID (TraceID int, ExecDT datetime)

declare @rc int
declare @TraceID int
declare @maxfilesize bigint
declare @StopTime datetime
declare @FileName nvarchar(255)

declare @rc int
declare @TraceID int
declare @maxfilesize bigint
declare @StopTime datetime
declare @FileName nvarchar(255)

--시작: 여기만 바꾸면 됩니다.
set @StopTime = '2008-04-04 23:59:59.000'
set @MaxFileSize = 512 
set @FileName = N'C:\SQL_Trace\TraceLog'
--종료: 여기만 바꾸면 됩니다. 파일명은 TraceLog.trc 로 됩니다.

exec @rc = sp_trace_create @TraceID output, 2, @FileName, @MaxFileSize, @StopTime
if (@rc != 0) goto error

-- Client side File and Table cannot be scripted

-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 11, 1, @on
exec sp_trace_setevent @TraceID, 11, 2, @on
exec sp_trace_setevent @TraceID, 11, 3, @on
exec sp_trace_setevent @TraceID, 11, 8, @on
exec sp_trace_setevent @TraceID, 11, 9, @on
exec sp_trace_setevent @TraceID, 11, 10, @on
exec sp_trace_setevent @TraceID, 11, 11, @on
exec sp_trace_setevent @TraceID, 11, 12, @on
exec sp_trace_setevent @TraceID, 11, 13, @on
exec sp_trace_setevent @TraceID, 11, 14, @on
exec sp_trace_setevent @TraceID, 11, 15, @on
exec sp_trace_setevent @TraceID, 11, 16, @on
exec sp_trace_setevent @TraceID, 11, 17, @on
exec sp_trace_setevent @TraceID, 11, 18, @on
exec sp_trace_setevent @TraceID, 11, 21, @on
exec sp_trace_setevent @TraceID, 11, 26, @on
exec sp_trace_setevent @TraceID, 11, 35, @on
exec sp_trace_setevent @TraceID, 13, 1, @on
exec sp_trace_setevent @TraceID, 13, 2, @on
exec sp_trace_setevent @TraceID, 13, 3, @on
exec sp_trace_setevent @TraceID, 13, 8, @on
exec sp_trace_setevent @TraceID, 13, 9, @on
exec sp_trace_setevent @TraceID, 13, 10, @on
exec sp_trace_setevent @TraceID, 13, 11, @on
exec sp_trace_setevent @TraceID, 13, 12, @on
exec sp_trace_setevent @TraceID, 13, 13, @on
exec sp_trace_setevent @TraceID, 13, 14, @on
exec sp_trace_setevent @TraceID, 13, 15, @on
exec sp_trace_setevent @TraceID, 13, 16, @on
exec sp_trace_setevent @TraceID, 13, 17, @on
exec sp_trace_setevent @TraceID, 13, 18, @on
exec sp_trace_setevent @TraceID, 13, 21, @on
exec sp_trace_setevent @TraceID, 13, 26, @on
exec sp_trace_setevent @TraceID, 13, 35, @on
exec sp_trace_setevent @TraceID, 14, 1, @on
exec sp_trace_setevent @TraceID, 14, 2, @on
exec sp_trace_setevent @TraceID, 14, 3, @on
exec sp_trace_setevent @TraceID, 14, 8, @on
exec sp_trace_setevent @TraceID, 14, 9, @on
exec sp_trace_setevent @TraceID, 14, 10, @on
exec sp_trace_setevent @TraceID, 14, 11, @on
exec sp_trace_setevent @TraceID, 14, 12, @on
exec sp_trace_setevent @TraceID, 14, 13, @on
exec sp_trace_setevent @TraceID, 14, 14, @on
exec sp_trace_setevent @TraceID, 14, 15, @on
exec sp_trace_setevent @TraceID, 14, 16, @on
exec sp_trace_setevent @TraceID, 14, 17, @on
exec sp_trace_setevent @TraceID, 14, 18, @on
exec sp_trace_setevent @TraceID, 14, 21, @on
exec sp_trace_setevent @TraceID, 14, 26, @on
exec sp_trace_setevent @TraceID, 14, 35, @on
exec sp_trace_setevent @TraceID, 15, 1, @on
exec sp_trace_setevent @TraceID, 15, 2, @on
exec sp_trace_setevent @TraceID, 15, 3, @on
exec sp_trace_setevent @TraceID, 15, 8, @on
exec sp_trace_setevent @TraceID, 15, 9, @on
exec sp_trace_setevent @TraceID, 15, 10, @on
exec sp_trace_setevent @TraceID, 15, 11, @on
exec sp_trace_setevent @TraceID, 15, 12, @on
exec sp_trace_setevent @TraceID, 15, 13, @on
exec sp_trace_setevent @TraceID, 15, 14, @on
exec sp_trace_setevent @TraceID, 15, 15, @on
exec sp_trace_setevent @TraceID, 15, 16, @on
exec sp_trace_setevent @TraceID, 15, 17, @on
exec sp_trace_setevent @TraceID, 15, 18, @on
exec sp_trace_setevent @TraceID, 15, 21, @on
exec sp_trace_setevent @TraceID, 15, 26, @on
exec sp_trace_setevent @TraceID, 15, 35, @on
exec sp_trace_setevent @TraceID, 17, 1, @on
exec sp_trace_setevent @TraceID, 17, 2, @on
exec sp_trace_setevent @TraceID, 17, 3, @on
exec sp_trace_setevent @TraceID, 17, 8, @on
exec sp_trace_setevent @TraceID, 17, 9, @on
exec sp_trace_setevent @TraceID, 17, 10, @on
exec sp_trace_setevent @TraceID, 17, 11, @on
exec sp_trace_setevent @TraceID, 17, 12, @on
exec sp_trace_setevent @TraceID, 17, 13, @on
exec sp_trace_setevent @TraceID, 17, 14, @on
exec sp_trace_setevent @TraceID, 17, 15, @on
exec sp_trace_setevent @TraceID, 17, 16, @on
exec sp_trace_setevent @TraceID, 17, 17, @on
exec sp_trace_setevent @TraceID, 17, 18, @on
exec sp_trace_setevent @TraceID, 17, 21, @on
exec sp_trace_setevent @TraceID, 17, 26, @on
exec sp_trace_setevent @TraceID, 17, 35, @on
exec sp_trace_setevent @TraceID, 53, 1, @on
exec sp_trace_setevent @TraceID, 53, 2, @on
exec sp_trace_setevent @TraceID, 53, 3, @on
exec sp_trace_setevent @TraceID, 53, 8, @on
exec sp_trace_setevent @TraceID, 53, 9, @on
exec sp_trace_setevent @TraceID, 53, 10, @on
exec sp_trace_setevent @TraceID, 53, 11, @on
exec sp_trace_setevent @TraceID, 53, 12, @on
exec sp_trace_setevent @TraceID, 53, 13, @on
exec sp_trace_setevent @TraceID, 53, 14, @on
exec sp_trace_setevent @TraceID, 53, 15, @on
exec sp_trace_setevent @TraceID, 53, 16, @on
exec sp_trace_setevent @TraceID, 53, 17, @on
exec sp_trace_setevent @TraceID, 53, 18, @on
exec sp_trace_setevent @TraceID, 53, 21, @on
exec sp_trace_setevent @TraceID, 53, 26, @on
exec sp_trace_setevent @TraceID, 53, 35, @on
exec sp_trace_setevent @TraceID, 70, 1, @on
exec sp_trace_setevent @TraceID, 70, 2, @on
exec sp_trace_setevent @TraceID, 70, 3, @on
exec sp_trace_setevent @TraceID, 70, 8, @on
exec sp_trace_setevent @TraceID, 70, 9, @on
exec sp_trace_setevent @TraceID, 70, 10, @on
exec sp_trace_setevent @TraceID, 70, 11, @on
exec sp_trace_setevent @TraceID, 70, 12, @on
exec sp_trace_setevent @TraceID, 70, 13, @on
exec sp_trace_setevent @TraceID, 70, 14, @on
exec sp_trace_setevent @TraceID, 70, 15, @on
exec sp_trace_setevent @TraceID, 70, 16, @on
exec sp_trace_setevent @TraceID, 70, 17, @on
exec sp_trace_setevent @TraceID, 70, 18, @on
exec sp_trace_setevent @TraceID, 70, 21, @on
exec sp_trace_setevent @TraceID, 70, 26, @on
exec sp_trace_setevent @TraceID, 70, 35, @on
exec sp_trace_setevent @TraceID, 71, 1, @on
exec sp_trace_setevent @TraceID, 71, 2, @on
exec sp_trace_setevent @TraceID, 71, 3, @on
exec sp_trace_setevent @TraceID, 71, 8, @on
exec sp_trace_setevent @TraceID, 71, 9, @on
exec sp_trace_setevent @TraceID, 71, 10, @on
exec sp_trace_setevent @TraceID, 71, 11, @on
exec sp_trace_setevent @TraceID, 71, 12, @on
exec sp_trace_setevent @TraceID, 71, 13, @on
exec sp_trace_setevent @TraceID, 71, 14, @on
exec sp_trace_setevent @TraceID, 71, 15, @on
exec sp_trace_setevent @TraceID, 71, 16, @on
exec sp_trace_setevent @TraceID, 71, 17, @on
exec sp_trace_setevent @TraceID, 71, 18, @on
exec sp_trace_setevent @TraceID, 71, 21, @on
exec sp_trace_setevent @TraceID, 71, 26, @on
exec sp_trace_setevent @TraceID, 71, 35, @on
exec sp_trace_setevent @TraceID, 72, 1, @on
exec sp_trace_setevent @TraceID, 72, 2, @on
exec sp_trace_setevent @TraceID, 72, 3, @on
exec sp_trace_setevent @TraceID, 72, 8, @on
exec sp_trace_setevent @TraceID, 72, 9, @on
exec sp_trace_setevent @TraceID, 72, 10, @on
exec sp_trace_setevent @TraceID, 72, 11, @on
exec sp_trace_setevent @TraceID, 72, 12, @on
exec sp_trace_setevent @TraceID, 72, 13, @on
exec sp_trace_setevent @TraceID, 72, 14, @on
exec sp_trace_setevent @TraceID, 72, 15, @on
exec sp_trace_setevent @TraceID, 72, 16, @on
exec sp_trace_setevent @TraceID, 72, 17, @on
exec sp_trace_setevent @TraceID, 72, 18, @on
exec sp_trace_setevent @TraceID, 72, 21, @on
exec sp_trace_setevent @TraceID, 72, 26, @on
exec sp_trace_setevent @TraceID, 72, 35, @on
exec sp_trace_setevent @TraceID, 74, 1, @on
exec sp_trace_setevent @TraceID, 74, 2, @on
exec sp_trace_setevent @TraceID, 74, 3, @on
exec sp_trace_setevent @TraceID, 74, 8, @on
exec sp_trace_setevent @TraceID, 74, 9, @on
exec sp_trace_setevent @TraceID, 74, 10, @on
exec sp_trace_setevent @TraceID, 74, 11, @on
exec sp_trace_setevent @TraceID, 74, 12, @on
exec sp_trace_setevent @TraceID, 74, 13, @on
exec sp_trace_setevent @TraceID, 74, 14, @on
exec sp_trace_setevent @TraceID, 74, 15, @on
exec sp_trace_setevent @TraceID, 74, 16, @on
exec sp_trace_setevent @TraceID, 74, 17, @on
exec sp_trace_setevent @TraceID, 74, 18, @on
exec sp_trace_setevent @TraceID, 74, 21, @on
exec sp_trace_setevent @TraceID, 74, 26, @on
exec sp_trace_setevent @TraceID, 74, 35, @on
exec sp_trace_setevent @TraceID, 77, 1, @on
exec sp_trace_setevent @TraceID, 77, 2, @on
exec sp_trace_setevent @TraceID, 77, 3, @on
exec sp_trace_setevent @TraceID, 77, 8, @on
exec sp_trace_setevent @TraceID, 77, 9, @on
exec sp_trace_setevent @TraceID, 77, 10, @on
exec sp_trace_setevent @TraceID, 77, 11, @on
exec sp_trace_setevent @TraceID, 77, 12, @on
exec sp_trace_setevent @TraceID, 77, 13, @on
exec sp_trace_setevent @TraceID, 77, 14, @on
exec sp_trace_setevent @TraceID, 77, 15, @on
exec sp_trace_setevent @TraceID, 77, 16, @on
exec sp_trace_setevent @TraceID, 77, 17, @on
exec sp_trace_setevent @TraceID, 77, 18, @on
exec sp_trace_setevent @TraceID, 77, 21, @on
exec sp_trace_setevent @TraceID, 77, 26, @on
exec sp_trace_setevent @TraceID, 77, 35, @on
exec sp_trace_setevent @TraceID, 78, 1, @on
exec sp_trace_setevent @TraceID, 78, 2, @on
exec sp_trace_setevent @TraceID, 78, 3, @on
exec sp_trace_setevent @TraceID, 78, 8, @on
exec sp_trace_setevent @TraceID, 78, 9, @on
exec sp_trace_setevent @TraceID, 78, 10, @on
exec sp_trace_setevent @TraceID, 78, 11, @on
exec sp_trace_setevent @TraceID, 78, 12, @on
exec sp_trace_setevent @TraceID, 78, 13, @on
exec sp_trace_setevent @TraceID, 78, 14, @on
exec sp_trace_setevent @TraceID, 78, 15, @on
exec sp_trace_setevent @TraceID, 78, 16, @on
exec sp_trace_setevent @TraceID, 78, 17, @on
exec sp_trace_setevent @TraceID, 78, 18, @on
exec sp_trace_setevent @TraceID, 78, 21, @on
exec sp_trace_setevent @TraceID, 78, 26, @on
exec sp_trace_setevent @TraceID, 78, 35, @on
exec sp_trace_setevent @TraceID, 100, 1, @on
exec sp_trace_setevent @TraceID, 100, 2, @on
exec sp_trace_setevent @TraceID, 100, 3, @on
exec sp_trace_setevent @TraceID, 100, 8, @on
exec sp_trace_setevent @TraceID, 100, 9, @on
exec sp_trace_setevent @TraceID, 100, 10, @on
exec sp_trace_setevent @TraceID, 100, 11, @on
exec sp_trace_setevent @TraceID, 100, 12, @on
exec sp_trace_setevent @TraceID, 100, 13, @on
exec sp_trace_setevent @TraceID, 100, 14, @on
exec sp_trace_setevent @TraceID, 100, 15, @on
exec sp_trace_setevent @TraceID, 100, 16, @on
exec sp_trace_setevent @TraceID, 100, 17, @on
exec sp_trace_setevent @TraceID, 100, 18, @on
exec sp_trace_setevent @TraceID, 100, 21, @on
exec sp_trace_setevent @TraceID, 100, 26, @on
exec sp_trace_setevent @TraceID, 100, 35, @on


-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

exec sp_trace_setfilter @TraceID, 11, 1, 6, N'로그인계정'
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler'


-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

insert dbo.TraceID values(@TraceID, getdate())

-- display trace id for future references
select TraceID=@TraceID
goto finish

error: 
select ErrorCode=@rc

finish: 
go

/*
--dbo.TraceID 테이블 확인
SELECT * FROM ::fn_trace_getinfo(1) --값 1은 TraceID

select 
	TextData
,	HostName
,	ApplicationName
,	LoginName
,	SPID
,	Duration
,	StartTime
,	EndTime
,	Reads
,	Writes
,	CPU
,	RowCounts
,	EventClass
.
.
.
.
.
.
from fn_trace_gettable('C:\SQL_Trace\TraceLog.trc', default)
where TextData NOT LIKE 'use%'
and TextData Not LIKE 'SET%'
and TextData Not LIKE 'SELECT @@%'
and TextData Not LIKE '%SERVERPROPERTY%'
and TextData Not LIKE '%select 504,c.name%'
and TextData Not LIKE '%DB_NAME()%'
and TextData Not LIKE '%USER_NAME()%'
and TextData Not LIKE '%su.name%'

--trace 중지하기..sp_trace_setstatus 의 첫번째 매개변수는 TraceID
exec sp_trace_setstatus 1, 0	-- 지정한 추적을 중지합니다.
exec sp_trace_setstatus 1, 2	--지정한 추적을 닫고 서버에서 해당 정의를 삭제합니다.
*/
}}}