May 10, 2012 at 8:06 am
Using Profiler I created a trace (see below) but for some reason it is comming up NULL for key things. So the below statement (found in the TextData) is missing things that I would expect to be in the trace data like DatabaseID, Duration, EndTime, Reads, Writes, CPU, DatabaseName. Is there something special that needs to be done so the trace picks up this data? (in addition to what it is capturing.. TextData, NTUserName, ApplicationName, LoginName, StartTime, ServerName, EventClass)
/****************************************************/
/* Created by: SQL Server Profiler 2005 */
/* Date: 05/10/2012 10:05:10 AM */
/****************************************************/
-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5
-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share
exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, NULL
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, 14, 1, @on
exec sp_trace_setevent @TraceID, 14, 9, @on
exec sp_trace_setevent @TraceID, 14, 6, @on
exec sp_trace_setevent @TraceID, 14, 10, @on
exec sp_trace_setevent @TraceID, 14, 14, @on
exec sp_trace_setevent @TraceID, 14, 11, @on
exec sp_trace_setevent @TraceID, 14, 12, @on
exec sp_trace_setevent @TraceID, 15, 15, @on
exec sp_trace_setevent @TraceID, 15, 16, @on
exec sp_trace_setevent @TraceID, 15, 9, @on
exec sp_trace_setevent @TraceID, 15, 13, @on
exec sp_trace_setevent @TraceID, 15, 17, @on
exec sp_trace_setevent @TraceID, 15, 6, @on
exec sp_trace_setevent @TraceID, 15, 10, @on
exec sp_trace_setevent @TraceID, 15, 14, @on
exec sp_trace_setevent @TraceID, 15, 18, @on
exec sp_trace_setevent @TraceID, 15, 11, @on
exec sp_trace_setevent @TraceID, 15, 12, @on
exec sp_trace_setevent @TraceID, 17, 12, @on
exec sp_trace_setevent @TraceID, 17, 1, @on
exec sp_trace_setevent @TraceID, 17, 9, @on
exec sp_trace_setevent @TraceID, 17, 6, @on
exec sp_trace_setevent @TraceID, 17, 10, @on
exec sp_trace_setevent @TraceID, 17, 14, @on
exec sp_trace_setevent @TraceID, 17, 11, @on
exec sp_trace_setevent @TraceID, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 9, @on
exec sp_trace_setevent @TraceID, 10, 17, @on
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 18, @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, 6, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 9, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 6, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 18, @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, 13, 12, @on
exec sp_trace_setevent @TraceID, 13, 1, @on
exec sp_trace_setevent @TraceID, 13, 9, @on
exec sp_trace_setevent @TraceID, 13, 6, @on
exec sp_trace_setevent @TraceID, 13, 10, @on
exec sp_trace_setevent @TraceID, 13, 14, @on
exec sp_trace_setevent @TraceID, 13, 11, @on
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - 57f689a6-67c3-475f-91e3-07f20274587c'
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1
-- display trace id for future references
select TraceID=@TraceID
goto finish
error:
select ErrorCode=@rc
finish:
go
May 10, 2012 at 8:55 am
Reading through your code you have picked and chosen what columns for each event you want to trace. I noticed that some of the fields you wanted were not included in what you selected. You have two choices when you setup a trace on an event, you can take ALL columns or just the columns you specify. You have gone down the latter.
I suggest either adding the columns you want, or specifying no columns at all and sending NULL for the @columnid in sp_trace_setevent. Read up in BOL for sp_trace_setevent, near the bottom, there is a table shown in the @on section that discusses this..
CEWII
May 10, 2012 at 9:06 am
In addition to what Elliot said, some of the events in your trace will not return all columns;
if you run this query (change the patch to match your path to the trace files), for example, where i joined it so you can see the Eventclass, and you can see events like these two:
RPC:Completed
Audit Logout
will not have TextData; I'd call that perfectly normal.
SELECT TE.name,
T.*
FROM ::fn_trace_gettable('c:\Data\MyTrace4.trc', default) T
INNER JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
i ran your trace thru my ScriptAnyTrace proc, which adds the descriptive events and column names:
for me, i really need the descriptiosn to visualize what the trace is supposed to be doing.
--#################################################################################################
--Scripting trace_id 2 from server DEV223
-- Trace Last Started/Restarted on May 10 2012 10:50:53:930AM
-- Scripted for Analysis on May 10 2012 11:05:16:623AM
--#################################################################################################
--declare variables for parameterizing the command
declare @traceidout int
declare @options int
declare @path nvarchar(256)
declare @maxfilesize bigint
declare @maxRolloverFiles int
declare @stoptime 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 @maxfilesize = 5 --size in MB
set @maxRolloverFiles = 1 --number of files; ie if 5 files, start rewriting on rollover
set @stoptime = NULL -- null if never ends, else a specific date
set @options = 0 -- TRACE_FILE_ROLLOVER = FALSE, SHUTDOWN_ON_ERROR = FALSE
set @path = 'c:\Data\MyTrace4' -- the trace adds ".trc" to the pathname, so avoid "name.trc.trc" by removing it for scripting
--#################################################################################################
--create the trace
exec sp_trace_create @traceidout output, @options, @path, @maxfilesize, @stoptime, @maxRolloverFiles
--#################################################################################################
--Begin Event definitions
--#################################################################################################
exec sp_trace_setevent @traceidout,10,6,@on --RPC:Completed,NTUserName
exec sp_trace_setevent @traceidout,10,9,@on --RPC:Completed,ClientProcessID
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,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,10,18,@on --RPC:Completed,CPU
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,9,@on --SQL:BatchCompleted,ClientProcessID
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,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,12,18,@on --SQL:BatchCompleted,CPU
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,9,@on --SQL:BatchStarting,ClientProcessID
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,12,@on --SQL:BatchStarting,SPID
exec sp_trace_setevent @traceidout,13,14,@on --SQL:BatchStarting,StartTime
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,9,@on --Audit Login,ClientProcessID
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,12,@on --Audit Login,SPID
exec sp_trace_setevent @traceidout,14,14,@on --Audit Login,StartTime
exec sp_trace_setevent @traceidout,15,6,@on --Audit Logout,NTUserName
exec sp_trace_setevent @traceidout,15,9,@on --Audit Logout,ClientProcessID
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,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,15,18,@on --Audit Logout,CPU
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,9,@on --ExistingConnection,ClientProcessID
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,12,@on --ExistingConnection,SPID
exec sp_trace_setevent @traceidout,17,14,@on --ExistingConnection,StartTime
--#################################################################################################
--End Event definitions
--#################################################################################################
--#################################################################################################
--begin filter definitions
--#################################################################################################
-- WHERE 1 = 1
-- AND ApplicationName NOT LIKE N'SQL Server Profiler - 57f689a6-67c3-475f-91e3-07f20274587c'
exec sp_trace_setfilter @traceidout,10,0,7, N'SQL Server Profiler - 57f689a6-67c3-475f-91e3-07f20274587c'
--#################################################################################################
---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
May 10, 2012 at 9:21 am
Lowell is correct that not all fields will be filled in even if they are included.
CEWII
May 10, 2012 at 10:51 am
Ok.. Thanks for your help. I guess you get all columns in the output file, even though they were not in the set trace command.
May 10, 2012 at 11:49 am
you won't get all columns by default unless you have selected the option for show all columns , the important thing to remember here is that the columns which get populated have some relevance to the event which is generating they data. you can see this by simply choosing some of the predefined template and looking for the cloumns which ticks in them when running profiler.
Another thing to remember is that filters on columns which do not generate data are ignored.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply