Server side trace - stored procedure

ServerSideTrace.sql

/************************************************************************************************************************************************************************************************************************************
************************************************************************************************************************************************************************************************************************************
Author	:	Kin Shah
Site	:	http://sqlsybase.azurewebsites.net/

The server side trace SP uses XP_CMDSHELL for creating directories.
Change :	@FilePath = 'E:\trace\' as per your location
			@Duration = 61 -- this is the trace time in minutes .. change as per your needs

If you want to look at different columns for a given event then use 

SELECT * FROM sys.trace_event_bindings AS b
INNER JOIN sys.trace_columns AS c ON c.trace_column_id = b.trace_column_id
WHERE b.trace_event_id = 162 -- change here 

Disclaimer
The views expressed on my posts on this site are mine alone and do not reflect the views of my company. All posts of mine are provided "AS IS" with no warranties, and confers no rights.
 
The following disclaimer applies to all code, scripts and demos available on my posts:
 
This Sample Code is provided for the purpose of illustration only and is not intended to be used in a production environment. THIS SAMPLE CODE AND ANY RELATED INFORMATION ARE PROVIDED “AS IS” WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE. 
 
I grant You a nonexclusive, royalty-free right to use and modify the Sample Code and to reproduce and distribute the object code form of the Sample Code, provided that You agree: 
 
(i) 	to use my name, logo, or trademarks to market Your software product in which the Sample Code is embedded; 
(ii) 	to include a valid copyright notice on Your software product in which the Sample Code is embedded; and 
(iii) 	to indemnify, hold harmless, and defend me from and against any claims or lawsuits, including attorneys’ fees, that arise or result from the use or distribution of the Sample Code.
************************************************************************************************************************************************************************************************************************************
*************************************************************************************************************************************************************************************************************************************/
 

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--4  
CREATE procedure [dbo].[usp_profiler_trace]  
as  
set nocount on  
declare @rc int  
declare @TraceID int  
declare @maxfilesize bigint  
declare @DateTime datetime  
declare @FilePath varchar(256)  
declare @FileName nvarchar(128)  
declare @Duration int  
declare @Servername varchar(128)  
declare @instance_name varchar(128)  

declare @path varchar(200)  
  
set @Servername = replace(@@SERVERNAME, '\', '$')  
  
-- Set trace time in minutes  
set @Duration = 61  
  
  
set @DateTime = dateadd(minute,@Duration, getdate())  
  

--set @FilePath = @Drive+':\trace\'  
set @FilePath = 'E:\trace\'  
print @FilePath  
  
-- Check if trace path exists, and create trace path if not exists  
declare @result int, @result2 int  
declare @SQLText varchar(8000)  
  
set @SQLText = 'cd '+@FilePath  
  
exec @result = master..xp_cmdshell @SQLText,no_output  
if @result <> 0 -- backup path does not exist  
begin  
 set @SQLText = 'mkdir '+@FilePath  
 exec @result2 = master..xp_cmdshell @SQLText,no_output  
 if @result2 <> 0  
 begin  
  RAISERROR ('Failed to create trace directory', 16, 1)  
 end  
end  
  
--create specific folder for each instance under trace folder  
select @instance_name = CONVERT(nvarchar(100), SERVERPROPERTY('instancename'))  
select @path = case when @instance_name is null then @FilePath else @FilePath+@instance_name+'\' end  
select @SQLText = 'cd '+@path  
  
exec @result = master..xp_cmdshell @SQLText, no_output  
if @result <> 0 -- path does not exist  
begin  
 set @SQLText = 'mkdir '+@path  
 exec @result = master..xp_cmdshell @SQLText, no_output  
 if @result <> 0   
  RAISERROR('Failed to create trace instance directory', 16, 1)  
end  
  
  
  
  
set @FileName = @path + @Servername + '_' +replace(replace(replace(convert(varchar(20),getdate(),120),':',''),' ',''),'-','')  
  
-- Set maximum file size  
set @maxfilesize = 5000   
  
exec @rc = sp_trace_create @TraceID output, 0, @FileName, @maxfilesize, @DateTime  
print @rc  
if (@rc != 0)   
 raiserror ('Failed to run profiler' , 1, 16)  
declare @on bit  
set @on = 1  
declare @events varchar(1000),@eleft int, @eright int  
declare @columns varchar(1000), @cleft int, @cright int  
declare @eventid int, @columnid int  
  
-- Set events to trace  
--set @events = '10,12,14,15,17,19,25,33,34,35,36,37,38,53,55,59,60,61,67,69,79,80,81'  
set @events = '10,11,12,13,16,19,33,37,53,61,162'  
-- Set columns to record  
--set @columns = '1,3,6,8,9,10,11,12,13,14,15,16,17,18,21,22,24,27,28,30,31,34,35,40'  
set @columns = '1,6,8,10,11,12,13,14,15,16,17,18,27,30,31,35,40'  
set @eleft = 1  
set @eright = charindex(',',@events)  
while @eleft > 0  
begin  
 set @cleft = 1  
 set @cright = charindex(',',@columns)  
 set @eventid = convert(int,substring(@events, @eleft, @eright - @eleft))  
 while @cleft > 0  
 begin  
  set @columnid = convert(int,substring(@columns, @cleft, @cright - @cleft))  
    
  --print str(@eventid) + ' , ' + str(@columnid)  
  exec sp_trace_setevent @TraceID, @eventid, @columnid, @on  
  
  if ((@cright + 1) > len(@columns)) begin set @cleft = 0 end else set @cleft =@cright + 1  
  if (charindex(',',@columns,@cleft) > 0) begin set @cright = charindex(',',@columns,@cleft) end else set @cright = len(@columns)+1  
  
 end  
 if ((@eright + 1) > len(@events)) begin set @eleft = 0 end else set @eleft =@eright + 1  
 if (charindex(',',@events,@eleft) > 0) begin set @eright = charindex(',',@events,@eleft) end else set @eright = len(@events)+1  
  
end  
declare @intfilter int  
declare @bigintfilter bigint  
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler'  
set @intfilter = 5701  
exec sp_trace_setfilter @TraceID, 31, 0, 1, @intfilter  
  
set @intfilter = 2528  
exec sp_trace_setfilter @TraceID, 31, 0, 1, @intfilter  
  
set @intfilter = 5703  
exec sp_trace_setfilter @TraceID, 31, 0, 1, @intfilter  
  
set @intfilter = 8153  
exec sp_trace_setfilter @TraceID, 31, 0, 1, @intfilter  
  
set @intfilter = 14108  
exec sp_trace_setfilter @TraceID, 31, 0, 1, @intfilter  
  
set @intfilter = 14149  
exec sp_trace_setfilter @TraceID, 31, 0, 1, @intfilter  
  
set @intfilter = 20554  
exec sp_trace_setfilter @TraceID, 31, 0, 1, @intfilter  
  
set @intfilter = 21010  
exec sp_trace_setfilter @TraceID, 31, 0, 1, @intfilter  
  
set @intfilter = 4035  
exec sp_trace_setfilter @TraceID, 31, 0, 1, @intfilter  
  
  
set @intfilter = 5201  
exec sp_trace_setfilter @TraceID, 31, 0, 1, @intfilter  
  
  
set @intfilter = 9008  
exec sp_trace_setfilter @TraceID, 31, 0, 1, @intfilter  
  
  
set @intfilter = 14152  
exec sp_trace_setfilter @TraceID, 31, 0, 1, @intfilter  
  
  
set @intfilter = 14213  
exec sp_trace_setfilter @TraceID, 31, 0, 1, @intfilter  
  
  
set @intfilter = 14214  
exec sp_trace_setfilter @TraceID, 31, 0, 1, @intfilter  
  
  
set @intfilter = 14215  
exec sp_trace_setfilter @TraceID, 31, 0, 1, @intfilter  
  
set @intfilter = 14216  
exec sp_trace_setfilter @TraceID, 31, 0, 1, @intfilter  
  
set @intfilter = 14243  
exec sp_trace_setfilter @TraceID, 31, 0, 1, @intfilter  
  
  
set @intfilter = 3014  
exec sp_trace_setfilter @TraceID, 31, 0, 1, @intfilter  
  
set @intfilter = 3211  
exec sp_trace_setfilter @TraceID, 31, 0, 1, @intfilter  
  
set @intfilter = 5060  
exec sp_trace_setfilter @TraceID, 31, 0, 1, @intfilter  
  
set @intfilter = 9104  
exec sp_trace_setfilter @TraceID, 31, 0, 1, @intfilter  
  
  
exec sp_trace_setstatus @TraceID, 1  
select TraceID=@TraceID  
  
set nocount off  
  
  
print 'In Process: Applying sp usp_move_profiler_trace'  
GO