x

Get details about an sql trace

Is there any way to figure out the host machine name from where an sql trace was originated or some other details like spid against which the trace is running ?

When I execute the following query -

SELECT *  
FROM :: fn_trace_getinfo(default)  
where property = 5 and value = 1

I get there are two sql traces running ,for the first one I am sure is the default one initiated by the sql server but not sure about the other one.

I am also not able to get any idea when I query the following -

select * 
from sys.sysprocesses  
where program_name like '%profiler%'
Regards, Hemant
more ▼

asked Feb 16 '11 at 10:18 PM in Default

Hemant gravatar image

Hemant
277 12 12 14

Are you asking which client/spid actually started a trace?
Feb 16 '11 at 11:39 PM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

I adopted the following query from @Håkan Winther great script to find ["who changed what"][1]

SELECT * 
FROM    
[fn_trace_gettable](CONVERT(VARCHAR(150), ( SELECT TOP 1
value
FROM    [fn_trace_getinfo](NULL)
WHERE   [property] = 2
)), DEFAULT) T

Credit to @Håkan Winther!

[1]: http://ask.sqlservercentral.com/questions/21094/how-to-find-which-user-dropped-a-table-in-a-database
more ▼

answered Feb 16 '11 at 10:54 PM

Cyborg gravatar image

Cyborg
10.6k 36 39 45

This query will not return any details about those traces where file name property is not available.Even if this file name property is available for a trace how would I retrieve the session id against which this particular trace process is running or say host name from where the trace was originated ?

Thanks, Hemant
Feb 17 '11 at 02:03 AM Hemant
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x34
x18

asked: Feb 16 '11 at 10:18 PM

Seen: 1097 times

Last Updated: Feb 16 '11 at 11:34 PM