how to find SQL trace and stop it

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/
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

marrowyungSenior Technical architecture (Data)Author Commented:
must restart after changing configuration ?
Vitor MontalvãoMSSQL Senior EngineerCommented:
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

dbaSQLCommented:
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
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

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.
dbaSQLCommented:
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ãoMSSQL Senior EngineerCommented:
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 ?
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.