--------------------------------------------------------------------------------------
-- Because the SQL Profiler tool places a severe load on the production Database    */
-- use this Script to start lightweight server-side trace for the following events: */
-- 10 RPC:Completed                                                                 */
-- 12 SQL:BatchCompleted                                                            */
-- 45 SP:StmtCompleted                                                              */
-- 71 TSQL:PrepareSQL                                                               */
-- 72 TSQL:Exec PreparedSQL                                                         */
-- 73 TSQL:Unprepare SQL    							    						*/
-- 123 SQL - Full Text Query							    						*/
-- 13  SQL:BatchStarting							    							*/
-- 97  Showplan All				                                    				*/
-- 137  Blocked Process Report                                                      */
--                                                                                  */
--    set @TimeToRun -- Number of Minutes to Run                                    */
--    set @maxfilesize -- Size of File                                              */
--    set @TraceFileName -- Path and file name                                      */
--                                                                                  */
--                                                                                  */
-- Replace the text 'InsertFileNameHere' with the name of your tracefile.           */
--                                                                                  */
-- This script returns the traceId that you will need to check the trace status.    */
-- and to manually stop the trace.  By default, trace will run for 15 minutes.      */
-- To stop the trace manually, Use:                                                 */
-- exec sp_trace_setstatus @traceId, 2                                              */
--------------------------------------------------------------------------------------

declare @TimeToRun int
set @TimeToRun = 1
-- @TimeToRun is duration of trace, in minutes

declare @TraceFileName NVARCHAR(1000)
set @TraceFileName = N'C:\Trace1'
-- @TraceFileName set the path and file name of trace.

declare @maxfilesize bigint
set @maxfilesize = 50000
-- max trace file size in MB
 
declare @rc int
declare @TraceID int

declare @optionValue int
set @optionValue = 2
-- optionValue = 2 Specifies that when the @maxfilesize is reached, the current trace file is closed and a new file is created
 
declare @stoptime datetime
set @stoptime = dateadd(minute,@TimeToRun, getdate())
-- set @stoptime to NULL to run trace until stopped manually

-- INSTRUCTIONS:
-- Replace the text 'InsertFileNameHere', with an appropriate filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc
-- extension will be appended to the filename automatically.

-- This script returns the trace_id that you will need to check the trace status, and to stop the trace.

-- sp_trace_create trace_id OUTPUT, option_value, 'trace_file', max_file_size, stop_time

exec @rc = sp_trace_create @TraceID output, @optionValue,@TraceFileName, @maxfilesize, @stoptime
-- set @stoptime to NULL to run trace forever, until stopped manually with 'exec sp_trace_setstatus @traceId, 2'

if (@rc != 0) goto error

-- Set the events, and the desired columns to be captured for each event
declare @on bit
set @on = 1

-- 10 RPC:Completed
exec sp_trace_setevent @TraceID, 10, 1, @on
exec sp_trace_setevent @TraceID, 10, 3, @on
exec sp_trace_setevent @TraceID, 10, 4, @on
exec sp_trace_setevent @TraceID, 10, 8, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 13, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
exec sp_trace_setevent @TraceID, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 17, @on
exec sp_trace_setevent @TraceID, 10, 18, @on
exec sp_trace_setevent @TraceID, 10, 31, @on
exec sp_trace_setevent @TraceID, 10, 34, @on
exec sp_trace_setevent @TraceID, 10, 48, @on
exec sp_trace_setevent @TraceID, 10, 50, @on
exec sp_trace_setevent @TraceID, 10, 51, @on

-- 12 SQL:BatchCompleted 
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 3, @on
exec sp_trace_setevent @TraceID, 12, 4, @on
exec sp_trace_setevent @TraceID, 12, 8, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 18, @on
exec sp_trace_setevent @TraceID, 12, 31, @on
exec sp_trace_setevent @TraceID, 12, 48, @on
exec sp_trace_setevent @TraceID, 12, 50, @on
exec sp_trace_setevent @TraceID, 12, 51, @on


-- 13 SQL:BatchStarting
exec sp_trace_setevent @TraceID, 13, 1, @on
exec sp_trace_setevent @TraceID, 13, 3, @on
exec sp_trace_setevent @TraceID, 13, 4, @on
exec sp_trace_setevent @TraceID, 13, 8, @on
exec sp_trace_setevent @TraceID, 13, 10, @on
exec sp_trace_setevent @TraceID, 13, 12, @on
exec sp_trace_setevent @TraceID, 13, 14, @on
exec sp_trace_setevent @TraceID, 13, 27, @on
exec sp_trace_setevent @TraceID, 13, 35, @on
exec sp_trace_setevent @TraceID, 13, 49, @on
exec sp_trace_setevent @TraceID, 13, 50, @on
exec sp_trace_setevent @TraceID, 13, 51, @on
exec sp_trace_setevent @TraceID, 13, 66, @on


-- 45 SP:StmtCompleted 
exec sp_trace_setevent @TraceID, 45, 1, @on
exec sp_trace_setevent @TraceID, 45, 3, @on
exec sp_trace_setevent @TraceID, 45, 4, @on
exec sp_trace_setevent @TraceID, 45, 5, @on
exec sp_trace_setevent @TraceID, 45, 8, @on
exec sp_trace_setevent @TraceID, 45, 12, @on
exec sp_trace_setevent @TraceID, 45, 13, @on
exec sp_trace_setevent @TraceID, 45, 14, @on
exec sp_trace_setevent @TraceID, 45, 15, @on
exec sp_trace_setevent @TraceID, 45, 16, @on
exec sp_trace_setevent @TraceID, 45, 17, @on
exec sp_trace_setevent @TraceID, 45, 18, @on
exec sp_trace_setevent @TraceID, 45, 25, @on
exec sp_trace_setevent @TraceID, 45, 29, @on
exec sp_trace_setevent @TraceID, 45, 34, @on
exec sp_trace_setevent @TraceID, 45, 48, @on
exec sp_trace_setevent @TraceID, 45, 50, @on
exec sp_trace_setevent @TraceID, 45, 51, @on
exec sp_trace_setevent @TraceID, 45, 55, @on

