-- variables
declare @rc int
declare @TraceID int
declare @maxFileSize bigint
declare @fileName nvarchar(128)
declare @on bit
set @maxFileSize = 200
set @fileName = N'C:\Trace'
set @on = 1
-- create trace
exec @rc = sp_trace_create @TraceID output, 2, @fileName, @maxFileSize, NULL
-- if error end process
if (@rc != 0) goto error
-- set the events
-- logins
exec sp_trace_setevent @TraceID, 14, 3, @on --DatabaseID
exec sp_trace_setevent @TraceID, 14, 6, @on -- NTUsername
exec sp_trace_setevent @TraceID, 14, 7, @on -- NTDomainname
exec sp_trace_setevent @TraceID, 14, 8, @on -- Hostname
exec sp_trace_setevent @TraceID, 14, 10, @on -- Applicationname
exec sp_trace_setevent @TraceID, 14, 11, @on -- Loginname
exec sp_trace_setevent @TraceID, 14, 12, @on -- SPID
exec sp_trace_setevent @TraceID, 14, 13, @on -- Duration
exec sp_trace_setevent @TraceID, 14, 14, @on -- Starttime
exec sp_trace_setevent @TraceID, 14, 15, @on -- Endtime
exec sp_trace_setevent @TraceID, 14, 21, @on -- Eventsubclass
exec sp_trace_setevent @TraceID, 14, 26, @on -- Servername
exec sp_trace_setevent @TraceID, 14, 35, @on -- Databasename
exec sp_trace_setevent @TraceID, 14, 40, @on -- DBusername
exec sp_trace_setevent @TraceID, 14, 51, @on -- Eventsequence
exec sp_trace_setevent @TraceID, 14, 64, @on -- Sessionloginname
-- set Filters
exec sp_trace_setfilter @TraceID, 11, 0, 6, 'testuser'
-- start the trace
exec sp_trace_setstatus @TraceID, 1
-- display trace id for future references
select TraceID=@TraceID
goto finish
-- error
error:
select ErrorCode=@rc
-- exit
finish:
go
--******************************************************--
-- to view the trace information
select * from :: fn_trace_getinfo(default)
-- to stop and delete the trace
exec sp_trace_setstatus @traceid, @status = 0 -- 0 stop
exec sp_trace_setstatus @traceid, @status = 2 -- 2 delete
-- load the trace into temp table
use testdb
go
select * into temp_trace
from fn_trace_gettable('C:\Trace.trc', default);
go
