How to find the source of the SQL Profiler traces

Category: sql server tools

Question

Manuel Avil├ęs on Tue, 29 Jan 2013 16:27:46


Hi,

I have lots of files of SQL Profiler in a SQL Server volume and I don't know which server is generating this files.

SQL Profiler is not running in the same server where the files are created.

Is there any way to find out which server has the Profiler activated?

Thanks,

Replies

Kalman Toth on Tue, 29 Jan 2013 21:09:42


Is it Profiler or server-side (silent) tracing?

Article on server-side tracing:

http://sqlserverpedia.com/wiki/The_Server-side_Trace:_What,_Why,_and_How

Stopping server-side trace:

http://support.microsoft.com/kb/822853

Hima Nagisetty on Tue, 29 Jan 2013 21:19:52


Open the trace file and look at the server name that is being traced. Then connect to that and run sp_who2. In the results of sp_who2 you should be able to find a row where the  value in 'ProgramName' column starts with 'SQL Server Profiler - ...'. Look at the Host Name against it and you should be able to find the machine from which the profiler is being run.

Also, it isn't necessary to start SQL server profiler to get profiler traces. They can also be taken by running server side trace with code similar to the following

/****************************************************/
/* Created by: SQL Server 2008 R2 Profiler          */
/* Date: 01/29/2013  03:18:47 PM         */
/****************************************************/
-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5
-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share
exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, NULL 
if (@rc != 0) goto error
-- Client side File and Table cannot be scripted
-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 14, 1, @on
exec sp_trace_setevent @TraceID, 14, 9, @on
exec sp_trace_setevent @TraceID, 14, 6, @on
exec sp_trace_setevent @TraceID, 14, 10, @on
exec sp_trace_setevent @TraceID, 14, 14, @on
exec sp_trace_setevent @TraceID, 14, 11, @on
exec sp_trace_setevent @TraceID, 14, 12, @on
exec sp_trace_setevent @TraceID, 15, 15, @on
exec sp_trace_setevent @TraceID, 15, 16, @on
exec sp_trace_setevent @TraceID, 15, 9, @on
exec sp_trace_setevent @TraceID, 15, 17, @on
exec sp_trace_setevent @TraceID, 15, 6, @on
exec sp_trace_setevent @TraceID, 15, 10, @on
exec sp_trace_setevent @TraceID, 15, 14, @on
exec sp_trace_setevent @TraceID, 15, 18, @on
exec sp_trace_setevent @TraceID, 15, 11, @on
exec sp_trace_setevent @TraceID, 15, 12, @on
exec sp_trace_setevent @TraceID, 15, 13, @on
exec sp_trace_setevent @TraceID, 17, 1, @on
exec sp_trace_setevent @TraceID, 17, 9, @on
exec sp_trace_setevent @TraceID, 17, 6, @on
exec sp_trace_setevent @TraceID, 17, 10, @on
exec sp_trace_setevent @TraceID, 17, 14, @on
exec sp_trace_setevent @TraceID, 17, 11, @on
exec sp_trace_setevent @TraceID, 17, 12, @on
exec sp_trace_setevent @TraceID, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 9, @on
exec sp_trace_setevent @TraceID, 10, 17, @on
exec sp_trace_setevent @TraceID, 10, 2, @on
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 18, @on
exec sp_trace_setevent @TraceID, 10, 11, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 13, @on
exec sp_trace_setevent @TraceID, 10, 6, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 9, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 6, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 18, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on
exec sp_trace_setevent @TraceID, 13, 1, @on
exec sp_trace_setevent @TraceID, 13, 9, @on
exec sp_trace_setevent @TraceID, 13, 6, @on
exec sp_trace_setevent @TraceID, 13, 10, @on
exec sp_trace_setevent @TraceID, 13, 14, @on
exec sp_trace_setevent @TraceID, 13, 11, @on
exec sp_trace_setevent @TraceID, 13, 12, @on
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - 2a6401c6-5dd1-472a-ae7b-5335b4bfce76'
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1
-- display trace id for future references
select TraceID=@TraceID
goto finish
error: 
select ErrorCode=@rc
finish: 
go


Hima, To help the community better identify relevant thread, please don't forget to mark as answer or vote as helpful if and answer helps.


Dan Guzman on Wed, 30 Jan 2013 02:25:12


Hi,

I have lots of files of SQL Profiler in a SQL Server volume and I don't know which server is generating this files.

SQL Profiler is not running in the same server where the files are created.

Is there any way to find out which server has the Profiler activated?

Thanks,

You can query sys.traces on a server to find out what traces are running.  Profiler traces will have is_rowset value 1 whereas server-side traces will have the value 0.  Normally, you will see at least the default trace (is_default = 1), which is enabled by default.

Waqas M on Thu, 31 Jan 2013 12:12:11


Hi,

You can use following to find out whether there is a profiler trace running or not 

 

SELECT * FROM sys.fn_trace_getinfo(0) 

You want to know if any profiler is running against to SQL Server, you can check the sys.dm_exec_sessions DMV as following

 

SELECT * 
FROM sys.dm_exec_sessions
WHERE program_name LIKE 'SQL Server Profiler%'