¿Qué es un registro de transacciones?

Un registro de transacciones es un archivo – parte integral de toda base de datos SQL Server. Contiene registros producidos durante el proceso de registro en una base de datos SQL Server. El registro de transacciones es el componente más importante de una base de datos SQL Server cuando se trata de recuperaciones de desastres – sin embargo, debe estar no corrupto. Después de cada modificación de la base de datos – ocurrencia de transacción, un registro es escrito en el registro de transacciones. Todos los cambios son escritos secuencialmente.

¿Qué almacena un registro de transacciones SQL Server?

Un registro de transacciones almacena cada transacción hecha a una base de datos SQL Server, excepto algunas que son mínimamente registradas como BULK IMPORT o SELECT INTO. Internamente está dividido en partes más pequeñas llamadas Archivos de Registros Virtuales (Virtual Log Files, VLFs). Cuando un VLF se llena, el registro continúa en el siguiente registro de transacciones disponible. El archivo de registro de transacciones puede ser representado como un archivo circular. Cuando el registro llega al final del archivo, inicia de nuevo desde el principio, pero sólo si todos los requerimientos han sido cumplidos y las partes inactivas han sido truncadas. El proceso de truncar es necesario para marcar todas las partes inactivas de modo que puedan ser usadas de nuevo y sobrescritas.

Un registro ya no es necesario en el registro de transacciones si todos los siguientes son verdaderos:

• La transacción de la que es parte se ha enviado
• Las páginas de la base de datos que cambió han sido todas escritas un disco por un punto de control
• El registro no es necesario para una copia de seguridad (completa, diferencial o de registro)
• El registro no es necesario para ninguna característica que lee el registro (como bases de datos en espejo o replicación) [1]

El registro lógico es una parte activa del registro de transacciones. Un Log Sequence Number (LSN) identifica cada transacción en el registro de transacciones. EL MiniLSN es el punto de partida de la transacción activa más antigua en el registro de transacciones en línea.

¿Puede funcionar una base de datos SQL Server sin un registro de transacciones?

No, eso no es posible debido al diseño de SQL Server y al cumplimiento de ACID. ACID significa atomicidad, consistencia, aislamiento (isolation) y durabilidad. Todas las transacciones deben cumplir con estas características:

• Una transacción atómica o está completa o no inició
• Una transacción tiene consistencia en el estado del sistema asegurándose de que al final de cada transacción el sistema está en un estado válido
• Cuando una transacción está aislada, aparece como la única acción que el sistema está manejando al mismo tiempo
• Una transacción es durable, lo que significa que una vez que ha sido exitosamente completada, todos los cambios que ha hecho al sistema son permanentes [2]

¿Puede una base de datos SQL Server tener más de un registro de transacciones?

Sí, eso es posible, pero sólo es recomendado en situaciones específicas. Añadir múltiples registros de transacciones no mejorará el desempeño de la base de datos SQL Server de ninguna manera. La escritura puede ocurrir sólo en un archivo al mismo tiempo, así que operaciones I/O paralelas no son posibles.

Tener múltiples registros de transacciones es recomendado sólo si el primero está lleno o el disco se está quedando sin espacio. De cualquier manera, estos problemas deberían tratarse más antes y manejarse creando copias de seguridad del registro de transacciones y monitoreando el espacio disponible en el disco.

¿Por qué el registro de transacciones SQL Server está creciendo?

Cada transacción es seguida por el registro en el registro de transacciones en línea. Durante el trabajo de SQL Server, el registro de transacciones crece si los cambios son hechos en una base de datos, por tanto mantener el registro de transacciones es crucial para una operación apropiada de SQL Server.

Hay tres tipos de recuperación disponibles en SQL Server, dependiendo de cuál es usado, el crecimiento del registro se manifiesta de manera diferente:

• Modelo de recuperación Simple – las copias de seguridad del registro de transacciones no están soportadas. El proceso de truncado es automatizado y el espacio es reclamado para volver a usarlo. Hay riesgo de pérdida de datos porque los cambios desde la copia de seguridad de la base de datos más reciente están expuestos. En la recuperación Simple hay pocas posibilidades para que el registro de transacciones crezca – sólo en situaciones específicas donde hay una transacción de larga duración o una transacción que crea muchos cambios.
• Modelo por medio de Registros de Operaciones Masivas – Las copias de seguridad del registro de transacciones son soportadas y necesarias de forma regular. No hay un proceso automatizado de truncamiento de registros de transacciones, las copias de seguridad del registro de transacciones deben ser hechas regularmente para marcar espacio no utilizado para sobrescribir. El modelo por medio de registros de operaciones masivas reduce el uso de espacio del registro de transacciones usando un registro mínimo para la mayoría de las operaciones masivas.
• Modelo de recuperación Completo – las copias de seguridad del registro de transacciones son soportadas y necesarias de forma regular. No hay un proceso automatizado de truncamiento de registros de transacciones, las copias de seguridad de registros de transacciones deben ser hechas regularmente para marcar el espacio no usado disponible para sobrescribir. En la recuperación Completa hay una gran posibilidad de que el registro de transacciones crezca porque todas las transacciones son registradas.

¿Cómo mantener un registro de transacciones en SQL Server?

El mantenimiento del registro de transacciones es una tarea importante en la administración de SQL Server. Se recomienda monitorear diariamente o incluso más frecuentemente si la base de datos SQL Server tiene una gran cantidad de tráfico. El espacio del registro de transacciones puede ser monitoreado usando el comando DBCC SQLPREF:

• Database Name – El nombre de la base de datos para las estadísticas mostradas
• Log Size (MB) – El tamaño actual asignado al registro. Este valor es siempre más pequeño que la cantidad originalmente asignada para el espacio del registro porque el Motor de la Base de Datos reserva una pequeña cantidad de espacio de disco para la información de cabecera interna.
• Log Space Used (%) – Porcentaje del archivo de registro actualmente ocupado con información del registro de transacciones
• Status – El estado del archivo de registro. Siempre 0 [3]

El registro de transacciones debería ser respaldado de forma regular para evitar la operación de crecimiento automática y para llenar el archivo del registro de transacciones. El espacio en un registro de transacciones puede ser truncado (limpiado) a través de SQL Server Management Studio seleccionando Transaction Logs como tipo de copia de seguridad o a través de CLI ejecutando el siguiente comando:

El espacio respaldado puede ser reutilizado y será rescrito con nuevas transacciones. Algunas operaciones no pueden ser combinadas y deben ser ejecutadas separadamente.

¿Necesito copias de seguridad del registro de transacciones SQL Server?

Sí, ese es uno de los recursos más importantes cuando se trata de recuperación de desastres. Las copias de seguridad no son necesarias (y disponibles) sólo si el modelo de recuperación Simple es usado – sino que también cuando pérdida de datos. La mayoría de los Administradores de Bases de Datos usan intervalos de 15 minutos o incluso menos para bases de datos SQL Server de alto tráfico. Las copias de seguridad del registro de transacciones son importantes porque cuando marcan VLFs inactivos, ellos pueden ser usados para escribir nuevas transacciones.

Referencias

• Understanding Logging and Recovery in SQL Server
• ACID properties
• DBCC SQLPERF (Transact-SQL)

Traductor: Daniel Calbimonte