fn_dblog. Cómo analizar transactional log en SQL Server

Hola a Todos! Hoy vamos a ver cómo analizar un transactional log en SQL Server. Para ello, usaremos la función fn_dblog. ¡Así podemos ver qué operaciones son las que más movimiento generan!

La función fn_dblog(), mejorada con la función fn_full_dblog() en SQL Server 2017, nos permite leer la parte activa del archivo de registro de transacciones y recuperar información útil sobre las actividades de modificación en su base de datos.

Cosas a tener en cuenta con la función fn_dblog()

Un par de cosas a tener en cuenta al usar la función fn_dblog ():

  • Sólo devuelve información sobre la parte activa del registro de transacciones, como las transacciones abiertas o la última actividad.   
  • Esta es una función INDOCUMENTADA y, como tal, Microsoft podría cambiarla sin previo aviso. Por lo tanto, no recomendamos insertarla en el código de producción.

La función fn_dblog() debe invocarse en el contexto de la base de datos y requiere dos parámetros: iniciar y finalizar el número de secuencia de registro (LSN). Los dejaremos en  «NULL» para recuperar todo el contenido del archivo de registro.

 SELECT * FROM fn_dblog (
               NULL, -- Start LSN nvarchar(25)
               NULL  -- End LSN nvarchar(25)
 ) 

Descripción de columnas

Una vez que se ejecuta la función, devuelve varios resultados con las siguientes columnas. A continuación, se detallan las columnas que devuelve y su descripción:

COLUMN DESCRIPTION
Current LSN Número de la secuencia actual
Previous LSN Numero previo de la secuencia
Operation Describe la operación realizada
Context Contexto de la operación
Transaction ID Id de la transacción
Log record Length Longitud del registro
AllocUnitName Nombre del objeto contra el que se ha realizado la operación
Page ID Tabla/índice id de pagina
SPID ID de sesion de usuario
Xact ID Id de transacción de usuario
Begin Time Hora de comienzo de la transacción
End Time Hora de finalización de la transacción
Transaction Name Tipo de transacción
Transaction SID Identificador de seguridad del usuario
Parent Transaction ID Identificador de la transacción padre
Transaction Begin Primer LSN de la transacción
Numbers of locks Numero de bloqueos
Lock information Informacion sobre los bloqueos
Description Descripción
Log record Contenido de la transacción en hexadecimal

Operaciones

La columna «Operation» indica el tipo de operación que se ha realizado y registrado en el archivo de registro de transacciones.

Utilizando la siguiente query podemos agrupar por operación y ver qué es lo que más se está realizando.

 SELECT
 [Operation],
 count(*) AS [No of Records],
 SUM([Log Record Length]/1024.00/1024.00) AS [RecordSize (MB)]
 FROM fn_dblog(NULL,NULL)
 GROUP BY Operation
 ORDER BY [RecordSize (MB)] DESC 

Escenarios de uso de fn_dblog:

SELECT:

fn_dblog select

INSERT:

fn_dblog insert

DELETE:

fn_dblog delete

Con el siguiente código podemos sobre qué objeto ha sido:

 SELECT
 distinct (AllocUnitId) 
 FROM fn_dblog(NULL,NULL)
 where Operation like '%DELETE%' 

Cruzamos la información con las siguientes líneas y tendremos el objeto:

 SELECT object_name(object_id) AS name, object_id
 ,index_id ,allocation_unit_id
 FROM sys.allocation_units AS au
 INNER JOIN sys.partitions AS p
 ON au.container_id = p.hobt_id
 AND (au.type = 1 OR au.type = 3)
 where allocation_unit_id='72057594066632704' 
fn_dblog cruzado

¡¡EXTRA TIP!!

Podemos utilizar la siguiente query para analizar cualquier transactional log que tengamos en disco, ¡no solo el actual!

 SELECT
 [Operation],
 count(*) AS [No of Records],
 SUM([Log Record Length]/1024.00/1024.00) AS [RecordSize (MB)]
 FROM
     fn_dump_dblog (
         NULL, NULL, N'DISK', 1, N'G:\backup\CloudMailSync_backup_201910231000.trn',
         DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
         DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
         DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
         DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
         DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
         DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
         DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
         DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
         DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT)
 where operation='LOP_MODIFY_COLUMNS'
 and [Lock Information] like '%2023678257%' 
 GROUP BY Operation
 ORDER BY [RecordSize (MB)] DESC 
 GO 

En esta ocasión, hemos hablado sobre cómo leer el transaction log en SQL Server pero todas las semanas realizamos alguna entrada. Si no te quieres perder ninguna, suscríbete a nuestra newsletter. Con solo un email al mes estarás al día de todas nuestras entradas.