GuilleSQL :: Microsoft SQL Server, SSIS, y más !!

SQL Server FAQ: ¿Es posible leer el LOG de SQL Server? ¿Puede utilizarse fn_dblog y DBCC LOG?

Volver a: [SQL Server FAQ :: Preguntas y Respuestas Frecuentes de SQL Server :: Manual SQL Server]

Durante el diagnóstico de problemas con SQL Server, habitualmente es suficiente con la ejecución de trazas de SQL Profiler (y su posterior análisis) y la captura de determinados contadores de rendimiento (y su análisis posterior) con un monitor (ej: MOM2005, SCOM, Patrol, etc.). Sin embargo, en alguna ocasión surge la duda de averiguar qué ha ocurrido en una base de datos SQL Server explorando el LOG. Este capítulo explica las utilidades disponibles con el producto (fn_dblog y DBCC LOG), se incluyen consultas de ejemplo, y se incluye referencias a herramientas de terceros (Apex SQL Log, Log Explorer, SQL Log Rescue, ApexSQL Audit, Lumigent Audit DB, Omni Audit, SQLLog, Upscene SQL Log Manager).

El contenido del LOG de SQL Server utiliza un formato propietario, bajo el cual, almacena la información de las modificaciones realizadas en la base de datos. En teoría no se ofrece ningún mecanismo para ver el contenido del LOG de una base de datos SQL Server. Sin embargo, existen dos métodos no documentados para conseguir obtener alguna información, siempre bajo la responsabilidad de cada uno:

fn_dblog. Se trata de una función no documentada del sistema, por lo cual, tiene la ventaja de poder utilizarse en la cláusula FROM de una sentencia SELECT. Afecta a la base de datos contextual, es decir, a la base de datos en la que estamos posicionados actualmente (si deseamos cambiar de base de datos, utilizar el comando USE). Ej: SELECT * FROM ::fn_dblog(NULL,NULL). La información que devuelve en SQL Server 2005 es la siguiente:

Current LSN
Operation
Context
Transaction ID
Tag Bits
Log Record Fixed Length
Log Record Length
Previous LSN
Flag Bits
AllocUnitId
AllocUnitName
Page ID
Slot ID
Previous Page LSN
PartitionID
RowFlags
Num Elements
Offset in Row
Checkpoint Begin
CHKPT Begin DB Version
Max XDESID
Num Transactions
Checkpoint End
CHKPT End DB Version
Minimum LSN
Dirty Pages
Oldest Replicated Begin LSN
Next Replicated End LSN
Last Distributed Backup End LSN
Last Distributed End LSN
Server UID
UID
SPID
Beginlog Status
Begin Time
Transaction Name
Transaction SID
End Time
Transaction Begin
Replicated Records
Oldest Active LSN
Server Name
Database Name
Mark Name
Master XDESID
Master DBID
Preplog Begin LSN
Prepare Time
Virtual Clock
Previos Savepoint
Savepoint Name
Rowbits First Bit
Rowbits Bit Count
Rowbits Bit Value
Number of Locks
Lock Information
LSN before writes
Pages Written
Data Pages Delta
Reserved Pages Delta
Used Pages Delta
Data Rows Delta
Command Type
Publication ID
Article ID
Partial Status
Command
Byte Offset
New Value
Old Value
New Split Page
Rows Deleted
Bytes Freed
CI Table ID
CI Index Id
FileGroup ID
Meta Status
File Status
File ID
Physical Name
Logical Name
Format LSN
RowsetId
TextPtr
Column Offset
Flags
Text Size
Offset
Old Size
New Size
Description
RowLog Contents 0
RowLog Contents 1
RowLog Contents 2
RowLog Contents 3
RowLog Contents 4
Log Record

DBCC LOG. Este comando DBCC admite como parámetros el nombre de la base de datos deseada, y un código numérico que indica que tipo de información se desea obtener del LOG. Ej: DBCC LOG(tempdb, 0). Si se omite el parámetro tipo, se usará por defecto el valor 0. El valor del parámetro tipo puede ser:

0: Información mínima. La información que devuelve en SQL Server 2005 es la siguiente:

Current LSN
Operation
Context
Transaction ID

1: Algo más información. La información que devuelve en SQL Server 2005 es la siguiente:

Current LSN
Operation
Context
Transaction ID
Tag Bits
Log Record Fixed Length
Log Record Length
Previous LSN
Flag Bits
Description

2: Bastante más información. La información que devuelve en SQL Server 2005 es la siguiente:

Current LSN
Operation
Context
Transaction ID
Tag Bits
Log Record Fixed Length
Log Record Length
Previous LSN
Flag Bits
AllocUnitId
AllocUnitName
Page ID
Slot ID
Previous Page LSN
Number of Locks
Lock Information
Description

3: Toda la información de cada operación. La información que devuelve en SQL Server 2005 es la siguiente:

