Which system stored procedure should you use?

You are database administrator for your company. You manage an instance of SQL Server 2008 named SQL1.
You create a new trace for tracing server-level events by using the sp_trace_create system stored procedure on SQL1.
Next, you want to start the trace. Which system stored procedure should you use?

A.
sp_trace_generateevent

B.
sp_trace_setevent

C.
sp_trace_setfilter

D.
sp_trace_setstatus

Explanation:

You should use the sp_trace_setstatus system stored procedure. SQL Server 2008 contains Transact-SQLsystem stored procedures that can be used for creating traces on a SQL Server instance. These system storedprocedures can be used as an alternative to SQL Server Profiler for creating and running traces. The sp_trace_create system stored procedure allows you to create a new trace definition. The new trace created byusing this stored procedure remains in a stopped state unless it is started. While creating a trace by using the
sp_trace_create system stored procedure, you can use the @tracefile parameter to specify the location in whichthe trace file will be stored. When you create a trace by using SQL Server Profiler, it is saved in the C:ProgramFilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLLOG folder by default. To start, stop, or closea trace, you should use the
sp_trace_setstatus system stored procedure. The complete syntax for the sp_trace_setstatus system stored procedure is as follows: sp_trace_setstatus [ @traceid = ] trace_id , [ @status = ] status; The @traceid parameter specifies the ID of the trace that you want to modify. The @status parameter is used toconfigure the action that should be implemented for the trace. The possible values for the @status parameter are 0 , 1 , and 2 . A value of 0 is used to stop a trace. A value of 1 is used to start a trace. A value of 2 is used to closea trace and remove the trace’s information from the server. You should not use the sp_trace_generateevent system stored procedure because this stored procedure doesnot allow you to start a trace. The sp_trace_generateevent system stored procedure is used to create a user-defined event. You should not use the sp_trace_setevent system stored procedure because this stored procedure does notallow you to start a trace. The sp_trace_setevent system stored procedure is used to add or remove an event orevent column in a trace. You should not use the sp_trace_setfilter system stored procedure because this stored procedure does notallow you to start a trace. The sp_trace_setfilter system stored procedure is used to apply a filter to a trace.

Objective:
Optimizing SQL Server Performance

Sub-Objective:
Collect trace data by using SQL Server Profiler.

References:
TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Operations > Monitoring > MonitoringEvents > Introducing SQL Trace > Using SQL Trace
MSDN > MSDN Library > Servers and Enterprise Development > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Technical Reference > Transact-SQLReference > System Stored Procedures (Transact-SQL) > SQL Server Profiler Stored Procedures (Transact-SQL) > sp_trace_setstatus (Transact-SQL)