how to find SQL trace and stop it

marrowyung
marrowyung used Ask the Experts™
on
how to find SQL trace and stop it as we found someone left created this and ran for months.

this impact our production platform.

I found these link but seems no much information on how to stop it

https://blogs.msdn.microsoft.com/anthonybloesch/2012/12/14/finding-and-stopping-rogue-sql-traces/
https://mssqlwiki.com/2010/04/26/how-to-find-all-the-profiler-traces-running-on-my-sql-server/
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
marrowyungSenior Technical architecture (Data)

Author

Commented:
must restart after changing configuration ?
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Did the first query return records?
select T.id as [Id],
       case T.status when 0 then N’stopped’ else N’running’ end as [Status],
       T.path as [Path],
       case T.is_rowset when 0 then N’false’ else N’true’ end as [Rowset],
       case T.is_shutdown when 0 then N’disabled’ else N’enabled’ end as [Shutdown option],
       T.start_time as [Start],
       T.stop_time as [Stop]
from sys.traces as T
where T.is_default <> 1;

Open in new window

Commented:
I would think all you really need to do is run sp_who2, and look at the spid where the ProgramName is something like 'SQL Server Profiler'.  Then you look at the HostName  and the Login, and you'll know what workstation it is being run on, and under what login.

EXEC sp_who2
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

marrowyungSenior Technical architecture (Data)

Author

Commented:
dbaSQL,

what is who2 don't show this. recall the C2 audit also show nothing but leave the SQL server in non operational state. very low CPU but SQL server very slow.
Commented:
If a trace is running, the who2 should capture it.  It will show all sessions that are currently connected to the instance.  Why don't you try it with the active flag --

EXEC sp_who2 active
marrowyungSenior Technical architecture (Data)

Author

Commented:
dbaSQL,

it can't ! just like C2 audit, nothing will be show up and we have to stop it.

it is funny that once I tell some senior management about that, 80% of the trace stopped. and they said nothing and nothing has been turned on.

one of the rest remaining is from Idera SQL DM, it will just give one try each time.
marrowyungSenior Technical architecture (Data)

Author

Commented:
how to stop the trace once found ?
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Did you see my comment? Never had a feedback about it.
marrowyungSenior Technical architecture (Data)

Author

Commented:
hi victor sorry about that as I am focusing on my google result and I don't feel good yesterday.

yeah I ran that, it is funny to hear that once I told my senior manager, a lot of trace gone ! they keep saying no.

now only one line which shows it is the SQL DM SW from Idera do this ! but this one disappear once the DM SW done getting information from that box..

so that queries now just return one line for us (previously 6 lines), and that line will come in and out from time to time as it is from our DM box which keep monitoring everything.

what I want to know is, if we found one how can we stop it and even disable it forever ?
IT Engineer
Distinguished Expert 2017
Commented:
what I want to know is, if we found one how can we stop it and even disable it forever ?
It's all in your first link that you own posted:
execute sp_trace_setstatus
     @traceid = @id,
     @status = 0;
     
    execute sp_trace_setstatus
     @traceid = @id,
     @status = 2;


status = 0, stop the trace;
status = 2, close and delete the trace.
marrowyungSenior Technical architecture (Data)

Author

Commented:
tks all.
marrowyungSenior Technical architecture (Data)

Author

Commented:
sorry holiday here.
marrowyungSenior Technical architecture (Data)

Author

Commented:
tks all.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial