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

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_ROWSen 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