-- ***********************************************************************
-- ***  Crear/Arrancar Traza de Auditoría de Operaciones del Servidor  ***
-- ***********************************************************************
--
-- 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 '%\audit_server_operation_%')
BEGIN
   declare @tmp_traceid int
   select @tmp_traceid = traceid from fn_trace_getinfo(0) where cast(value as varchar(1000)) like '%\audit_server_operation_%'
   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\audit_server_operation_' + 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, 173, 8, @on
exec sp_trace_setevent @TraceID, 173, 1, @on
exec sp_trace_setevent @TraceID, 173, 10, @on
exec sp_trace_setevent @TraceID, 173, 14, @on
exec sp_trace_setevent @TraceID, 173, 26, @on
exec sp_trace_setevent @TraceID, 173, 11, @on
exec sp_trace_setevent @TraceID, 173, 35, @on
exec sp_trace_setevent @TraceID, 173, 12, @on


-- **************************
-- ***  Filtrar la traza  ***
-- **************************
declare @intfilter int
declare @bigintfilter bigint

exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - 0586943c-c605-4978-a62b-e36c2ca963af'
exec sp_trace_setfilter @TraceID, 35, 0, 7, N'tempdb'


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

finish: 
go