Unknown Traces - Keep restarting when stopped

Question

Post reply

Unknown Traces - Keep restarting when stopped

kellie.jenkins

Old Hand

Points: 345
More actions
July 25, 2013 at 12:14 am

#280564

We have an application that detects when tracing is enabled on our SQL server and it has been requested that the tracing is disabled to attempt to troubleshoot an issue with the application.
I've run:
SELECT *
FROM fn_trace_getinfo(default);
GO
and getting:
traceidpropertyvalue
111
12NULL
13NULL
142013-07-26 16:00:01.107
151
311
32NULL
33NULL
342013-07-26 06:12:01.277
351
and:
Select * from sys.traces
which gets:
idstatuspathmax_sizestop_timemax_filesis_rowsetis_rolloveris_shutdownis_defaultbuffer_countbuffer_sizefile_positionreader_spidstart_timelast_event_timeevent_countdropped_event_count
21NULLNULL2013-07-26 06:12:01.057NULL100010881NULL2212013-07-25 06:12:01.120NULL00
31NULLNULL2013-07-26 06:12:01.277NULL100010881NULL2232013-07-25 06:12:01.2072013-07-25 15:49:45.43310040
When I run:
Exec sp_trace_setstatus @traceId=2,@Enabled=0 and Exec sp_trace_setstatus @traceId=2,@Enabled=2
to stop and remove the trace, it just starts up again automatically.
I've checked if there are any jobs running under the SQL server agent but there is only the syspolicy_purge_history job and its history is not showing it running.
Is there anything else to check to try and find what is running these traces??

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 1

if your trying to stop the default trace you need to use sp_configure > default trace enabled (0)

also the first query doesnt show a trace id 2 so ar you sure your stopping the right trace.

Jayanth Kurup[/url]

kellie.jenkins Old Hand Points: 345 More actions · Answer 2

I had re-run the first query after I the one to stop the trace and so it restarted as trace id 1. It was originally trace id 2.

When I run the default trace command all I get is : 'Configuration option 'default trace enabled' changed from 0 to 0. Run the RECONFIGURE statement to install.' and the number of traces does not change

SQLCharger SSCommitted Points: 1928 More actions · Answer 3

kellie.jenkins (7/25/2013)
...
Is there anything else to check to try and find what is running these traces??

Might be worth checking for Stored Procs marked as 'startup' 😉

alternatively run sp_configure to disable them for troubleshooting purposes.

Cheers,

JohnA

MCM: SQL2008

Lowell SSC Guru Points: 323504 More actions · Answer 4

note that the PATH variable is null?

I'm sure that means because someone is actively using SQL Profiler to run the trace live; Profiler will instantly recreate the trace if it were stopped or dropped.

I'm sure you've heard that that can have an impact when compared to a server side trace, so you need to get whoever needs that trace to script it out instead.

run sp_who2 and see which machine is running any ProgramName starting with "SQL Server Profiler"

mine says SQL Server Profiler - df2195da-7702-4117-9837-ecfb5ebd7239 for one i just started as the proof of concept.

as far as i know, a scripted trace always has a file name; a profiler traces has a path that is null, because iut's being redirected to the applicaiton on a desktop , and not to a file on the server.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

kellie.jenkins Old Hand Points: 345 More actions · Answer 5

When I've run sp_who2, there is nothing running with the programname "SQL Server Profiler", but I have found that there are entries with SQL Trace. These seem to be coming from our Sharepoint server with the main sharepoint service account but running on the master DB rather than any of the sharepoint databases - this account is not activey logged in, but services are running using the account.