-- 71 TSQL:PrepareSQL
exec sp_trace_setevent @TraceID, 71, 3, @on
exec sp_trace_setevent @TraceID, 71, 4, @on
exec sp_trace_setevent @TraceID, 71, 8, @on
exec sp_trace_setevent @TraceID, 71, 12, @on
exec sp_trace_setevent @TraceID, 71, 14, @on
exec sp_trace_setevent @TraceID, 71, 33, @on
exec sp_trace_setevent @TraceID, 71, 50, @on
exec sp_trace_setevent @TraceID, 71, 51, @on

-- 72 TSQL:Exec PreparedSQL
exec sp_trace_setevent @TraceID, 72, 3, @on
exec sp_trace_setevent @TraceID, 72, 4, @on
exec sp_trace_setevent @TraceID, 72, 8, @on
exec sp_trace_setevent @TraceID, 72, 12, @on
exec sp_trace_setevent @TraceID, 72, 14, @on
exec sp_trace_setevent @TraceID, 72, 33, @on
exec sp_trace_setevent @TraceID, 72, 50, @on
exec sp_trace_setevent @TraceID, 72, 51, @on

-- 73 TSQL:Unprepare SQL
exec sp_trace_setevent @TraceID, 73, 3, @on
exec sp_trace_setevent @TraceID, 73, 4, @on
exec sp_trace_setevent @TraceID, 73, 8, @on
exec sp_trace_setevent @TraceID, 73, 12, @on
exec sp_trace_setevent @TraceID, 73, 14, @on
exec sp_trace_setevent @TraceID, 73, 33, @on
exec sp_trace_setevent @TraceID, 73, 50, @on
exec sp_trace_setevent @TraceID, 73, 51, @on


-- 123 SQL - Full Text Query
exec sp_trace_setevent @TraceID, 123, 1, @on
exec sp_trace_setevent @TraceID, 123, 3, @on
exec sp_trace_setevent @TraceID, 123, 4, @on
exec sp_trace_setevent @TraceID, 123, 5, @on
exec sp_trace_setevent @TraceID, 123, 8, @on
exec sp_trace_setevent @TraceID, 123, 12, @on
exec sp_trace_setevent @TraceID, 123, 13, @on
exec sp_trace_setevent @TraceID, 123, 14, @on
exec sp_trace_setevent @TraceID, 123, 15, @on
exec sp_trace_setevent @TraceID, 123, 16, @on
exec sp_trace_setevent @TraceID, 123, 17, @on
exec sp_trace_setevent @TraceID, 123, 18, @on
exec sp_trace_setevent @TraceID, 123, 25, @on
exec sp_trace_setevent @TraceID, 123, 29, @on
exec sp_trace_setevent @TraceID, 123, 32, @on
exec sp_trace_setevent @TraceID, 123, 34, @on
exec sp_trace_setevent @TraceID, 123, 48, @on
exec sp_trace_setevent @TraceID, 123, 50, @on
exec sp_trace_setevent @TraceID, 123, 51, @on
exec sp_trace_setevent @TraceID, 123, 55, @on

-- 97 Showplan XML Statistics Profile  
exec sp_trace_setevent @TraceID, 97, 1, @on
exec sp_trace_setevent @TraceID, 97, 2, @on
exec sp_trace_setevent @TraceID, 97, 4, @on
exec sp_trace_setevent @TraceID, 97, 5, @on
exec sp_trace_setevent @TraceID, 97, 8, @on
exec sp_trace_setevent @TraceID, 97, 12, @on
exec sp_trace_setevent @TraceID, 97, 14, @on
exec sp_trace_setevent @TraceID, 97, 25, @on
exec sp_trace_setevent @TraceID, 97, 27, @on
exec sp_trace_setevent @TraceID, 97, 28, @on
exec sp_trace_setevent @TraceID, 97, 29, @on
exec sp_trace_setevent @TraceID, 97, 34, @on
exec sp_trace_setevent @TraceID, 97, 35, @on
exec sp_trace_setevent @TraceID, 97, 41, @on
exec sp_trace_setevent @TraceID, 97, 49, @on
exec sp_trace_setevent @TraceID, 97, 51, @on
exec sp_trace_setevent @TraceID, 97, 66, @on

-- 137 Blocked Process Report
exec sp_trace_setevent @TraceID, 137, 1, @on
exec sp_trace_setevent @TraceID, 137, 3, @on
exec sp_trace_setevent @TraceID, 137, 4, @on
exec sp_trace_setevent @TraceID, 137, 13, @on
exec sp_trace_setevent @TraceID, 137, 15, @on
exec sp_trace_setevent @TraceID, 137, 22, @on
exec sp_trace_setevent @TraceID, 137, 24, @on
exec sp_trace_setevent @TraceID, 137, 26, @on
exec sp_trace_setevent @TraceID, 137, 27, @on
exec sp_trace_setevent @TraceID, 137, 32, @on
exec sp_trace_setevent @TraceID, 137, 41, @on
exec sp_trace_setevent @TraceID, 137, 51, @on
exec sp_trace_setevent @TraceID, 137, 60, @on
exec sp_trace_setevent @TraceID, 137, 64, @on

-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references
select TraceID=@TraceID
goto finish

error: 
select ErrorCode=@rc

finish: 
go