
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.