Monday, December 12, 2016

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 - %'

No comments:

Post a Comment