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

  • Apex SQL Log
  • Log Explorer
  • SQL Log Rescue

Otras herramientas para trabajar con el LOG y herramientas de utilidad relacionadas:

  • ApexSQL Audit
  • Lumigent Audit DB
  • OmniAudit
  • SQLLog
  • Upscene SQL Log Manager

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


[Fecha del Artículo (UTC): 09/01/2008]
[Autor: GuilleSQL]



Escribir un Comentario

Para poder escribir un comentario, debe Iniciar Sesión con un usuario.

Si no dispone de un usuario, puede Registrarse y hacerse miembro.

Si dispone de un usuario, pero no recuerda sus credenciales de acceso, puede Restablecer su Contraseña.

Miembros de
Miembros de GITCA (Global IT Community Association)

Menu de Usuario
  Iniciar Sesión
  Registrarse
  Restablecer Contraseña
  Ventajas de Registrarse

Acerca de
  Contigo desde Oct 2007
  771 usuarios registrados
  86146 pageloads/mes
  Ranking Alexa 498160

Social Networks
Sigue a Portal GuilleSQL en Linkedin !!
Sigue a Portal GuilleSQL en Twitter !!



Archivo

Enero de 2017 (2)
Junio de 2016 (1)
Mayo de 2016 (2)
Abril de 2016 (2)
Septiembre de 2015 (2)
Agosto de 2015 (2)
Junio de 2015 (10)
Mayo de 2015 (4)
Abril de 2015 (8)
Marzo de 2015 (11)
Octubre de 2014 (3)
Septiembre de 2014 (7)
Agosto de 2014 (5)
Julio de 2014 (2)
Mayo de 2014 (4)
Abril de 2014 (4)
Marzo de 2014 (4)
Febrero de 2014 (1)
Enero de 2014 (5)
Diciembre de 2013 (8)
Noviembre de 2013 (2)
Octubre de 2013 (7)
Septiembre de 2013 (6)
Agosto de 2013 (1)
Julio de 2013 (6)
Junio de 2013 (11)
Mayo de 2013 (7)
Abril de 2013 (6)
Febrero de 2013 (5)
Enero de 2013 (7)
Diciembre de 2012 (12)
Noviembre de 2012 (13)
Octubre de 2012 (5)
Septiembre de 2012 (3)
Agosto de 2012 (6)
Julio de 2012 (4)
Junio de 2012 (1)
Mayo de 2012 (2)
Abril de 2012 (7)
Marzo de 2012 (16)
Febrero de 2012 (9)
Enero de 2012 (5)
Diciembre de 2011 (10)
Noviembre de 2011 (10)
Octubre de 2011 (4)
Septiembre de 2011 (5)
Agosto de 2011 (2)
Julio de 2011 (2)
Junio de 2011 (4)
Mayo de 2011 (2)
Abril de 2011 (6)
Marzo de 2011 (4)
Febrero de 2011 (10)
Enero de 2011 (5)
Diciembre de 2010 (6)
Noviembre de 2010 (4)
Octubre de 2010 (8)
Septiembre de 2010 (4)
Agosto de 2010 (1)
Julio de 2010 (3)
Mayo de 2010 (5)
Abril de 2010 (6)
Marzo de 2010 (8)
Febrero de 2010 (3)
Enero de 2010 (1)
Diciembre de 2009 (9)
Noviembre de 2009 (14)
Octubre de 2009 (2)
Septiembre de 2009 (8)
Agosto de 2009 (2)
Julio de 2009 (10)
Junio de 2009 (9)
Mayo de 2009 (10)
Abril de 2009 (9)
Marzo de 2009 (3)
Febrero de 2009 (2)
Enero de 2009 (3)
Noviembre de 2008 (2)
Octubre de 2008 (2)
Septiembre de 2008 (2)
Agosto de 2008 (5)
Julio de 2008 (5)
Junio de 2008 (1)
Mayo de 2008 (3)
Abril de 2008 (2)
Marzo de 2008 (2)
Febrero de 2008 (2)
Enero de 2008 (5)
Noviembre de 2007 (2)
Octubre de 2007 (2)






Esta información se proporciona "como está" sin garantías de ninguna clase, y no otorga ningún derecho.
This information is provided "AS IS" with no warranties, and confers no rights.

Copyright © 2007 GuilleSQL, todos los derechos reservados.
GuilleSQL.com y GuilleSQL.net son también parte de Portal GuilleSQL.

Visitas recibidas (Page Loads) en GuilleSQL (fuente: StatCounter):

screen resolution stats
Visitas