Creando una Traza SQL Server (Ejemplo)
Consulta y Creación de una Traza
Finalmente aplicaremos todas las sentencias para crear una traza que registre cada vez que:
° se inicia una instrucción Transact-SQL (event_id 40)
° se completa una instrucción Transact-SQL (event_id 41)
° se inicia un procedimiento almacenado (event_id 42)
° se completa un procedimiento almacenado (event_id 43)
° se inicia una instrucción Transact-SQL de un procedimiento almacenado (event_id 44)
° se ha creado un objeto, como para las instrucciones CREATE INDEX, CREATE TABLE o
CREATE DATABASE (event_id 45)
° Realiza un seguimiento de las instrucciones Transact-SQL BEGIN, COMMIT, SAVE y
ROLLBACK TRANSACTION (event_id 50)
Como primer paso consultaremos las trazas que estén creadas actualmente con el script
select * from [Link]
go
Podemos notar que solo existe una traza que actualmente está en estado 1 es decir corriendo.
Ahora crearemos una nueva traza lo primero seria definir variables con los argumentos necesarios
para el procedimiento sp_trace_create.
declare @tracefile nvarchar(500) set @tracefile=N'F:\Trace\mi_primera_traza’ --(nombre y
ubicación del archivo traza)
declare @trace_id int --(Declaramos el id de la traza y lo dejamos sin valor para que el
procedimiento asigne este valor)
declare @maxsize bigint --(Declaramos la variable para el tamaño máximo que queremos que
tenga cada archivo de la traza)
set @maxsize =20 --(asignamos el valor máximo de tamaño en este caso será de 20MB)
Una vez declarados todos los argumentos procedemos a crear la traza
exec sp_trace_create @trace_id output,2,@tracefile ,@maxsize
go
Si notamos el segundo argumento del procedimiento es un ‘2’ lo cual equivale a que será una traza
de opción TRACE_FILE_ROLLOVER.
Si nuevamente consultamos las trazas existentes debe aparecer la que acabamos de crear
Configuración de una Traza
El procedimiento sp_trace_create solo crea la traza pero ahora es necesario añadir los eventos y
columnas que la traza va a monitorear para esto usamos sp_trace_setevent, pero el proceso puede
llegar a ser engorroso ya que por cada evento que vamos a añadir debemos ejecutar una vez el
procedimiento y por cada columna para ese evento debemos ejecutar el procedimiento por
ejemplo.
exec sp_trace_setevent 2,50, 1,1
Aquí añadimos a la traza 2 el evento 50 la columna 1 en estado on, para añadir más columnas al
mismo debería hacer.
exec sp_trace_setevent 2,50, 2,1;
exec sp_trace_setevent 2,50, 3,1;
exec sp_trace_setevent 2,50, 4,1;
y así consecutivamente hasta poner todas las columnas que quiero para ese evento y
posteriormente repetir el proceso para otros eventos, pero para hacer esto de una manera mas
sencilla implementaremos un ciclo while de la siguiente manera.
declare @trace_id int --(Declaramos una variable para guardar el id de la traza)
set @trace_id=2 --(Asignamos el id de la traza a la variable)
declare @on bit --(Declaramos una variable para el on/off de la traza)
set @on=1 --(Asignamos el valor a la variable anterior)
declare @current_num int --(Declaramos una variable para el numero de la columna)
set @current_num =1 --(Asignamos un valor inicial)
while(@current_num <65)--(Iniciamos el while y le decimos que realice las acciones hasta que
la variable de las columnas llegue a 65)
begin
/*Iniciamos el proceso del ciclo en el que empezaremos a añadir los eventos y sus columnas, como
esto se repetirá hasta que la variable de las columnas llegue a 65, quiere decir que nuestra traza
tendrá las columnas de la 1 a la 65 para los eventos que mencionamos antes.*/
exec sp_trace_setevent @trace_id,50, @current_num,@on
exec sp_trace_setevent @trace_id,40, @current_num,@on
exec sp_trace_setevent @trace_id,41, @current_num,@on
exec sp_trace_setevent @trace_id,42, @current_num,@on
exec sp_trace_setevent @trace_id,43, @current_num,@on
exec sp_trace_setevent @trace_id,44, @current_num,@on
exec sp_trace_setevent @trace_id,45, @current_num,@on
set @current_num=@current_num+1
end
go
En este momento nuestra traza ya tiene los eventos y columnas a monitorear
Crear filtros y cambios de estados una Traza
Pero para hacer aún más específico este monitoreo utilizaremos un filtro con el procedimiento
sp_trace_setfilter.
sp_trace_setfilter 2, 11, 0, 6 , N'%IUSR_Satelite%';
go
En este filtro lo que estamos especificando es que filtre para la traza con id 2 en la columna 11
“LoginName” (Nombre de inicio de sesión de SQL Server del cliente.) y cuando ese nombre de
inicio de sesión contenga IUSR_Satelite.
En query correspondería a decir Select * from traza2 where LoginName like’%IUSR_Satelite%’
Nuestra traza ya está lista para iniciarse para esto debemos utilizar el procedimiento
sp_trace_setstatus porque en este momento la traza está en estado detenido y debemos iniciarla.
declare @trace_id int
set @trace_id=2
exec sp_trace_setstatus @trace_id,1
Con esto nuestra traza se inicia como no establecimos una hora para que se detenga la detención
debe hacerse manual.
Si nuevamente consultamos las trazas existentes veremos que el estado de nuestra traza paso de
0 a 1 es decir iniciada.
Para detenerla nuevamente solo ejecutamos el script anterior pero cambiamos el 1 por 0
Y para eliminar la traza utilizamos el estado 2
Posterior a esto consultamos las trazas y ya no se nos debe mostrar.
Los datos de la traza quedan almacenados en archivos en la ubicación que determinamos al
momento de crear la traza.
Y estos pueden ser consultados con el siguiente script
select LoginName,DatabaseName,* from ::fn_trace_gettable(N'F:\Trace\mi_pr
imera_traza.trc',default)
where TextData like '%DateOnly%'
go
Las utilidades dadas a las trazas dentro de la administración son múltiples y dependen de la
configuración que se aplique a la misma.
