Today, while doing query or performance analysis, I wanted to start a profiler trace on the sql server in question. But since profiler generates network traffic I was looking for another approach. I learned that we can start the trace using system stored procedures. But I went a bit further and used a sql agent job to schedule the start of the trace. To monitor the performance of an instance of SQL Server, we can trace the SQL Server events by using a server-side trace.

To create the definition for the server-side trace, we use the sptracecreate system stored procedure. To start the trace, we use the sptracesetstatus system stored procedure.

exec sp_trace_setstatus @TraceID, 1

Now that trace has already started I had to stop it once I got the required information. In order to manually stop the server-side trace, we must locate the traceid trace event, stop the trace, and then delete the trace definition from the instance of SQL Server.

I followed these steps,

Run the below Transact-SQL statement in SSMS to retrieve the list of the running trace

SELECT * FROM ::fn_trace_getinfo(DEFAULT)

Got the traceid of the server-side trace that I had to stop and ran the following statement,

EXEC sp_trace_setstatus @traceid = <traceid> , @status = 0

where <traceid> is the traceid noted above

Finally executed the following Transact-SQL statement to close the trace and to delete the trace information.

EXEC sp_trace_setstatus @traceid = <traceid> , @status = 2

where <traceid> is the traceid noted above

After we start a server-side trace, the trace continues to run and generates output until we stop the trace.

Published under: SQL Server Tips ·  ·  ·  ·