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