DECLARE @TraceID int

EXEC CreateTrace 
	'C:\My SQL Traces\All Procedure Calls', 
	@OutputTraceID = @TraceID OUT

EXEC AddEvent 
	@TraceID, 
	'SP:Starting', 
	'TextData, StartTime, EndTime, ApplicationName, ClientHostName, DatabaseID'

EXEC AddFilter 
	@TraceID, 
	'DatabaseID', 
	37

EXEC StartTrace 
	@TraceID
GO

The massive saving in the number of stored procedure calls to setup the trace, comes from the fact that the main wrapper stored procedure accepts a list of all events and data columns in a comma delimited string format. So, how many ever events and data columns you want to capture in your trace, you'll only make one call to the wrapper stored procedure.

Better yet, all the above wrapper procedures handle errors gracefully, and return meaningful and helpful error messages, instead of throwing some cryptic return code back.

An important note: Only members of the sysadmin fixed server role can execute the SQL Trace system stored procedures. So, even if you manage to call my wrapper stored procedures, they will fail to invoke SQL Trace system procedures, if you are not a sysadmin.

Deployment Instructions:

Click here to download the complete set of scripts in a zip file. Run all the scripts inside a database of your choice. But make sure the tables and stored procedures are created in the same database. I tend to create them in a utilities/tools database created for use by the DBA team.

Now let me introduce you to all the wrapper stored procedures I created:

Stored procedure name	Parameter usage
CreateTrace Creates an empty trace definition, ready for use. Click here to download	@OutputFile - nvarchar(245) Specifies the trace file name and complete path. Do not provide a .trc extension to the file name, as SQL Trace automatically adds the .trc extension to the output file. @OverwriteFile - bit Specifies whether to overwrite the trace file, if it already exists. Default is 0, in which case if the file already exists, an error will be raised and trace will not be created. Specify 1 to overwrite an existing trace file. @MaxSize - bigint Specifies the maximum size in megabytes (MB) a trace file can grow upto. Default is 5 MB. This stored procedure code restricts the maximum trace file size to 512 MB (half Giga Byte (GB)) as a safety measure, but can be overridden by setting the variable @MaxAllowedSize to a bigger value. You will find @MaxAllowedSize in the body of the stored procedure. @Rollover - bit Specifies that when the trace file reaches the maximum specified size, a new trace file will be created. Default is 1, meaning new file will be created when the current trace file reaches the maximum size. If you specify 0, tracing will stop when the file reaches its size limit. The new file will get the same name, but will be postfixed with a number, to indicate the sequence. For example, when the file MyTrace.trc reaches its maximum size, MyTrace_1.trc will be created. @Shutdown - bit Defaults to 0. If you specify 1, SQL Server will shut down, if the trace cannot be written to the file for whatever reason. Use this option with caution, and only when absolutely needed. @Blackbox - bit Defaults to 0. If you specify 1, a blackbox trace will be created. A black box trace stores a record of the last 5 MB of trace information produced by the server. When 1 is specified, all other parameters will be ignored. To learn more about how black box trace works, consult SQL Server 2000 Books Online. @StopAt - datetime Defaults to NULL. When NULL, the trace will run until it is manually stopped or until the server shuts down. If you specify a valid date and time, the trace will stop automatically at that specified date and time. @OutputTraceID - int - OUTPUT parameter This is an OUTPUT parameter and returns the ID for the trace that is created. This ID is needed for adding events and filters to the trace, as well as for querying the trace definition. Return values: -1 indicates a failure and 0 indicates success Note: The ID of the created trace will also be returned as a resultset for convenience.
AddEvent Adds events and columns to the trace definition. Click here to download	@TraceID - int The ID of the trace, created by CreateTrace, and is used to identify the trace to which to add the events and columns. @EventList - varchar(1000) Used to specify a comma separated list of events to capture. You can see a list of all valid events in the SQL Server Books Online page titled "sp_trace_setevent". Alternatively, you will find a list of all the events and their descriptions, in this script. @ColumnList - varchar(1000) Used to specify a comma separated list of data columns to capture. You can see a list of all valid column names in the SQL Server Books Online page titled "sp_trace_setevent". Alternatively, you will find a list of all the data columns and their descriptions, in this script. Return values: -1 indicates a failure and 0 indicates success
AddFilter Adds filters to existing trace definitions Click here to download	@TraceID - int The ID of the trace, created by CreateTrace, and is used to identify the trace to which to add the filter. @ColumnName - varchar(50) Name of the column on which to apply the filter. You can only filter on a column, after adding that column to the trace definition, using AddEvent @Value - sql_variant Specifies the value on which to filter. @ComparisonOperator - varchar(8) Specifies the type of comparison to be made. Defaults to '=', meaning 'Equals' comparison. Other valid comparison operators are: '<>' (Not Equal) , '>' (Greater Than) , '<' (Less Than) , '>=' (Greater Than Or Equal), '<=' (Less Than Or Equal), 'LIKE' and 'NOT LIKE'. @LogicalOperator - varchar(3) Defaults to 'OR'. You could also specify 'AND'. Useful for filtering a column for multiple values. Return values: -1 indicates a failure and 0 indicates success Note: Call this procedure once for each filter. If you want to filter a column for a range of values (similar to BETWEEN operator), call this procedure once with '>=' comparison operator and again with '<=' comparison operator.
StartTrace Starts a specified trace. Click here to download	@TraceID - int The ID of the trace (created by CreateTrace), to be started. Return values: -1 indicates a failure and 0 indicates success
StopTrace Stops a specified trace. Click here to download	@TraceID - int The ID of the trace (created by CreateTrace), to be stopped. Return values: -1 indicates a failure and 0 indicates success
ClearTrace Clears the definition of the trace from memory. Click here to download	@TraceID - int The ID of the trace (created by CreateTrace), to be cleared from memory. Return values: -1 indicates a failure and 0 indicates success

