-- ******************************************
-- ***  Crear/Arrancar Traza de Bloqueos  ***
-- ******************************************
--
-- 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 '%\blocks_%')
BEGIN
   declare @tmp_traceid int
   select @tmp_traceid = traceid from fn_trace_getinfo(0) where cast(value as varchar(1000)) like '%\blocks_%'
   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\blocks_' + 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, 137, 15, @on
exec sp_trace_setevent @TraceID, 137, 4, @on
exec sp_trace_setevent @TraceID, 137, 12, @on
exec sp_trace_setevent @TraceID, 137, 24, @on
exec sp_trace_setevent @TraceID, 137, 32, @on
exec sp_trace_setevent @TraceID, 137, 1, @on
exec sp_trace_setevent @TraceID, 137, 13, @on
exec sp_trace_setevent @TraceID, 137, 14, @on
exec sp_trace_setevent @TraceID, 137, 22, @on
exec sp_trace_setevent @TraceID, 137, 26, @on


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

finish: 
go