Creación de una Traza en SQL Server

Traza SQL

SQL nos provee procedimientos almacenados para la creación de trazas que nos permiten configurarlas manualmente para de esta manera suplir el uso del SQL Server Profiler y hacer un seguimiento específico a procedimientos y/o procesos puntuales.

Procedimientos de una traza y descripción

Como se mencionó antes existen una serie de procedimientos almacenados que nos permiten la creación de una traza manual, a continuación listaremos esos procedimientos y definiremos su funcionalidad:

·         fn_trace_geteventinfo

Devuelve información acerca de los eventos incluidos en la traza.

Como resultado obtendremos una tabla con la siguiente estructura.

Nombre de columna	Tipo de datos	Descripción
eventid	int	Id. del evento del que se hace el seguimiento.
columnid	int	Números de Id. de todas las columnas recopiladas para cada evento.

·         fn_trace_getinfo

Devuelve información acerca de una traza especificada o de todas las trazas existentes.

Como resultado obtendremos una tabla con la siguiente estructura.

Nombre de columna	Tipo de datos	Descripción
traceid	int	Id. de seguimiento.
property	int	Propiedad del seguimiento: 1= Opciones de seguimiento 2 = Nombre de archivo 3 = Tamaño máximo 4 = Hora de detención 5 = Estado actual del seguimiento. 0 = detenido. 1 = en ejecución.
value	sql_variant	Información acerca de la propiedad de seguimiento especificado.

·         sp_trace_create

Crea una definición de traza. El nuevo seguimiento estará en estado de detención.

Para la creación de una traza debemos tener en cuenta los siguientes argumentos:

-       id_traza: Es el número que se le asigna a la nueva Traza. Sera el número con el cual identificaremos la traza para procesos de identificación, modificación y control.(Este valor es asignado por SQL pero debe ser declarado por el usuario como una salida)

-       option_value: Es un valor entero. A través de este atributo especificaremos la opción de traza que implementaremos que puede ser:

Nombre de la opción	Valor de la opción	Descripción
TRACE_FILE_ROLLOVER	2	Especifica que al alcanzar el valor establecido en max_file_size, el archivo de traza actual se cerrará y se creará un archivo. Todos los nuevos registros se escribirán en el archivo nuevo. El archivo nuevo tendrá el mismo nombre que el archivo anterior, pero se agregará un entero para indicar su secuencia. Por ejemplo, si el archivo de traza original se llama FILENAME.TRC, siguiente archivo de traza se llamará FILENAME_1.TRC, el siguiente archivo de traza se llamará FILENAME_2.TRC, y así sucesivamente. A medida que se creen más archivos de traza de sustitución incremental, el valor entero agregado al nombre del archivo aumentará secuencialmente. SQL Server utiliza el valor predeterminado de max_file_size (5 MB) si se especifica esta opción sin especificar un valor para max_file_size.
SHUTDOWN_ON_ERROR	4	Especifica que, si por cualquier razón, no se puede escribir la traza en el archivo, SQL Server se cerrará. Esta opción es muy útil cuando se realizan trazas de auditoría de seguridad.
TRACE_PRODUCE_BLACKBOX	8	Especifica que el servidor guardará un registro de los últimos 5 MB de información de traza producidos por el servidor. TRACE_PRODUCE_BLACKBOX es incompatible con las demás opciones.

-       trace_file: En este argumento especificamos el nombre y la ubicación en la que se guardara la traza  y el archivo debe guardarse con la extensión .trc

-       max_file_size: Especifica el tamaño máximo en megabytes (MB) que puede alcanzar un archivo de traza.

-       stop_time: Especifica la fecha y la hora de la detención de la traza. Si el valor se deja en NULL, la traza se ejecuta hasta que se detiene de forma manual.

-       max_rollover_files: Especifica el número máximo de archivos de traza que pueden existir con el mismo nombre y solo es aplicable si la opción de traza seleccionada es TRACE_FILE_ROLLOVER según el número máximo de archivos que se determine al llegar al límite se borrara el archivo más antiguo con el mismo nombre y se escribirá uno nuevo.

Los tres atributos anteriores están muy relacionados con las option values que se hayan determinado para la traza, por ejemplo si se determinó que la traza será TRACE_FILE_ROLLOVER y un max_file_size de 10 (MB) sin stop_time el archivo .trc llegara a 10MB y creara uno nuevo con el mismo nombre mas el consecutivo y continuara repitiendo la operación cada que el archivo .trc alcance los 10MB solo dejara de hacerlo cuando la traza se detenga manualmente o se apague el servidor.

Es por ese motivo que debe tenerse cuidado al crear la traza por que en una base de datos con alta transaccionaldad se podría llenar un disco de 10GB en una hora o menos.

El sp_trace_create es un procedimiento almacenado que remplaza la mayor parte de las acciones que se utilizaban en versiones anteriores de SQL Server con los procedimientos almacenados extendidos como:

o   xp_trace_addnewqueue

o   xp_trace_setqueuecreateinfo

o   xp_trace_setqueuedestination

sp_trace_setevent

Agrega o quita un evento o una columna de evento de una traza y solo puede ser ejecutado para trazas ya creadas y que estén detenidas.

            Los argumentos para el uso de esta función corresponden a:

-       trace_id: Corresponde al número de identificación de la traza.

-       event_id: Es el Id. del evento que se debe activar, los eventos que podemos adicionar a una traza son 235.

-       column_id: Es el Id. de la columna que va a agregarse para el evento las columnas que es posible agregar son 64.

La lista completa con sus descripciones tanto de los eventos como de las columnas  se puede encontrar en el siguiente link:

https://technet.microsoft.com/es-es/library/ms186265(v=sql.105).aspx

-       on: Este último argumento especifica la activación o desactivación del evento, (1) ON (0) OFF

sp_trace_setevent

Modifica el estado actual de la traza especificada.

Los argumentos para el uso de esta función:

-       trace_id: Corresponde al número de identificación de la traza.

-       Status: Por medio de este argumento podemos determinar en que estado estará la traza

(0)  Detenida

(1)  Inicia

(2)  Cierra y elimina

sp_trace_setevent

Aplica un filtro a una traza y solo puede ser ejecutado para trazas ya creadas y que estén detenidas.

Los argumentos para el uso de esta función:

-       trace_id: Corresponde al número de identificación de la traza.

-       column_id: Determina la columna a la que se aplicara el filtro.

-       logical_operator: Especifica el operador lógico (0) AND (1) OR

-       comparison_operator: Determina la comparación a hacer según la siguiente tabla:

o   (0) =(Es igual a)

o   (1) <>(Diferente a)

o   (2) > (Mayor que)

o   (3) < (Menor que)

o   (4) >= (Mayor o igual)

o   (5) <= (Menor o igual)

o   (6) LIKE

o   (7) NOT LIKE

-       Value: En este argumento ponemos el valor que se va a filtrar. (El tipo de dato a filtrar debe coincidir con el de la columna si es id. Objeto que es un tipo int el valor del value debe corresponder a un número)