Now that we know what the stored procedures are called and how to use their parameters, let me show you, how to use these stored procedures to setup traces for specific requirements. Before we go any further, here is a quick tip. Once you setup a specific trace using my wrapper stored procedures, you could save all those stored procedure calls in a file. Now this file will serve you as a template, just like the Profiler templates.

Scenario 1: Identifying long running stored procedures

In this scenario, we will trace for all stored procedures, that took more than 15 Seconds to complete. The output trace file 'LongRunningProcs.trc' will be saved to 'C:\My SQL Traces\' (Note that this is the location on the SQL Server machine, not the client machine).

DECLARE @TraceID int

EXEC CreateTrace
	'C:\My SQL Traces\LongRunningProcs', 
	@OutputTraceID = @TraceID OUT

EXEC AddEvent 
	@TraceID, 
	'SP:Completed', 
	'TextData, Duration'

EXEC AddFilter
	@TraceID,
	'Duration',
	15000,
	'>='
 
EXEC StartTrace @TraceID
GO

Once you are done, you could stop the trace by calling the following stored procedures. Important Note: You can only view the trace file, after successfully stopping the trace and clearing it from memory: (Lets assume that the ID of the trace created above was 1)

EXEC StopTrace 1
EXEC ClearTrace 1

DECLARE @TraceID int, @DB_ID int

EXEC CreateTrace
	'C:\My SQL Traces\ProceduresCalledInMSDB', 
	@OutputTraceID = @TraceID OUT

EXEC AddEvent 
	@TraceID, 
	'SP:Completed', 
	'TextData, StartTime, EndTime, ApplicationName, ClientHostName, NTUserName, NTDomainName, DatabaseID'

SET @DB_ID = DB_ID('msdb')

EXEC AddFilter
	@TraceID,
	'DatabaseID',
	@DB_ID
 
EXEC StartTrace @TraceID
GO

DECLARE @TraceID int

EXEC CreateTrace 
	'C:\Trapping942s', 
	@OverwriteFile = 1,
	@OutputTraceID = @TraceID OUTPUT

EXEC AddEvent 
	@TraceID, 
	'Exception', 
	'Error, StartTime, ApplicationName, NTUserName, ClientHostName'

EXEC AddFilter
	@TraceID,
	'Error',
	942

EXEC StartTrace @TraceID

DECLARE @TraceID int

EXEC dbo.CreateTrace 
	'C:\My SQL Traces\Dead Locks', 
	@OutputTraceID = @TraceID OUT

EXEC dbo.AddEvent 
	@TraceID, 
	'Lock:Deadlock, Lock:Deadlock Chain, RPC:Starting, SQL:BatchStarting', 
	'TextData'

EXEC dbo.StartTrace 
	@TraceID

DECLARE @TraceID int

EXEC dbo.CreateTrace 
	'C:\My SQL Traces\Recompilations', 
	@OutputTraceID = @TraceID OUT

EXEC dbo.AddEvent 
	@TraceID, 
	'SP:Recompile', 
	'ObjectID, ObjectName, EventSubClass, DatabaseID'

EXEC dbo.StartTrace 
	@TraceID

DECLARE @TraceID int

EXEC CreateTrace 
	@Blackbox = 1, 
	@OutputTraceID = @TraceID OUT

EXEC StartTrace 
	@TraceID