Re: Profiler Bug viewing trace flat file?
- From: Cranfield <alan_cranfield@xxxxxxxxx>
- Date: Fri, 1 Feb 2008 03:25:08 -0800
The Application Name filter is correct. I obfuscated it intentionally -
sorry. I need the filter as these servers are very busy at 300+ batch
requests/sec.
The TextData column is definitely populated when I query resulting trace
file through fn_trace_gettable. Here are some sample rows:
select [TextData]
from
fn_trace_gettable('g:\mssql\trace\archive\Logger_GUI_investigation_XXXXXXX_20080201.trc',0)
TextData
----------------------------------------------------------------------------------
exec PostAvailableContractsCache_sp 'CONNECT\fuuser404','31 January 2008
09:35:26'
exec PostAvailableContractsCache_sp 'CONNECT\fuuser304','31 January 2008
10:04:26'
..
..
All servers are SQL2005 SP2+.
I only want the RPC:Completed event. This works fine when the event is
selected in Profiler and run from Profiler. If I then export the trace
definition from Profiler, run the T-SQL trace, stop it, and then open the
resulting trace file in Profiler, the TextData field is not there. But its
there to view through fn_trace_gettable.
I'm not certain that SSMS will generate RPC events as a test.
As I said its not an issue for us as we load the trace files into tables but
it does expose a bug somewhere in the Profiler GUI code.
Thanks for the tip on sys.traces!
--
-- cranfield, DBA
"Kalen Delaney" wrote:
I had some problems with this script. First, you have a filter to only.
return events when the Application Name is 'myfilter', and I don't have an
application of that name. Even when I comment out that filter, I am still
getting all NULLs in the textdata column.
I noticed that the only event you are capturing is RPC:Completed. Are you
sure you are actually seeing the TextData column populated? Running a test
through SSMS will generate RPC:Completed events so I see nothing there. If I
had Batch:Completed to the list of events, with the text data column:
exec sp_trace_setevent @TraceID, 12, 1, @on
Then I can see text data just fine, both in the output from
fn_trace_gettable and the Profiler.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Cranfield" <alan_cranfield@xxxxxxxxx> wrote in message
news:630D3BC9-F968-402C-AEF8-3C48A5BE1E03@xxxxxxxxxxxxxxxx
I'm collecting from 30 servers and they're all the same. Its not a major
problem as all the trace files get loaded into SQL through
fn_trace_gettable
and there we get the TextData. I do open the odd one up through profiler
though and this is where I notice the problem.
I guess you are correct in that fn_trace_gettable may derive the Textdata
from the binary data.
Here is my code. Try and you will see:
-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5
exec @rc = sp_trace_create @TraceID output, 0, N'c:\mytrace',
@maxfilesize,
NULL
if (@rc != 0) goto error
-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 1, @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
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
exec sp_trace_setfilter @TraceID, 10, 1, 6, N'myfilter'
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler -
7412f6af-1549-44c6-bc15-4d0e9dbc9d61'
-- 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
-- stop the trace
declare @trace_id INT
select @trace_id = 0
select @trace_id = traceid
from fn_trace_getinfo (NULL)
where value = 'c:\mytrace.trc'
IF @trace_id <> 0
BEGIN
EXEC master..sp_trace_setstatus @traceid = @trace_id ,@status = 0
EXEC master..sp_trace_setstatus @traceid = @trace_id ,@status = 2
END
--
-- cranfield, DBA
"Andrew J. Kelly" wrote:
That function will return all the columns regardless of if they were in
the
original trace or not. Most will simply be null though. It may be that
the
function is getting the data from the Binary column to display in the
textdata but this sounds really weird since you say that Textdata is in
the
trace definition. Are you on the latest service pack? Have you tried
creating a new trace definition and file from scratch to see if it is
just
this one file?
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Cranfield" <alan_cranfield@xxxxxxxxx> wrote in message
news:D9A75649-5AD5-4A55-9B1A-10A668B7A3B1@xxxxxxxxxxxxxxxx
Hi Kalen
No, the TextData column is not in the properties in Profiler.
When I select from the file using fn_trace_gettable, TextData is the
first
column returned and has the data I require.
--
-- cranfield, DBA
"Kalen Delaney" wrote:
Can you look in the trace properties once you open it in Profiler to
see
if
text data is there?
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Cranfield" <alan_cranfield@xxxxxxxxx> wrote in message
news:4FC6DE47-D82F-4933-A9FE-EF34F01A3ACD@xxxxxxxxxxxxxxxx
Hello
I have recorded a Profiler trace to a flat file using the sp_trace
procs.
When I query the flat file using the fn_trace_gettable i can see the
TextData field but when I open the flat file through the Profiler
GUI I
dont
see the TextData field.
These are the events I'm logging:
-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 1, @on
exec sp_trace_setevent @TraceID, 10, 9, @on
exec sp_trace_setevent @TraceID, 10, 17, @on
exec sp_trace_setevent @TraceID, 10, 2, @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
is this a BUG or is there a way to see the Textdata field through
Profiler?
tks
--
-- cranfield, DBA
- Follow-Ups:
- Re: Profiler Bug viewing trace flat file?
- From: Andrew J. Kelly
- Re: Profiler Bug viewing trace flat file?
- References:
- Re: Profiler Bug viewing trace flat file?
- From: Kalen Delaney
- Re: Profiler Bug viewing trace flat file?
- Prev by Date: Re: database maintanance plan
- Next by Date: Re: database maintanance plan
- Previous by thread: Re: Profiler Bug viewing trace flat file?
- Next by thread: Re: Profiler Bug viewing trace flat file?
- Index(es):
Relevant Pages
|
