Post reply

Server side trace question

  • November 18, 2013 at 5:36 pm

    #395848

    I've started monitoring on my SQL 2000 servers by running server side trace through SQL job.

    The steps of the job are

    (1)start trace

    (2)run the trace for 2 mins

    (3)stop trace

    (4)populate the results of the trace into a table

    The events that I'm watching are :

    exec sp_trace_setevent @TraceID, 10, 1, @on

    exec sp_trace_setevent @TraceID, 10, 6, @on

    exec sp_trace_setevent @TraceID, 10, 7, @on

    exec sp_trace_setevent @TraceID, 10, 8, @on

    exec sp_trace_setevent @TraceID, 10, 10, @on

    exec sp_trace_setevent @TraceID, 10, 11, @on

    exec sp_trace_setevent @TraceID, 10, 12, @on

    exec sp_trace_setevent @TraceID, 10, 13, @on

    exec sp_trace_setevent @TraceID, 10, 14, @on

    exec sp_trace_setevent @TraceID, 10, 15, @on

    exec sp_trace_setevent @TraceID, 10, 16, @on

    exec sp_trace_setevent @TraceID, 10, 17, @on

    exec sp_trace_setevent @TraceID, 10, 18, @on

    exec sp_trace_setevent @TraceID, 10, 26, @on

    exec sp_trace_setevent @TraceID, 10, 35, @on

    exec sp_trace_setevent @TraceID, 12, 1, @on

    exec sp_trace_setevent @TraceID, 12, 6, @on

    exec sp_trace_setevent @TraceID, 12, 7, @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, 13, @on

    exec sp_trace_setevent @TraceID, 12, 14, @on

    exec sp_trace_setevent @TraceID, 12, 15, @on

    exec sp_trace_setevent @TraceID, 12, 16, @on

    exec sp_trace_setevent @TraceID, 12, 17, @on

    exec sp_trace_setevent @TraceID, 12, 18, @on

    exec sp_trace_setevent @TraceID, 12, 26, @on

    exec sp_trace_setevent @TraceID, 12, 35, @on

    exec sp_trace_setevent @TraceID, 13, 1, @on

    exec sp_trace_setevent @TraceID, 13, 6, @on

    exec sp_trace_setevent @TraceID, 13, 7, @on

    exec sp_trace_setevent @TraceID, 13, 8, @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, 26, @on

    exec sp_trace_setevent @TraceID, 13, 35, @on

    exec sp_trace_setevent @TraceID, 14, 1, @on

    exec sp_trace_setevent @TraceID, 14, 6, @on

    exec sp_trace_setevent @TraceID, 14, 7, @on

    exec sp_trace_setevent @TraceID, 14, 8, @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, 26, @on

    exec sp_trace_setevent @TraceID, 14, 35, @on

    exec sp_trace_setevent @TraceID, 15, 1, @on

    exec sp_trace_setevent @TraceID, 15, 6, @on

    exec sp_trace_setevent @TraceID, 15, 7, @on

    exec sp_trace_setevent @TraceID, 15, 8, @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, 26, @on

    exec sp_trace_setevent @TraceID, 15, 35, @on

    exec sp_trace_setevent @TraceID, 17, 1, @on

    exec sp_trace_setevent @TraceID, 17, 6, @on

    exec sp_trace_setevent @TraceID, 17, 7, @on

    exec sp_trace_setevent @TraceID, 17, 8, @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, 26, @on

    exec sp_trace_setevent @TraceID, 17, 35, @on

    exec sp_trace_setevent @TraceID, 40, 1, @on

    exec sp_trace_setevent @TraceID, 40, 6, @on

    exec sp_trace_setevent @TraceID, 40, 7, @on

    exec sp_trace_setevent @TraceID, 40, 8, @on

    exec sp_trace_setevent @TraceID, 40, 10, @on

    exec sp_trace_setevent @TraceID, 40, 11, @on

    exec sp_trace_setevent @TraceID, 40, 12, @on

    exec sp_trace_setevent @TraceID, 40, 13, @on

    exec sp_trace_setevent @TraceID, 40, 14, @on

    exec sp_trace_setevent @TraceID, 40, 15, @on

    exec sp_trace_setevent @TraceID, 40, 16, @on

    exec sp_trace_setevent @TraceID, 40, 17, @on

    exec sp_trace_setevent @TraceID, 40, 18, @on

    exec sp_trace_setevent @TraceID, 40, 26, @on

    exec sp_trace_setevent @TraceID, 40, 35, @on

    exec sp_trace_setevent @TraceID, 41, 1, @on

    exec sp_trace_setevent @TraceID, 41, 6, @on

    exec sp_trace_setevent @TraceID, 41, 7, @on

    exec sp_trace_setevent @TraceID, 41, 8, @on

    exec sp_trace_setevent @TraceID, 41, 10, @on

    exec sp_trace_setevent @TraceID, 41, 11, @on

    exec sp_trace_setevent @TraceID, 41, 12, @on

    exec sp_trace_setevent @TraceID, 41, 13, @on

    exec sp_trace_setevent @TraceID, 41, 14, @on

    exec sp_trace_setevent @TraceID, 41, 15, @on

    exec sp_trace_setevent @TraceID, 41, 16, @on

    exec sp_trace_setevent @TraceID, 41, 17, @on

    exec sp_trace_setevent @TraceID, 41, 18, @on

    exec sp_trace_setevent @TraceID, 41, 26, @on

    exec sp_trace_setevent @TraceID, 41, 35, @on

    exec sp_trace_setevent @TraceID, 43, 1, @on

    exec sp_trace_setevent @TraceID, 43, 6, @on

    exec sp_trace_setevent @TraceID, 43, 7, @on

    exec sp_trace_setevent @TraceID, 43, 8, @on

    exec sp_trace_setevent @TraceID, 43, 10, @on

    exec sp_trace_setevent @TraceID, 43, 11, @on

    exec sp_trace_setevent @TraceID, 43, 12, @on

    exec sp_trace_setevent @TraceID, 43, 13, @on

    exec sp_trace_setevent @TraceID, 43, 14, @on

    exec sp_trace_setevent @TraceID, 43, 15, @on

    exec sp_trace_setevent @TraceID, 43, 16, @on

    exec sp_trace_setevent @TraceID, 43, 17, @on

    exec sp_trace_setevent @TraceID, 43, 18, @on

    exec sp_trace_setevent @TraceID, 43, 26, @on

    exec sp_trace_setevent @TraceID, 43, 35, @on

    exec sp_trace_setevent @TraceID, 44, 1, @on

    exec sp_trace_setevent @TraceID, 44, 6, @on

    exec sp_trace_setevent @TraceID, 44, 7, @on

    exec sp_trace_setevent @TraceID, 44, 8, @on

    exec sp_trace_setevent @TraceID, 44, 10, @on

    exec sp_trace_setevent @TraceID, 44, 11, @on

    exec sp_trace_setevent @TraceID, 44, 12, @on

    exec sp_trace_setevent @TraceID, 44, 13, @on

    exec sp_trace_setevent @TraceID, 44, 14, @on

    exec sp_trace_setevent @TraceID, 44, 15, @on

    exec sp_trace_setevent @TraceID, 44, 16, @on

    exec sp_trace_setevent @TraceID, 44, 17, @on

    exec sp_trace_setevent @TraceID, 44, 18, @on

    exec sp_trace_setevent @TraceID, 44, 26, @on

    exec sp_trace_setevent @TraceID, 44, 35, @on

    exec sp_trace_setevent @TraceID, 45, 1, @on

    exec sp_trace_setevent @TraceID, 45, 6, @on

    exec sp_trace_setevent @TraceID, 45, 7, @on

    exec sp_trace_setevent @TraceID, 45, 8, @on

    exec sp_trace_setevent @TraceID, 45, 10, @on

    exec sp_trace_setevent @TraceID, 45, 11, @on

    exec sp_trace_setevent @TraceID, 45, 12, @on

    exec sp_trace_setevent @TraceID, 45, 13, @on

    exec sp_trace_setevent @TraceID, 45, 14, @on

    exec sp_trace_setevent @TraceID, 45, 15, @on

    exec sp_trace_setevent @TraceID, 45, 16, @on

    exec sp_trace_setevent @TraceID, 45, 17, @on

    exec sp_trace_setevent @TraceID, 45, 18, @on

    exec sp_trace_setevent @TraceID, 45, 26, @on

    exec sp_trace_setevent @TraceID, 45, 35, @on

    The filters that I've set are:

    exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler%'

    exec sp_trace_setfilter @TraceID, 10, 0, 7, N'%Quest Diagnostic Server (Monitoring)%'

    exec sp_trace_setfilter @TraceID, 10, 0, 7, N'%SQLAgent - Job Manager%'

    I ran long running select queries on various databases to check if they are being trapped but they aren't. Not sure if I'm missing something.

    All I see in TextData column is thedetails of this trace job.

  • November 19, 2013 at 1:10 am

    #1667314

    sunny.tjk (11/18/2013)

    The steps of the job are

    (1)start trace

    (2)run the trace for 2 mins

    (3)stop trace

    ??

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • November 19, 2013 at 6:09 am

    #1667366

    Perry Whittle (11/19/2013)

    sunny.tjk (11/18/2013)

    The steps of the job are

    (1)start trace

    (2)run the trace for 2 mins

    (3)stop trace

    ??

    Like Perry, I'm not sure where the question is, but i'll throw out a best guess.

    If it were me, i would simply drop and create the trace(with a specific two minute stop time, rather than start it and stop it.

    --SELECT * from sys.traces

    declare @TraceIDToReview int

    declare @path varchar(255)

    SET @TraceIDToReview = 2 --this is the trace you want to review!

    SELECT @path = path from sys.traces WHERE id = @TraceIDToReview

    --start the trace

    --turn on the trace

    exec sp_trace_setstatus @TraceIDToReview , 1; ---start trace

    --run the trace for two minutes?

    WAITFOR DELAY '000:02:01'; --wait two minutes?

    exec sp_trace_setstatus @TraceIDToReview , 0; ---stop trace, you must know the traceid to stop it

    --exec sp_trace_setstatus @TraceIDToReview , 2 ---close trace you must know the traceid to delete it

    --get data from the trace

    SELECT

    TE.name As EventClassDescrip,

    v.subclass_name As EventSubClassDescrip,

    T.*

    FROM ::fn_trace_gettable(@path, default) T

    LEFT OUTER JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id

    LEFT OUTER JOIN sys.trace_subclass_values V

    ON T.EventClass = V.trace_event_id AND T.EventSubClass = V.subclass_value

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • November 19, 2013 at 8:49 am

    #1667446

    Not sure if you were able to see my complete question.

    I listed out the events that I'm tracing and the filters that I've put.

    So, my question was --I ran long running select queries on various databases to check if they are being trapped but they aren't. Not sure if I'm missing something.

    All I see in TextData column is thedetails of this trace job.

  • November 19, 2013 at 8:53 am

    #1667450

    I ran the following query to see if my select queries were trapped or not:

    select * from dbo.trace

    where textdata like '%SELECT * FROM %'

    TextData result just has:

    -- fn_trace_getinfo insert @tab select * from OpenRowset(TraceInfo, @handle)

  • November 22, 2013 at 6:12 am

    #1668379

    A few questions:

    1. If you run the trace without filters for a short period of time do you see any of the events you want to capture being captured?

    2. You are filtering on column 10 of your output, does your output of your events have 10 columns?

    3. Are you passing the correct traceid to the filters (select id from sys.traces)

    Can you attach the full trace definition you have?

    MCITP SQL 2005, MCSA SQL 2012

  • November 22, 2013 at 6:30 am

    #1668385

    RTaylor2208 (11/22/2013)

    A few questions:

    1. If you run the trace without filters for a short period of time do you see any of the events you want to capture being captured?

    2. You are filtering on column 10 of your output, does your output of your events have 10 columns?

    3. Are you passing the correct traceid to the filters (select id from sys.traces)

    Can you attach the full trace definition you have?

    I created a trace with his definitions and filters;

    here it is scripted out and fully commented:

    --#################################################################################################

    -- Scripting trace_id 2 from server HOL-WKS-444

    -- Trace Last Started/Restarted on Nov 19 2013 7:58:08:013AM

    -- Scripted for Analysis on Nov 22 2013 8:27:39:943AM

    --#################################################################################################

    --declare variables for parameterizing the command

    declare @traceidout int

    declare @myoptions int

    declare @mypath nvarchar(256)

    declare @mymaxfilesize bigint

    declare @mymaxRolloverFiles int

    declare @mystoptime datetime

    declare @on bit

    set @on = 1 --for scripting purposes, I think its better Always setting a script to start the trace after creation.

    set @mymaxfilesize = 20 --size in MB

    set @mymaxRolloverFiles = 5 --number of files; ie if 5 files, start rewriting on rollover

    set @mystoptime = NULL -- null if never ends, else a specific date

    set @myoptions = 2 -- TRACE_FILE_ROLLOVER = TRUE, SHUTDOWN_ON_ERROR = FALSE

    --This is the Actual Path on the scripted server.

    --for Portability reasons, we change this in the script right after this actual path to get the path for the default trace, which we assume to exist on the server.

    SELECT @mypath = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\MyDMLTrace_1' -- the trace adds ".trc" to the pathname, so avoid "name.trc.trc" by removing it for scripting

    SELECT @mypath = REVERSE(SUBSTRING(REVERSE(path),CHARINDEX('\',REVERSE(path)),256)) FROM sys.traces WHERE is_default = 1

    SELECT @mypath = @mypath + N'MyDMLTrace_1' --system appends .trc automatically for the filename

    --#################################################################################################

    --create the trace

    exec sp_trace_create @traceid = @traceidout output,

    @options = @myoptions,

    @tracefile = @mypath,

    @maxfilesize = @mymaxfilesize,

    @stoptime = @mystoptime

    --#################################################################################################

    --Begin Event definitions

    --#################################################################################################

    exec sp_trace_setevent @traceidout,10,2,@on --RPC:Completed,BinaryData

    exec sp_trace_setevent @traceidout,10,6,@on --RPC:Completed,NTUserName

    exec sp_trace_setevent @traceidout,10,7,@on --RPC:Completed,NTDomainName

    exec sp_trace_setevent @traceidout,10,8,@on --RPC:Completed,HostName

    exec sp_trace_setevent @traceidout,10,10,@on --RPC:Completed,ApplicationName

    exec sp_trace_setevent @traceidout,10,11,@on --RPC:Completed,LoginName

    exec sp_trace_setevent @traceidout,10,18,@on --RPC:Completed,CPU

    exec sp_trace_setevent @traceidout,10,26,@on --RPC:Completed,ServerName

    exec sp_trace_setevent @traceidout,10,35,@on --RPC:Completed,DatabaseName

    exec sp_trace_setevent @traceidout,10,12,@on --RPC:Completed,SPID

    exec sp_trace_setevent @traceidout,10,13,@on --RPC:Completed,Duration

    exec sp_trace_setevent @traceidout,10,14,@on --RPC:Completed,StartTime

    exec sp_trace_setevent @traceidout,10,15,@on --RPC:Completed,EndTime

    exec sp_trace_setevent @traceidout,10,16,@on --RPC:Completed,Reads

    exec sp_trace_setevent @traceidout,10,17,@on --RPC:Completed,Writes

    exec sp_trace_setevent @traceidout,12,1,@on --SQL:BatchCompleted,TextData

    exec sp_trace_setevent @traceidout,12,6,@on --SQL:BatchCompleted,NTUserName

    exec sp_trace_setevent @traceidout,12,7,@on --SQL:BatchCompleted,NTDomainName

    exec sp_trace_setevent @traceidout,12,8,@on --SQL:BatchCompleted,HostName

    exec sp_trace_setevent @traceidout,12,10,@on --SQL:BatchCompleted,ApplicationName

    exec sp_trace_setevent @traceidout,12,11,@on --SQL:BatchCompleted,LoginName

    exec sp_trace_setevent @traceidout,12,18,@on --SQL:BatchCompleted,CPU

    exec sp_trace_setevent @traceidout,12,26,@on --SQL:BatchCompleted,ServerName

    exec sp_trace_setevent @traceidout,12,35,@on --SQL:BatchCompleted,DatabaseName

    exec sp_trace_setevent @traceidout,12,12,@on --SQL:BatchCompleted,SPID

    exec sp_trace_setevent @traceidout,12,13,@on --SQL:BatchCompleted,Duration

    exec sp_trace_setevent @traceidout,12,14,@on --SQL:BatchCompleted,StartTime

    exec sp_trace_setevent @traceidout,12,15,@on --SQL:BatchCompleted,EndTime

    exec sp_trace_setevent @traceidout,12,16,@on --SQL:BatchCompleted,Reads

    exec sp_trace_setevent @traceidout,12,17,@on --SQL:BatchCompleted,Writes

    exec sp_trace_setevent @traceidout,13,1,@on --SQL:BatchStarting,TextData

    exec sp_trace_setevent @traceidout,13,6,@on --SQL:BatchStarting,NTUserName

    exec sp_trace_setevent @traceidout,13,7,@on --SQL:BatchStarting,NTDomainName

    exec sp_trace_setevent @traceidout,13,8,@on --SQL:BatchStarting,HostName

    exec sp_trace_setevent @traceidout,13,10,@on --SQL:BatchStarting,ApplicationName

    exec sp_trace_setevent @traceidout,13,11,@on --SQL:BatchStarting,LoginName

    exec sp_trace_setevent @traceidout,13,18,@on --SQL:BatchStarting,CPU

    exec sp_trace_setevent @traceidout,13,26,@on --SQL:BatchStarting,ServerName

    exec sp_trace_setevent @traceidout,13,35,@on --SQL:BatchStarting,DatabaseName

    exec sp_trace_setevent @traceidout,13,12,@on --SQL:BatchStarting,SPID

    exec sp_trace_setevent @traceidout,13,13,@on --SQL:BatchStarting,Duration

    exec sp_trace_setevent @traceidout,13,14,@on --SQL:BatchStarting,StartTime

    exec sp_trace_setevent @traceidout,13,15,@on --SQL:BatchStarting,EndTime

    exec sp_trace_setevent @traceidout,13,16,@on --SQL:BatchStarting,Reads

    exec sp_trace_setevent @traceidout,13,17,@on --SQL:BatchStarting,Writes

    exec sp_trace_setevent @traceidout,14,1,@on --Audit Login,TextData

    exec sp_trace_setevent @traceidout,14,6,@on --Audit Login,NTUserName

    exec sp_trace_setevent @traceidout,14,7,@on --Audit Login,NTDomainName

    exec sp_trace_setevent @traceidout,14,8,@on --Audit Login,HostName

    exec sp_trace_setevent @traceidout,14,10,@on --Audit Login,ApplicationName

    exec sp_trace_setevent @traceidout,14,11,@on --Audit Login,LoginName

    exec sp_trace_setevent @traceidout,14,18,@on --Audit Login,CPU

    exec sp_trace_setevent @traceidout,14,26,@on --Audit Login,ServerName

    exec sp_trace_setevent @traceidout,14,35,@on --Audit Login,DatabaseName

    exec sp_trace_setevent @traceidout,14,12,@on --Audit Login,SPID

    exec sp_trace_setevent @traceidout,14,13,@on --Audit Login,Duration

    exec sp_trace_setevent @traceidout,14,14,@on --Audit Login,StartTime

    exec sp_trace_setevent @traceidout,14,15,@on --Audit Login,EndTime

    exec sp_trace_setevent @traceidout,14,16,@on --Audit Login,Reads

    exec sp_trace_setevent @traceidout,14,17,@on --Audit Login,Writes

    exec sp_trace_setevent @traceidout,15,1,@on --Audit Logout,TextData

    exec sp_trace_setevent @traceidout,15,6,@on --Audit Logout,NTUserName

    exec sp_trace_setevent @traceidout,15,7,@on --Audit Logout,NTDomainName

    exec sp_trace_setevent @traceidout,15,8,@on --Audit Logout,HostName

    exec sp_trace_setevent @traceidout,15,10,@on --Audit Logout,ApplicationName

    exec sp_trace_setevent @traceidout,15,11,@on --Audit Logout,LoginName

    exec sp_trace_setevent @traceidout,15,18,@on --Audit Logout,CPU

    exec sp_trace_setevent @traceidout,15,26,@on --Audit Logout,ServerName

    exec sp_trace_setevent @traceidout,15,35,@on --Audit Logout,DatabaseName

    exec sp_trace_setevent @traceidout,15,12,@on --Audit Logout,SPID

    exec sp_trace_setevent @traceidout,15,13,@on --Audit Logout,Duration

    exec sp_trace_setevent @traceidout,15,14,@on --Audit Logout,StartTime

    exec sp_trace_setevent @traceidout,15,15,@on --Audit Logout,EndTime

    exec sp_trace_setevent @traceidout,15,16,@on --Audit Logout,Reads

    exec sp_trace_setevent @traceidout,15,17,@on --Audit Logout,Writes

    exec sp_trace_setevent @traceidout,17,1,@on --ExistingConnection,TextData

    exec sp_trace_setevent @traceidout,17,6,@on --ExistingConnection,NTUserName

    exec sp_trace_setevent @traceidout,17,7,@on --ExistingConnection,NTDomainName

    exec sp_trace_setevent @traceidout,17,8,@on --ExistingConnection,HostName

    exec sp_trace_setevent @traceidout,17,10,@on --ExistingConnection,ApplicationName

    exec sp_trace_setevent @traceidout,17,11,@on --ExistingConnection,LoginName

    exec sp_trace_setevent @traceidout,17,18,@on --ExistingConnection,CPU

    exec sp_trace_setevent @traceidout,17,26,@on --ExistingConnection,ServerName

    exec sp_trace_setevent @traceidout,17,35,@on --ExistingConnection,DatabaseName

    exec sp_trace_setevent @traceidout,17,12,@on --ExistingConnection,SPID

    exec sp_trace_setevent @traceidout,17,13,@on --ExistingConnection,Duration

    exec sp_trace_setevent @traceidout,17,14,@on --ExistingConnection,StartTime

    exec sp_trace_setevent @traceidout,17,15,@on --ExistingConnection,EndTime

    exec sp_trace_setevent @traceidout,17,16,@on --ExistingConnection,Reads

    exec sp_trace_setevent @traceidout,17,17,@on --ExistingConnection,Writes

    exec sp_trace_setevent @traceidout,40,1,@on --SQL:StmtStarting,TextData

    exec sp_trace_setevent @traceidout,40,6,@on --SQL:StmtStarting,NTUserName

    exec sp_trace_setevent @traceidout,40,7,@on --SQL:StmtStarting,NTDomainName

    exec sp_trace_setevent @traceidout,40,8,@on --SQL:StmtStarting,HostName

    exec sp_trace_setevent @traceidout,40,10,@on --SQL:StmtStarting,ApplicationName

    exec sp_trace_setevent @traceidout,40,11,@on --SQL:StmtStarting,LoginName

    exec sp_trace_setevent @traceidout,40,18,@on --SQL:StmtStarting,CPU

    exec sp_trace_setevent @traceidout,40,26,@on --SQL:StmtStarting,ServerName

    exec sp_trace_setevent @traceidout,40,35,@on --SQL:StmtStarting,DatabaseName

    exec sp_trace_setevent @traceidout,40,12,@on --SQL:StmtStarting,SPID

    exec sp_trace_setevent @traceidout,40,13,@on --SQL:StmtStarting,Duration

    exec sp_trace_setevent @traceidout,40,14,@on --SQL:StmtStarting,StartTime

    exec sp_trace_setevent @traceidout,40,15,@on --SQL:StmtStarting,EndTime

    exec sp_trace_setevent @traceidout,40,16,@on --SQL:StmtStarting,Reads

    exec sp_trace_setevent @traceidout,40,17,@on --SQL:StmtStarting,Writes

    exec sp_trace_setevent @traceidout,41,1,@on --SQL:StmtCompleted,TextData

    exec sp_trace_setevent @traceidout,41,6,@on --SQL:StmtCompleted,NTUserName

    exec sp_trace_setevent @traceidout,41,7,@on --SQL:StmtCompleted,NTDomainName

    exec sp_trace_setevent @traceidout,41,8,@on --SQL:StmtCompleted,HostName

    exec sp_trace_setevent @traceidout,41,10,@on --SQL:StmtCompleted,ApplicationName

    exec sp_trace_setevent @traceidout,41,11,@on --SQL:StmtCompleted,LoginName

    exec sp_trace_setevent @traceidout,41,18,@on --SQL:StmtCompleted,CPU

    exec sp_trace_setevent @traceidout,41,26,@on --SQL:StmtCompleted,ServerName

    exec sp_trace_setevent @traceidout,41,35,@on --SQL:StmtCompleted,DatabaseName

    exec sp_trace_setevent @traceidout,41,12,@on --SQL:StmtCompleted,SPID

    exec sp_trace_setevent @traceidout,41,13,@on --SQL:StmtCompleted,Duration

    exec sp_trace_setevent @traceidout,41,14,@on --SQL:StmtCompleted,StartTime

    exec sp_trace_setevent @traceidout,41,15,@on --SQL:StmtCompleted,EndTime

    exec sp_trace_setevent @traceidout,41,16,@on --SQL:StmtCompleted,Reads

    exec sp_trace_setevent @traceidout,41,17,@on --SQL:StmtCompleted,Writes

    exec sp_trace_setevent @traceidout,43,1,@on --SP:Completed,TextData

    exec sp_trace_setevent @traceidout,43,6,@on --SP:Completed,NTUserName

    exec sp_trace_setevent @traceidout,43,7,@on --SP:Completed,NTDomainName

    exec sp_trace_setevent @traceidout,43,8,@on --SP:Completed,HostName

    exec sp_trace_setevent @traceidout,43,10,@on --SP:Completed,ApplicationName

    exec sp_trace_setevent @traceidout,43,11,@on --SP:Completed,LoginName

    exec sp_trace_setevent @traceidout,43,18,@on --SP:Completed,CPU

    exec sp_trace_setevent @traceidout,43,26,@on --SP:Completed,ServerName

    exec sp_trace_setevent @traceidout,43,35,@on --SP:Completed,DatabaseName

    exec sp_trace_setevent @traceidout,43,12,@on --SP:Completed,SPID

    exec sp_trace_setevent @traceidout,43,13,@on --SP:Completed,Duration

    exec sp_trace_setevent @traceidout,43,14,@on --SP:Completed,StartTime

    exec sp_trace_setevent @traceidout,43,15,@on --SP:Completed,EndTime

    exec sp_trace_setevent @traceidout,43,16,@on --SP:Completed,Reads

    exec sp_trace_setevent @traceidout,43,17,@on --SP:Completed,Writes

    exec sp_trace_setevent @traceidout,44,1,@on --SP:StmtStarting,TextData

    exec sp_trace_setevent @traceidout,44,6,@on --SP:StmtStarting,NTUserName

    exec sp_trace_setevent @traceidout,44,7,@on --SP:StmtStarting,NTDomainName

    exec sp_trace_setevent @traceidout,44,8,@on --SP:StmtStarting,HostName

    exec sp_trace_setevent @traceidout,44,10,@on --SP:StmtStarting,ApplicationName

    exec sp_trace_setevent @traceidout,44,11,@on --SP:StmtStarting,LoginName

    exec sp_trace_setevent @traceidout,44,18,@on --SP:StmtStarting,CPU

    exec sp_trace_setevent @traceidout,44,26,@on --SP:StmtStarting,ServerName

    exec sp_trace_setevent @traceidout,44,35,@on --SP:StmtStarting,DatabaseName

    exec sp_trace_setevent @traceidout,44,12,@on --SP:StmtStarting,SPID

    exec sp_trace_setevent @traceidout,44,13,@on --SP:StmtStarting,Duration

    exec sp_trace_setevent @traceidout,44,14,@on --SP:StmtStarting,StartTime

    exec sp_trace_setevent @traceidout,44,15,@on --SP:StmtStarting,EndTime

    exec sp_trace_setevent @traceidout,44,16,@on --SP:StmtStarting,Reads

    exec sp_trace_setevent @traceidout,44,17,@on --SP:StmtStarting,Writes

    exec sp_trace_setevent @traceidout,45,1,@on --SP:StmtCompleted,TextData

    exec sp_trace_setevent @traceidout,45,6,@on --SP:StmtCompleted,NTUserName

    exec sp_trace_setevent @traceidout,45,7,@on --SP:StmtCompleted,NTDomainName

    exec sp_trace_setevent @traceidout,45,8,@on --SP:StmtCompleted,HostName

    exec sp_trace_setevent @traceidout,45,10,@on --SP:StmtCompleted,ApplicationName

    exec sp_trace_setevent @traceidout,45,11,@on --SP:StmtCompleted,LoginName

    exec sp_trace_setevent @traceidout,45,18,@on --SP:StmtCompleted,CPU

    exec sp_trace_setevent @traceidout,45,26,@on --SP:StmtCompleted,ServerName

    exec sp_trace_setevent @traceidout,45,35,@on --SP:StmtCompleted,DatabaseName

    exec sp_trace_setevent @traceidout,45,12,@on --SP:StmtCompleted,SPID

    exec sp_trace_setevent @traceidout,45,13,@on --SP:StmtCompleted,Duration

    exec sp_trace_setevent @traceidout,45,14,@on --SP:StmtCompleted,StartTime

    exec sp_trace_setevent @traceidout,45,15,@on --SP:StmtCompleted,EndTime

    exec sp_trace_setevent @traceidout,45,16,@on --SP:StmtCompleted,Reads

    exec sp_trace_setevent @traceidout,45,17,@on --SP:StmtCompleted,Writes

    --#################################################################################################

    --End Event definitions

    --#################################################################################################

    --#################################################################################################

    --begin filter definitions

    --#################################################################################################

    -- WHERE 1 = 1

    -- AND ApplicationName NOT LIKE N'SQL Server Profiler%'

    -- AND ApplicationName NOT LIKE N'%Quest Diagnostic Server (Monitoring)%'

    -- AND ApplicationName NOT LIKE N'%SQLAgent - Job Manager%'

    exec sp_trace_setfilter @traceidout,10,0,7, N'SQL Server Profiler%'

    exec sp_trace_setfilter @traceidout,10,0,7, N'%Quest Diagnostic Server (Monitoring)%'

    exec sp_trace_setfilter @traceidout,10,0,7, N'%SQLAgent - Job Manager%'

    --#################################################################################################

    ---end filter definitions

    --#################################################################################################

    ---final step

    --turn on the trace

    exec sp_trace_setstatus @traceidout, 1 ---start trace

    --exec sp_trace_setstatus TRACEID, 0 ---stop trace, you must know the traceid to stop it

    --exec sp_trace_setstatus TRACEID, 2 ---close trace you must know the traceid to delete it

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply