December 18, 2008 at 1:19 am
Hi all,
I am running a trace with Profiler for a while now to monitor usage of certain SP's. I've created the trace without saving the results to a file. Because my remote desktop session was reset, the profiler session was closed without stopping the trace. Now I want to see what the trace has logged untill now. Is it possible to 're-connect' with Profiler to the running trace?
Kind regards,
Hans
December 18, 2008 at 1:26 am
U can not reconnect it, but u can find it and stop it
select * from fn_trace_getinfo(0)
u can find traceid and to stop run below:
sp_trace_setstatus [ @traceid = ] trace_id , [ @status = ] status
0 - Stops the specified trace.
1 - Starts the specified trace.
2 - Closes the specified trace and deletes its definition from the server.
December 18, 2008 at 1:41 am
Thanks for the reply Paresh.
But when I stop the trace, I guess I won't be able to see the events logged by the trace. Is there any possibility to see these events?
December 18, 2008 at 8:41 am
When Profiler is shut down normally the trace is stopped and deleted from the system. Are you sure you the trace is running? I really doubt it.
Run Select * From sys.traces and you should see the default trace, id = 1 and any others that are defined. The one from profiler will have a NULL path and is_rowset should be 1.
If it is running there is no way to attach to it that I am aware of so you are out of luck regardless.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 18, 2008 at 8:45 am
If you want to be able to see the data later and have less impact on your server you should run a server-side trace saved to a file. You can define the trace in Profiler and then export the definition to a script file and then do minor mod's on the script to create a server-side trace. You can get the details from BOL - server trace. Then you can either open the file in PRofiler or query it using fn_trace_gettable(Path, no of files)
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 18, 2008 at 10:32 am
set up a server side trace using the following
SP_TRACE_CREATE
SP_TRACE_SETEVENT
SP_TRACE_SETFILTER
SP_TRACE_SETSTATUS
also the functions
fn_trace_gettable
fn_trace_getinfo
to generate a trace definition use the following syntax
declare @traceidout int
declare @maxfilesize bigint
declare @on bit
set @on = 1
set @maxfilesize = 50 --size in MB's
exec sp_trace_create @traceidout output, 2, N'D:\Trace\mytrace', @maxfilesize, NULL
exec sp_trace_setevent @traceidout, 12, 1, @on
exec sp_trace_setevent @traceidout, 12, 3, @on
change @maxfilesize to whatever value you require (its in MB)
Dont append the .TRC to the path above it does it for you.
refer to BOL for all trace events and columns
set a filter using the following syntax
exec sp_trace_setfilter @traceidout, 3, 0, 0, 7
use the following to get your trace details and ID
select * from ::fn_trace_getinfo(default)
use the following to start, stop and close the trace
(must stop a trace before it can be closed.)
exec sp_trace_setstatus TRACEID, 1 --start trace
exec sp_trace_setstatus TRACEID, 0 --stop trace
exec sp_trace_setstatus TRACEID, 2 --close trace
finally to output to a table, stop and close the trace then use the following syntax
SELECT * INTO temp_tablename
FROM ::fn_trace_gettable('d:\trace\mytrace.trc', default)
You can create SQL jobs and schedule them using the code above. As it runs server
side there are no I\O nasties that a client would generate and you can schedule it at will
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
December 18, 2008 at 10:46 am
Thanks for all the replies...
I've stopped my trace and created a new one, now with saving results to a file...
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply