-- ***********************************************
-- ***  Crear/Arrancar Traza de Sort Warnings  ***
-- ***********************************************
--
-- GuilleSQL - Un portal sobre Microsoft SQL Server en Castellano
-- https://guillesql.es
---- ***********************************************************************
-- *** Si está la Traza en ejecución => Detener la traza y eliminarla  ***
-- ***********************************************************************
IF EXISTS(select traceid from fn_trace_getinfo(0) where cast(value as varchar(1000)) like '%\sort_warnings_%')
BEGIN
   declare @tmp_traceid int
   select @tmp_traceid = traceid from fn_trace_getinfo(0) where cast(value as varchar(1000)) like '%\sort_warnings_%'
   exec sp_trace_setstatus @tmp_traceid, 0
   exec sp_trace_setstatus @tmp_traceid, 2
END


-- ************************
-- ***  Crear la Traza  ***
-- ************************
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 50 
declare @trace_file nvarchar(245)
set @trace_file = 'g:\sqltraces\sort_warnings_' + CONVERT(VARCHAR, GETDATE(), 112) + '_' + REPLACE(CONVERT(VARCHAR, GETDATE(), 114),':','_') + '_R'

exec @rc = sp_trace_create @TraceID output, 2, @trace_file, @maxfilesize, NULL 
if (@rc != 0) goto finish

-- ***********************************************************************
-- ***  Configurar los Eventos y Campos deseados para la Traza creada  ***
-- ***********************************************************************
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 69, 8, @on
exec sp_trace_setevent @TraceID, 69, 9, @on
exec sp_trace_setevent @TraceID, 69, 21, @on
exec sp_trace_setevent @TraceID, 69, 10, @on
exec sp_trace_setevent @TraceID, 69, 14, @on
exec sp_trace_setevent @TraceID, 69, 26, @on
exec sp_trace_setevent @TraceID, 69, 50, @on
exec sp_trace_setevent @TraceID, 69, 11, @on
exec sp_trace_setevent @TraceID, 69, 35, @on
exec sp_trace_setevent @TraceID, 69, 4, @on
exec sp_trace_setevent @TraceID, 69, 12, @on


-- ***************************
-- ***  Arrancar la traza  ***
-- ***************************
exec sp_trace_setstatus @TraceID, 1

finish: 
go