SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Unknown Traces - Keep restarting when stopped


Unknown Traces - Keep restarting when stopped

Author
Message
kellie.jenkins
kellie.jenkins
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
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??
Jayanth_Kurup
Jayanth_Kurup
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2917 Visits: 1351
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
kellie.jenkins
kellie.jenkins
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
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
SQLCharger
SQLCharger
SSC Veteran
SSC Veteran (262 reputation)SSC Veteran (262 reputation)SSC Veteran (262 reputation)SSC Veteran (262 reputation)SSC Veteran (262 reputation)SSC Veteran (262 reputation)SSC Veteran (262 reputation)SSC Veteran (262 reputation)

Group: General Forum Members
Points: 262 Visits: 1422
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
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28409 Visits: 39963
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
kellie.jenkins
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
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.
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