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

Unknown Traces - Keep restarting when stopped Expand / Collapse
Author
Message
Posted Thursday, July 25, 2013 12:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, June 1, 2014 10:15 PM
Points: 9, Visits: 21
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:

traceid property value
1 1 1
1 2 NULL
1 3 NULL
1 4 2013-07-26 16:00:01.107
1 5 1
3 1 1
3 2 NULL
3 3 NULL
3 4 2013-07-26 06:12:01.277
3 5 1

and:
Select * from sys.traces

which gets:

id status path max_size stop_time max_files is_rowset is_rollover is_shutdown is_default buffer_count buffer_size file_position reader_spid start_time last_event_time event_count dropped_event_count
2 1 NULL NULL 2013-07-26 06:12:01.057 NULL 1 0 0 0 1088 1 NULL 221 2013-07-25 06:12:01.120 NULL 0 0
3 1 NULL NULL 2013-07-26 06:12:01.277 NULL 1 0 0 0 1088 1 NULL 223 2013-07-25 06:12:01.207 2013-07-25 15:49:45.433 1004 0

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??
Post #1477345
Posted Thursday, July 25, 2013 12:24 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, June 12, 2014 2:04 PM
Points: 1,786, Visits: 1,014
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
Post #1477346
Posted Thursday, July 25, 2013 4:25 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, June 1, 2014 10:15 PM
Points: 9, Visits: 21
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
Post #1477793
Posted Friday, July 26, 2013 5:17 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 17, 2014 1:41 AM
Points: 170, Visits: 1,400
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
Post #1477942
Posted Friday, July 26, 2013 5:40 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:40 PM
Points: 12,897, Visits: 32,105
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1477952
Posted Sunday, July 28, 2013 5:51 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, June 1, 2014 10:15 PM
Points: 9, Visits: 21
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.
Post #1478418
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse