Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

view a running trace Expand / Collapse
Author
Message
Posted Thursday, December 18, 2008 1:19 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:20 AM
Points: 2,482, Visits: 3,025
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


** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Post #621835
Posted Thursday, December 18, 2008 1:26 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, June 18, 2013 7:41 AM
Points: 323, Visits: 464
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.



_____________________________________________________________________________________________________________
Paresh Prajapati
+919924626601
http://paresh-sqldba.blogspot.com/
LinkedIn | Tweet Me | FaceBook | Brijj
Post #621841
Posted Thursday, December 18, 2008 1:41 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:20 AM
Points: 2,482, Visits: 3,025
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?


** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Post #621847
Posted Thursday, December 18, 2008 8:41 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:16 PM
Points: 10,381, Visits: 13,441
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #622173
Posted Thursday, December 18, 2008 8:45 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:16 PM
Points: 10,381, Visits: 13,441
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #622178
Posted Thursday, December 18, 2008 10:32 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:41 PM
Points: 6,760, Visits: 14,413
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"
Post #622304
Posted Thursday, December 18, 2008 10:46 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:20 AM
Points: 2,482, Visits: 3,025
Thanks for all the replies...
I've stopped my trace and created a new one, now with saving results to a file...


** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Post #622322
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse