CODEWITHSUNDEEP
sql-serverprofiler
Omtechguy
Omtechguy

Reputation: 3651

Closing Profiler without stop it first

Closing Microsoft SQL Profiler without stop it first. By doing that, is it keeping the process alive and affecting the SQL performance?

I am asking this because I am wondering why the utility warns me when I am trying to do so.

Upvotes: 2

Views: 3188

Answers (3)

Fandango68
Fandango68

Reputation: 4868

As mentioned using Trace is somewhat old-school, slows down the server (depending on what you're tracing), very difficult to pin-point events that are at the crux of your problem, and there are better ways now (post 2012).

I suggest EE, if you don't have SQL Server Enterprise edition (cannot do proper Audits down to table/row level), which was my issue.

https://learn.microsoft.com/en-us/sql/relational-databases/extended-events/quick-start-extended-events-in-sql-server?view=sql-server-ver16

But to answer your question, I am sure closing down SQL Profiler only stops a trace. It doesn't delete it.

You can see what's running by:

--List all SQL Traces 
SELECT * FROM sys.traces

And then re-start the trace directly:

EXEC sp_trace_setstatus 2,1 --where '1' is Start, '0' is stop and '2' is delete it

Upvotes: 0

anon
anon

Reputation:

Yes, the trace is still running if you close the tool without stopping it. If you've paused the trace, it's not running, but it still exists on the server. Much cleaner to stop it and clean up after yourself.

In any case, you should try to avoid using Profiler anyway, especially against production. Use a server-side trace, extended events or a 3rd party tool.

Upvotes: 3

andyd_28
andyd_28

Reputation: 161

For any future references - according to MS https://learn.microsoft.com/en-us/sql/tools/sql-server-profiler/close-a-trace-window-sql-server-profiler closing the trace window stops the associated trace.

Upvotes: 4

Related Questions