Tuesday, November 25, 2008

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

1 comment: