xSQL Profiler Take 2

Today I decided to use xSQL Profiler to help me diagnose some memory pressure/paged out problems we are facing, and boy, that was probably an error (partially my fault to trace too much perhaps)

Problems:

• I was tracing a remote server A (trace files are located on A's E drive) starting 12:30 pm
• xSQL Profiler repository is located on my computer J, that go fetch trace files from server A
• I think my computer cannot catch up to the trace files creation, and in ten minutes I have 6GB of trace files on server A and running low on space.
• I killed the xSQL Profiler hoping it would stop (at this time about 80,000 rows collected)

And....

• It did not stop!! and drove my local SQL Server memory to 1GB and slowed my laptop to a crawl
• I could not start the "xSQL Profiler" as it says it cannot connect to the database despite being there
• I could not start the "xSQL Profiler Service" as it creates 10+ traces insantly on server A again
• I tried deleting entries in the local "xSQLProfiler" database tables (such as dbo.Traces, dbo.APPLICATION_SETTINGS, dbo.TRACE_SCHEDULES....) => as soon as I started xSQL Profiler, it created the 10+ traces AGAIN.
  

Fix:

• Created the SQL script below to KILL ALL traces except the default one (I ran about 10 times this afternoon)
• Renamed the trace file directory on server A's E drive [this made it stop, finally, near 3:30 pm]
• I still cannot open up the xSQL Profiler application (cannot connect to database)
• EXTREME CAUTION for NEXT TIME
  

This is the code I ended up with, and added to my toolbox
It kills ALL Profilers trace but the default SQL trace


DECLARE @traceid INT
DECLARE curDBs CURSOR FAST_FORWARD FOR -- fast forward = read only, forward only
   --Run the following Transact-SQL statement to retrieve the list of the running trace:
   SELECT DISTINCT traceid
   --,*
   FROM ::fn_trace_getinfo(NULL)   
   WHERE traceid <> 1 -- do not kill the default SQL trace

OPEN curDBs
FETCH NEXT FROM curDBs INTO @traceid
WHILE @@FETCH_STATUS = 0
BEGIN

   --Run the following Transact-SQL statement to stop the server-side trace
   EXEC sp_trace_setstatus @traceid = @traceid , @status = 0
  
   --Run the following Transact-SQL statement to close the trace and to delete the trace information
   EXEC sp_trace_setstatus @traceid = @traceid , @status = 2

   FETCH NEXT FROM curDBs INTO @traceid
END

CLOSE curDBs
DEALLOCATE curDBs

SELECT * FROM ::fn_trace_getinfo(NULL)

RETURN