Finding Default and User Traces running in SQL Server

I have heard one issue from my manager saying one of SQL instance is taking almost all memory, what is causing this high load.

I starting thinking what can create this load, no SQL jobs running at this time. Then I got light which is any of developer running SQL trace to fix performance issues as it is non-production server.

I was searching to find the trace details, first query I used and got the default trace information. I thought it was the user trace and tried to stop that. But It gives me error message saying, you cannot kill default trace use sp_configure to change the status. I started searching again and found the second query, it is right one to find all user traces.

USE MSDB

SELECT * FROM [sys].[fn_trace_getinfo](DEFAULT)

declare @sp_who2 table (

    SPID    int,

    status  varchar(50),

    login   varchar(100),

    HostName    varchar(100),

    BlkBy   varchar(100),

    DBName  varchar(100),

    Command varchar(100),

    CPUTime int,

    DiskIO  int,

    LastBatch   varchar(50),

    ProgramName varchar(150),

    SPID2   int,

    REQUESTID   int

)

insert  @sp_who2

exec sp_who2

select  *

from    @sp_who2

where   ProgramName like 'SQL Server Profiler - %'