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:

INSERT:

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'

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