Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


view a running trace


view a running trace

Author
Message
HanShi
HanShi
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3049 Visits: 3633
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’! **
Paresh Prajapati
Paresh Prajapati
Old Hand
Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)

Group: General Forum Members
Points: 327 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
Arrow +919924626601
http://paresh-sqldba.blogspot.com/
LinkedIn | Tweet Me | FaceBook | Brijj
HanShi
HanShi
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3049 Visits: 3633
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’! **
Jack Corbett
  Jack Corbett
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11018 Visits: 14858
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
Jack Corbett
  Jack Corbett
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11018 Visits: 14858
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
Perry Whittle
Perry Whittle
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8782 Visits: 16555
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" ;-)
HanShi
HanShi
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3049 Visits: 3633
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’! **
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search