February 2, 2009 at 11:12 pm
Hi,
I have few queries reagarding active trace files:
For a given trace file whose trace id is not known, is there any way/query to determine that the trace is active or not?
OR
For a given trace file, is there any way/query to determine that the trace id of that trace file?
Pls respond asap.
Thanks in advance,
Shivani
February 2, 2009 at 11:23 pm
execute:
SELECT * FROM fn_trace_getinfo(0);
the argument '0' will return information about all active traces.
property column value '2' gives the logical file name. From the result you can determine the traceID of an active trace file.
February 2, 2009 at 11:45 pm
Thanks Krishna.
But when I generate a trace file using SQL Sever Profiler, I don't get to know the trace id of that file.
Moreover , when I execute above command , I get no info about the trace file that I created through SQL Server Profiler and which is active yet.
February 3, 2009 at 12:46 am
I see that it does not give the name of the file. I found a great resource which satisfies your requirement:
http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm
vyas got an awesome stored procedure created, just execute it with the filename of the trace and it will give you the traceid. I have run it on my machine. I think this is what your looking for.
Execute the stored procedure as below after you have created the sp:
DECLARE @TraceID int
EXEC CreateTrace
'filepath ',
@OutputTraceID = @TraceID OUT
February 3, 2009 at 1:27 am
Well Krishna, This is not exactly what I am looking for.
Actually my problem is:
If only the path of a trace file is given to me and that trace file is created by using SQL Server Profiler, then how can I ascertain whether this trace file is active or not ?
February 3, 2009 at 2:17 am
am being stupid. This is what happens if i sit without a cup of coffee:P
try this :
SELECT * FROM sys.traces WHERE path='file-path'
if status=0 which means stopped
=1 which means running
hope this should solve
February 3, 2009 at 2:57 am
This still does not solve my problem 🙁
This table does not have entry for the active trace file which has been generated using SQL Server Profiler.
I think SQL Server does create a trace id for the trace file which is created using SQL Server Profiler. But the value of path column of sys.traces table is NULL.
February 3, 2009 at 3:44 am
shivani.suri2 (2/3/2009)
This table does not have entry for the active trace file which has been generated using SQL Server Profiler.
'STATUS' COLUMN
I think SQL Server does create a trace id for the trace file which is created using SQL Server Profiler. But the value of path column of sys.traces table is NULL.
'ID' COLUMN
it runs on mine perfectly displaying pathname, status column.
If this still doesnt help you lets wait to whether someone can come up with a better solution
February 3, 2009 at 7:41 am
shivani.suri2 (2/3/2009)
But the value of path column of sys.traces table is NULL.
The path will be NULL because, according to SQL, it's not writing the trace to a file, it's streaming the trace data to another application, in this case, SQL profiler. The status and ID will be visible.
You'll see a path if the trace is created with an output file, which is not the way profiler does it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 5, 2009 at 7:57 pm
Need a suggestion...
If the system time is equal to last modified timestamp of the trace file , then can we assume that the trace file is active?
February 5, 2009 at 11:43 pm
shivani.suri2 (2/5/2009)
Need a suggestion...
As has been mentioned several times, use the status column. That is populated even for traces running through profiler. The status will tell you if the trace is running or not.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 18, 2009 at 10:23 pm
I was looking for a way to know who was running a trace and if it was still active or not. I came across this post and it gave me an idea to see who had kicked off a trace. Let me know if this works for you. It uses the sys.trace mentioned in the post and will tell you all of the traces out there (at least from what I see in the getinfo results) and uses sysprocesses to let me know if can be tied to a person/process.
SELECT CASE WHEN t.status = 1 THEN 'Active trace' ELSE 'Inactive trace' END,
CASE WHEN t.path IS NULL THEN 'Not outputted to a file' ELSE t.path END,
t.max_size,
t.stop_time,
t.max_files,
t.is_rowset,
t.is_rollover,
t.is_shutdown,
CASE WHEN t.is_default = 1 THEN 'System default trace' ELSE 'User trace' END,
t.start_time,
t.last_event_time,
s.status,
s.hostname,
nt_username
FROM sys.traces t
LEFT JOIN sys.sysprocesses s ON t.reader_spid = s.spid
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply