Introducción
Siempre ha habido debate acerca de si hay o no beneficios reales a ser ganados accediendo a la información del registro de transacciones. Este artículo hará el esfuerzo de responder esa pregunta tocando los siguientes puntos:
- ¿Qué es el Registro de Transacciones SQL Server?
- ¿Qué información está almacenada en el registro de transacciones?
- ¿Qué puede ganarse accediendo a la información en el registro de transacciones?
- ¿Cómo trabaja el registro de transacciones?
- ¿Qué herramientas están disponibles para leer el registro de transacciones?
- Y por último, ¿deberíamos estar haciendo esto en primer lugar?
¿Qué es el Registro de Transacciones SQL Server?
El principal propósito del Registro de Transacciones SQL Server es asegurar que su base de datos pueda restaurarse a un estado consistente en el caso de una falla del sistema. Adicionalmente, es usado para realizar otras funciones como retrotraer cuando un comando correspondiente es usado y soporta replicación transaccional y soluciones de alta disponibilidad.
SQL Server registra información acerca de cada transacción hecha en el registro de transacciones antes de que los cambios sean escritos a la base de datos. La cantidad de información registrada depende del modelo de recuperación de su base de datos. SQL Server ofrece 3 modelos de recuperación diferentes: Completo, Por medio de registros de operaciones masivas y Simple.
Modelos de Recuperación
Modelo de recuperación COMPLETOEste modelo de recuperación registra cada cambio a cada fila así como una copia de cada página añadida a los índices o tablas. Como tal el registro contiene suficiente información para poder reconstruir completamente cada acción que ocurrió en la base de datos, permitiéndole restaurar su base de datos a un punto de tiempo específico, dado que usted tienen una cadena de registro completa. Todas las entradas son mantenidas en el registro de transacciones en línea hasta que el registro es respaldado, después de lo cual sólo las transacciones activas permanecerán en el registro en línea. Esto significa que para obtener información acerca de las transacciones completadas desde el registro, las copias de seguridad tendrán que ser tomadas en cuenta.
Cuando usted está usando la opción De recuperación BULK_LOGGED, Todas las operaciones mínimamente registradas no son escritas en el registro. Las operaciones mínimamente registradas son operaciones como SELECT INTO, BULK INSERT y operaciones de Índice. Esencialmente sólo la información suficiente es registrada para poder deshacer la transacción, pero no para rehacerla. El registro es manejado en la misma manera que en el modelo de recuperación COMPLETO, y las transacciones inactivas son movidas a la copia de seguridad del registro cuando una copia de seguridad es tomada. Por supuesto, la información acerca de las transacciones masivas no está disponible.
El modelo de recuperación SIMPLE sólo registra suficiente información para permitirle recuperar su base de datos. Todas las entradas inactivas del registro son automáticamente truncadas cuando se pasa un punto de control. Todas las operaciones todavía son registradas, pero tan pronto como se alcanza el punto de control el registro es automáticamente truncado, lo que significa que está disponible para reutilización y las entradas antiguas del registro pueden ser ahora sobrescritas.
¿Qué se registra en el registro de transacciones?
SQL Server registra cada evento en una base de datos en mayor o menor grado.
- Cuando una transacción comienza o termina
- Cada update, insert o delete
- Eliminación y creación de tablas e índices
- Grado y página de asignaciones y des asignaciones
- Truncado de tablas
- Todos los bloqueos
Puede que algunas operaciones sean mínimamente registradas cuando la base de datos está en los modelos de recuperación simple o por medio de registros de operaciones masivas, como bcp, BULK_INSERT, SELECT INTO y SELECT … INSERT.
¿Qué se puede ganar de leer el Registro de Transacciones?
Hay cuatro razones principales por las que uno puede estar interesado en leer el registro de transacciones.
Auditoría / Análisis forense
SQL Server ofrece muchos métodos que pueden ser implementados como medidas preventivas para evitar la necesidad de usar el registro de transacciones SQL Server para auditar una base de datos. Esto incluye a SQL Server Auditing (SQL 2008 +), los rastros y los eventos extendidos, por mencionar algunos. La mayoría de estos con excepción del rastro por defecto, requieren implementación previa a la ocurrencia de un evento.
De todos modos, el registro de transacciones SQL Server siempre está presente y como tal puede ofrecer información valiosa después de que ocurrió un evento a pesar del hecho de que no se hizo ninguna configuración avanzada.
En la ausencia de todas las medidas preventivas, poder leer el registro de Transacciones SQL Server ofrece la habilidad de descubrir quién realizó una transacción específica después del hecho, así como la habilidad de obtener los valores que han sido modificados con la opción para retrotraerlos.
Recuperación
Ya que cada acción es registrada en el registro de transacciones de tal modo que una transacción puede arrastrarse o retrotraerse, el registro de transacciones SQL Server puede ser usado para recuperar datos perdidos. Dado que sólo los cambios son registrados y los datos en el registro no están almacenados en un formato legible para humanos, obtener esta información del registro no es fácil, pero los datos están disponibles si usted sabe dónde buscar y cómo leerlos. Más acerca de esto más adelante en este artículo en la sección acerca de interpretar los datos en fn_dblog y fn_dump_dblog.
Solución de Problemas
En ciertas instancias, poder ver qué exactamente pasó en una base de datos para un periodo de tiempo específico es necesario. Digamos que el tamaño de su base de datos ha crecido inexplicablemente durante el curso de la noche. Sería imposible decir, sólo mirando los datos, qué razón podría estar detrás de esto. Sin embargo, leer el registro de transacciones le dará la información exacta acerca de qué ocurrió durante el periodo específico que pudo haber resultado en un crecimiento rápido de su base de datos.
Identificando un punto de restauración
Finalmente, pero no menos importante, cuando se trata de tener que restaurar una copia de seguridad, sabiendo el punto exacto en el tiempo en que la base de datos puede ser restaurada para obtener los datos dañados o perdidos es una enorme ventaja. En lugar de restaurar múltiples copias de seguridad hasta que encuentre cuál tiene los datos relevantes intactos, usted puede leer el registro de transacciones para determinar cuándo ocurrió el evento y restaurar al momento preciso justo antes de que el evento ocurriera.
¿Debería Microsoft proveer una herramienta de lectura de registros?
Ha habido debate acerca de si Microsoft debería o no proveer una herramienta de lectura de registros. Tanto Kalen Delaney como Paul Randal están de acuerdo con que hay aplicaciones útiles de tal herramienta, pero que Microsoft debería enfocarse en cosas más críticas y dejar las herramientas de lectura de registros a otro fabricante que sea experto en el arte de leer el registro.
Anatomía del Registro de Transacciones SQL Server
El registro de transacciones SQL Server es un archivo que usualmente tiene la extensión .LDF. Aunque es posible tener múltiples archivos de registro para una base de datos, el registro de transacciones es siempre escrito secuencialmente y múltiples archivos físicos de registros son tratados como un archivo continuo circular.
SQL Server usa el registro de transacciones para asegurarse de que todas las transacciones mantienen su estado en caso de una falla de la base de datos o el servidor. Todas las transacciones son escritas en el Registro de Transacciones antes de ser escritas en los archivos de datos. Esto es conocido como registro de escritura adelantada.
Cada acción realizada en SQL Server es registrada en el registro de transacciones SQL Server, entradas múltiples pueden ser creadas para una transacción así como también todos los bloqueos que fueron tomados durante la operación. Cada entrada del registro tiene un número único conocido como LSN (número de secuencia de registro, log sequence number).
Suficiente información es escrita en el registro para que una transacción sea rehecha o deshecha. En algunos casos esto significa que el cambio real es registrado, en otras instancias los cambios pueden ser registrados eficientemente pero sólo registrando las páginas que han sido cambiadas como en el caso de un truncado de tabla.
Lógicamente el Registro de Transacciones SQL Server está dividido en múltiples secciones conocidas como archivos de registro virtuales o VLFs (virtual log files). El registro de transacciones lógico se trunca y expande en unidades de VLFs. Si un VLF ya no contiene una transacción activa, ese VLF puede ser marcado para reutilización. SI el registro necesita más espacio, el espacio es asignado en incrementos de VLFs. El número y tamaño de los VLFs es decidido por el motor de la base de datos y se esforzará en asignar tan pocos VLFs como sea posible. Aunque el tamaño y el número de VLFs no pueden ser configurados, son afectados por el tamaño inicial y el incremento de crecimiento del registro de transacciones. Si el incremento de crecimiento del registro está establecido a un valor muy bajo, puede resultar en una cantidad excesiva de VLFs, lo que puede tener un efecto adverso en el desempeño. Para evitar esto, es importante dar un tamaño adecuado al registro y hacerlo crecer en incrementos suficientemente grandes.
El siguiente comando puede ser ejecutado para ver cuánto archivos de registro virtuales hay, cuántos han sido usados y cuáles son sus tamaños. Esto será usado para determinar cuál tamaño y cuál incremento son los correctos.
|
1 |
DBCC LOGINFO |
Este procedimiento retorna las siguientes columnas:
| Columna | Descripción |
| RecoveryUnitID | |
| FileID | Este es el número de identificación del archivo físico del registro. Sólo aplica si usted tiene más de un archivo de registro físico. |
| FileSize | El tamaño del archivo en bytes |
| StartOffset | Este es el offset desde donde el VLF comienza en bytes. La salida es ordenada en esta columna. |
| FSeqNo | Este es el orden en el que el VLF será usado. El número más grande es el cual está siendo actualmente usado. |
| Status | Hay 2 posibles valores, 0 y 2. 2 significa que el VLF no puede ser reutilizado y 0 significa que está listo para reutilizarse. |
| Parity | Hay 2 posibles valores, 64 y 128. |
| CreateLSN | Este es el LSN cuando el VLF fue creado. Si createLSN es 0, significa que fue creado cuando se creó el archivo físico del registro de transacciones. |
Leyendo el Registro de Transacciones SQL Server
Aun cuando Microsoft no provee una herramienta, hay muchas funciones no documentadas que pueden ser usadas para ver el registro. Dado que estos procedimientos son no documentados, ellos tampoco son soportados por Microsoft y por tanto vienen con el aviso de “Use a su propio riesgo”. Han habido algunos reportes de que fn_db_dumplog crea un itinerario oculto del OS y algunos hilos que aparentemente permanecen en el servidor hasta que es reiniciado. De acuerdo con Paul Randal, el Equipo SQL de Microsoft está consciente de esto y lo arreglará en algún momento en el futuro.
fn_dblog()
Esta función con valores de tabla (la cual fue DBCC LOG previamente a SQL Server 2005) le permite ver las entradas en registros de transacciones en línea. Este procedimiento acepta 2 parámetros, el LSN de inicio y de final. Para ver todas las entradas disponibles, NULL puede ser pasado por ambos parámetros, y todas las entradas en la porción activa del registro en línea serán mostradas.
|
1 |
SELECT * FROM fn_dblog(NULL,NULL) |
fn_dump_dblog()
Esta función lee el registro en línea y las copias de seguridad del registro y acepta 68 parámetros. Todos los parámetros necesitan ser especificados para ejecutar la sentencia.
|
1 2 3 4 5 6 7 8 9 |
SELECT * FROM fn_dump_dblog(NULL,NULL,'DISK',1 ,'D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\AdventureWorks2012.bak' ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL ,NULL,NULL,NULL,NULL, NULL,NULL,NULL,NULL,NULL,NULL ,NULL,NULL,NULL,NULL, NULL,NULL,NULL,NULL,NULL,NULL ,NULL,NULL,NULL,NULL, NULL,NULL,NULL,NULL,NULL,NULL ,NULL,NULL,NULL,NULL, NULL,NULL,NULL,NULL,NULL,NULL ,NULL,NULL,NULL,NULL, NULL,NULL,NULL,NULL,NULL,NULL ,NULL,NULL,NULL,NULL) |
| Parámetro | Descripción |
| @start | El LSN de inicio |
| @end | El LSN de final |
| @devtype | Este es el tipo de dispositivo de respaldo. El valor por defecto es DISK. Otros valores válidos son NULL(DISK) | DISK | TAPE | VIRTUAL_DEVICE |
| @seqnum | Esto indica de qué copia de seguridad leer de un dispositivo de respaldo si hay más de una copia de seguridad en un archivo de copia de seguridad. El valor por defecto es 1. |
| @fname1 | Este parámetro acepta la ruta del archivo de copia de seguridad. |
| @fname2 to @fname64 | Estos parámetros son usados para especificar archivos de copias de seguridad adicionales, si el conjunto de medios tiene múltiples familias de medios. |
Interpretando los datos en fn_dblog y fn_dump_dblog
Ahora que hemos visto cómo podemos ver la información en el Registro de Transacciones SQL Server, demos un vistazo a cómo se presenta y cómo podemos comprenderla.
Este es un tema increíblemente complicado cuyos detalles están más allá del alcance de este artículo, pero para entender el valor de usar una herramienta de lectura de registros es necesario tener una idea básica de qué tomaría interpretar los datos en el registro sin la ayuda de una herramienta.
La secuencia de operaciones es indicada por el LSN (log sequence number), pero dado que múltiples operaciones pueden ocurrir simultáneamente, todas las entradas enlazadas a una transacción específica pueden no aparecer en secuencia, así que es importante también ver al Transaction ID para saber qué entradas del registro corresponden a qué transacción.
Insertemos una fila en una tabla simple llamada [dbo.attribute] y veamos qué pasó en el registro.
|
1 |
INSERT INTO dbo.attribute VALUES ('Red') |
En esta consulta solo estoy viendo las entradas que tienen el mismo Transaction ID que el INSERT.
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT [Transaction ID], [Current LSN], [Transaction Name], [Operation], [Context],[AllocUnitName],[Begin Time],[End Time], [Transaction SID], [Num Elements] , [RowLog Contents 0], [RowLog Contents 1], [RowLog Contents 2], [RowLog Contents 3] FROM fn_dblog (NULL, NULL) WHERE [Transaction ID] = (Select [Transaction ID] FROM fn_dblog (null,null) WHERE [Transaction Name] = 'INSERT') GO |
Note que la primera entrada es una operación LOP_BEGIN_XACT. Esto indica el inicio de cualquier transacción y muestra el tiempo de inicio. Similarmente, la última entrada es una operación LOP_COMMIT_XACT que muestra el tiempo de fina de la transacción.
La segunda fila indica que una operación de bloqueo fue hecha (LOP_LOCK_XACT). SI usted desea ver más información acerca del bloqueo, puede revisar la columna [Lock Information].
La tercera columna es donde empieza a ponerse realmente interesante. Esta es la entrada en la cual podremos encontrar los datos los datos reales que fueron insertados en la tabla. Los valores de los datos son almacenados en las columnas RowLog Contents. Hay 6 columnas RowLog Contents. Para saber cuál de estas columnas son relevantes para la transacción, podemos revisar la columna [Num Elements].
En este caso particular hay 3 elementos, lo cual significa que sólo deberíamos ver los valores presentes en [RowLog Contents 0], [RowLog Contents 1] y [RowLog Contents 2]. Como puede ver, no es obvio, sólo viendo los datos en estos campos, saber qué fue insertado realmente. Para saber qué valores fueron, tendremos realmente que desarmar cada entrada.
El dato en RowLog Contents 0 es:
0x300008000100000002000001001200526564
El cual puede ser partido como sigue:
| 30 | Bit de Estado A |
| 00 | Bit de Estado B |
| 0800 | Offset para encontrar el número de columnas en la fila |
| 01000000 | Dato de tamaño fijo col = 1 |
| 0200 | Número de columnas |
| 00 | Bitmap Null |
| 0100 | Número de columnas de tamaño variable |
| 1200 | Posición donde la primera columna de tamaño variable termina, este es un byte cambiado que es 0x0012 que se traduce a 18. |
| 526564 | Datos en la columna de tamaño variable = Red |
El valor actual insertado puede ser extractado desde el registro. Ahora demos un vistazo a que pasa cuando actualizamos una entrada. Voy a cambiar el valor de ‘Red’ a Rad.
|
1 |
UPDATE dbo.attribute SET name_e = 'Rad' WHERE id = 1 |
Veamos qué fue registrado ahora.
|
1 2 3 4 5 6 7 8 |
SELECT [Transaction ID], [Current LSN], [Transaction Name], [Operation], [Context], [RowLog Contents 0], [RowLog Contents 1] FROM fn_dblog (NULL, NULL) WHERE [Transaction ID] = (Select [Transaction ID] FROM fn_dblog(null,null) WHERE [Transaction Name] = 'UPDATE') GO |
Note que los valores en RowLog Contents 0 y RowLogContents 1. Si convertimos esto a varchar veremos que SQL Server sólo registró el cambio real. RowLog Contents 0 contiene el valor anterior y RowLog Contents 1 contiene el valor posterior.
|
1 2 |
SELECT CAST(0x65 AS VARCHAR) SELECT CAST(0x61 AS VARCHAR) |
En este caso el único cambio fue que la ‘e’ cambió a ‘a’. Para obtener el valor anterior de un campo que ha sido actualizado desde el registro, usted tendría que saber qué era el valor insertado antes que fuera cambiado así como todos los cambios subsecuentes. Para hacer esto una cadena completa es requerida, y por supuesto mucho esfuerzo para decodificar todo.
Ventajas de usar una herramienta de lectura de registros
Debido a la dificultad involucrada en leer la salida de fn_dblog y fn_dump_dblog, tener una herramienta pueda hacerlo por usted es esencial. Las herramientas de lectura de registros no tienen que ser implementadas con adelanto, dado que solamente depende de la disponibilidad del registro de Transacciones SQL Server. Puede ser instalado después de que haya ocurrido un evento, proveyendo la mejor posibilidad de rastrear al culpable o recuperar los datos perdido o dañados.
Conclusión
Leer el registro de transacciones ofrece la habilidad de auditar e investigar la actividad de la base de datos después del hecho. El formato en el cual el registro de transacciones SQL Server es escrito requiere un decodificado cuidadoso de cada artículo para entender qué valores han sido afectados. Microsoft no provee herramientas de lectura de registros aparte de 2 funciones que leen y muestran pero no decodifican los datos del registro.
Cuando se trata de investigar un evento inesperado después del hecho, leer el registro de transacciones es la única opción. Siempre que las bases de datos sean manejadas y operadas por humanos, siempre habrá la necesidad de poder leer el registro de transacciones SQL Server.
Referencias
- Logical architecture of the transaction log
- Factors which can delay log truncation
- Using fn_dblog, fn_dump_dblog, and restoring with STOPBEFOREMARK to an LSN
- Exploring the Transaction Log Structure
- Microsoft SQL Server Internals by Kalen Delaney
Traductor: Daniel Calbimonte
Ella es una creyente firme en el aprendizaje durante toda la vida y es una expositora regular en grupos de usuarios de SQL Server, compartiendo su pasión y conocimiento con cualquier persona que escuche.
Ver todas las entradas de Minette Steynberg
- Definición y descripción general interbloqueo de SQL Server - December 16, 2019
- 5 consejos prácticos de Transact-SQL que puede usted conozca (o no) - December 24, 2016
- Creando una estrategia de auditoría exitosa para sus bases de datos SQL Server - October 29, 2016