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)