How To: Stop a Server-Side Trace in SQL Server 2000 (822853)


The information in this article applies to:

  • Microsoft SQL Server 2000 (all editions)

SUMMARY

In Microsoft SQL Server 2000, you can trace SQL Server events by using a server-side trace that is performed by system stored procedures or by using a client-side trace that is performed by SQL Profiler. After you start a server-side trace, the trace continues to run and generates output until you stop the trace. This article describes how to stop a server-side trace.

MORE INFORMATION

To monitor the performance of an instance of SQL Server, you can trace the SQL Server event by using a server-side trace. To create the definition for the server-side trace, use the sp_trace_create system stored procedure. To start the trace, use the sp_trace_setstatus system stored procedure. To manually stop the server-side trace, you must locate the traceid trace event, stop the trace, and then delete the trace definition from the instance of SQL Server. To do so, follow these steps:
  1. Start SQL Query Analyzer.
  2. Connect to the instance of SQL Server where the server-side trace is running.
  3. Run the following Transact-SQL statement to retrieve the list of the running trace:
    SELECT * FROM ::fn_trace_getinfo(NULL)
  4. Note the traceid of the server-side trace that you want to stop.
  5. Run the following Transact-SQL statement to stop the server-side trace (where <traceid> is the traceid that you noted in step 4):
    EXEC sp_trace_setstatus @traceid = <traceid> , @status = 0
  6. Run the following Transact-SQL statement to close the trace and to delete the trace information (where <traceid> is the traceid that you noted in step 4):
    EXEC sp_trace_setstatus @traceid = <traceid> , @status = 2

REFERENCES

For more information, visit the following Microsoft Web sites:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_fa-fz_04yx.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ta-tz_0tnt.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_trace_7muq.asp

Modification Type:MajorLast Reviewed:11/26/2003
Keywords:kbhowto KB822853 kbAudDeveloper
©2003 Microsoft Corporation. All rights reserved.