Suppose you want to find out how many traces are running on your instance?
No idea???here is a solution to find out no of traces, also you can stop them by below script.
To Find out the number of traces running use the below query
SELECT * FROM fn_trace_getinfo(default);
You will get below output:
You can find out the no of traces through trace ID coloumn/count.Now suppose you have to stop them then run below script:
DECLARE @TraceID int
SET @TraceID = 2
EXEC sp_trace_setstatus @TraceID, 0
EXEC sp_trace_setstatus @TraceID, 2
Replace the trace id value with one which has to be stopped.