Current LSN
Operation
Context
Transaction ID
Tag Bits
Log Record Fixed Length
Log Record Length
Previous LSN
Flag Bits
AllocUnitId
AllocUnitName
Page ID
Slot ID
Previous Page LSN
PartitionID
RowFlags
Num Elements
Offset in Row
Checkpoint Begin
CHKPT Begin DB Version
Max XDESID
Num Transactions
Checkpoint End
CHKPT End DB Version
Minimum LSN
Dirty Pages
Oldest Replicated Begin LSN
Next Replicated End LSN
Last Distributed Backup End LSN
Server UID
UID
SPID
Beginlog Status
Begin Time
Transaction Name
Transaction SID
End Time
Transaction Begin
Replicated Records
Oldest Active LSN
Server Name
Database Name
Mark Name
Master XDESID
Master DBID
Preplog Begin LSN
Prepare Time
Virtual Clock
Previos Savepoint
Savepoint Name
Rowbits First Bit
Rowbits Bit Count
Rowbits Bit Value
Number of Locks
Lock Information
LSN before writes
Pages Written
Data Pages Delta
Reserved Pages Delta
Used Pages Delta
Data Rows Delta
Command Type
Publication ID
Article ID
Partial Status
Command
Byte Offset
New Value
Old Value
New Split Page
Rows Deleted
Bytes Freed
CI Table ID
CI Index Id
NewAllocUnitId
FileGroup ID
Meta Status
File Status
File ID
Physical Name
Logical Name
Format LSN
RowsetId
TextPtr
Column Offset
Flags
Text Size
Offset
Old Size
New Size
Description
Bulk allocated extent count
Bulk RowsetId
Bulk AllocUnitId
Bulk allocation first IAM Page ID
Bulk allocated extent ids
RowLog Contents 0
RowLog Contents 1
RowLog Contents 2
RowLog Contents 3
RowLog Contents 4

4: Algo más de información y un volcado hexadecimal de la correspondiente fila del LOG. La información que devuelve en SQL Server 2005 es la siguiente:

Current LSN
Operation
Context
Transaction ID
Tag Bits
Log Record Fixed Length
Log Record Length
Previous LSN
Flag Bits
Description
Log Record

Resulta complicado poder interpretar la información del LOG utilizando la función fn_dblog y el comando DBCC LOG. En cualquier caso, es más cómodo trabajar con la función fn_dblog, ya que se puede utilizar desde una consulta directamente, y esto facilita su acceso. También es muy útil lanzar trazas con la herramienta SQL Profiler, y relacionar la salida de la traza con la salida de consultas a fn_dblog o con la salida de DBCC LOG. A continuación se muestran varias consultas realizadas para SQL Server 2005 que utilizan la función fn_dblog:

SELECT [operation], COUNT([Current LSN]) Ocurrencias
FROM ::fn_dblog(NULL,NULL )
GROUP BY [operation]
order by 2 desc

SELECT AllocUnitName, COUNT([Current LSN]) Ocurrencias
FROM ::fn_dblog(NULL,NULL )
GROUP BY AllocUnitName
order by 2 desc

SELECT [operation], AllocUnitName, COUNT([Current LSN]) Ocurrencias
FROM ::fn_dblog(NULL,NULL )
GROUP BY [operation], AllocUnitName
ORDER BY 1,2

SELECT SPID, [operation], [Transaction Name], [Transaction SID], MIN([Begin Time]) [Min Begin Time], MAX([Begin Time]) [Max Begin Time], COUNT([Current LSN]) Ocurrencias
FROM ::fn_dblog(NULL,NULL )
WHERE operation='LOP_BEGIN_XACT'
GROUP BY SPID, [operation], [Transaction Name], [Transaction SID]

SELECT SPID, [operation], [Begin Time], [Transaction Name], [Transaction SID]
FROM ::fn_dblog(NULL,NULL )
WHERE operation='LOP_BEGIN_XACT'

El objetivo principal de estas consultas es poder utilizarlas como un punto de inicio, a partir de el cual, se puedan alterar estas consultas de ejemplo (las cláusula WHERE principalmente), como herramienta para explorar el contenido del LOG de SQL Server.

Es importante tener en cuenta que una transacción en SQL Server se inicia con un registro en el LOG con el valor de LOP_BEGIN_XACT en el campo Operation, y finaliza con un registro con el valor LOP_COMMIT_XACT al finalizar confirmándose la transacción. Así, es habitual encontrar registros con el valor de LOP_INSERT_ROWS, LOP_MODIFY_ROW, y LOP_DELETE_ROWS en el campo Operation, al registrar las inserciones, actualizaciones y borrados de filas.

Herramientas de Terceros para acceder al LOG de SQL Server

Existen varias herramientas de terceros que permiten tanto explorar el contenido del LOG de SQL Server, incluso restaurar datos (deshacer transacciones). También existen herramientas, que son capaces de realizar tareas de este tipo sobre los ficheros de Backup de LOG (además de sobre los propios ficheros de LOG).