rowset trace - Anyone knows where it hides?

Question

rowset trace - Anyone knows where it hides?

paulaalkz 9229

Old Hand

Points: 334
More actions
February 19, 2014 at 1:19 pm

#396292

Hello. querying sys.traces I found out today that Spotlight Quest is running two rowset traces. I can view the events and columns it is capturing but I wonder if I can view the data? It is not stored to a file, path is NULL thus rowset trace.
One trace is for query perf and another for deadlocks. I wanted to read the deadlocks info. Anyone knows where it hides?
I already read:
http://technet.microsoft.com/en-us/library/cc293613.aspx
http://www.sqlservercentral.com/articles/Stairway+Series/72465/

Viewing 4 posts - 1 through 3 (of 3 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

paulaalkz 9229 (2/19/2014)
Hello. querying sys.traces I found out today that Spotlight Quest is running two rowset traces. I can view the events and columns it is capturing but I wonder if I can view the data? It is not stored to a file, path is NULL thus rowset trace.
One trace is for query perf and another for deadlocks. I wanted to read the deadlocks info. Anyone knows where it hides?
I already read:
http://technet.microsoft.com/en-us/library/cc293613.aspx
http://www.sqlservercentral.com/articles/Stairway+Series/72465/

as i understand it, when the PATH is NULL, it's always an active profiler trace that has created it, and is currently running. Apparently spotlight does the same thing!

I've got a handy procedure i've posted here that will script out any trace with a ton of detail about the trace itself, and you'll probably see the one of the filters will be to avoid tracing commands from profiler itself.

sp_ScriptAnyTrace.txt

so if your traces have id 2 and id 3, this should script them out for you, so you can make them permanent

EXEC sp_ScriptAnyTrace 2

EXEC sp_ScriptAnyTrace 3

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!

Lowell SSC Guru Points: 323518 More actions · Answer 2

now if you want to view the trace results, i like to create a view for each trace: unfortunately, i think it has to have a path variable to be able to trace.

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

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

SET @cmd='

CREATE VIEW VW_Trace'

+ RIGHT('00' + CONVERT(varchar,@TraceIDToReview),2)

+ '

AS

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'

print @cmd

IF EXISTS(select * from master.dbo.sysobjects where xtype='V' and name= 'VW_Trace' + RIGHT('00' + CONVERT(varchar,@TraceIDToReview),2))

BEGIN

SET @cmd = REPLACE(@cmd,'CREATE VIEW','ALTER VIEW')

print @cmd

END

print @cmd

--exec(@cmd)

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!

paulaalkz 9229 Old Hand Points: 334 More actions · Answer 3

Hey thanks for your replies and trying to help but this rowset trace is truly a mystery as the default trace does have a path and continuously run. So although your scripts are great they don't apply here.:-)