Let's say the DBA ran a trace on a server and left for a long lunch, and the client server where the profiler is running is suffering badly with poor performance on the applications running on it and someone in the admin team want to kill that trace even in the absence of the DBA.
Running the following will give you the status of running traces. You may see one running, usually traceid of 1, this is a system trace which needs to stay running:
SELECT * FROM ::fn_trace_getinfo(0) ;
GO
Once you've found your trace, you need to stop it: 20 being the TraceID and 0 being the command to stop it.
EXEC sp_trace_setstatus 20, 0
