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 sys.traces

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_primera_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.