Los 10 mitos más importantes del Logs de transacciones de SQL Server

Mito: el truncamiento del logs de transacciones SQL lo hará más pequeño

El proceso de truncamiento no reduce el tamaño de un archivo de log físico

Durante el proceso de truncamiento, solo se analiza la parte activa del archivo de log de transacciones de SQL Server en línea. Algunas partes de la parte escaneada se marcan como inactivas y se utilizarán como espacio libre para escribir nuevas transacciones. No hay un cambio en el tamaño del log de transacciones en línea porque las partes inactivas permanecen intactas, no se elimina ni elimina nada.

Cada log de transacciones de SQL Server está hecho de archivos de log virtuales (VLF). Durante el proceso de truncamiento, solo se escanea el log lógico. Un log lógico está hecho de VLF activos. Se utiliza un Número de secuencia de log (LSN) para identificar de manera única cada transacción en el log de transacciones en línea. El MinLSN es el punto de partida de la transacción activa más antigua en el log de transacciones en línea.

El archivo de log de transacciones de SQL Server en línea es circular por organización interna. Cuando el log llega al final del registro de transacciones, comienza de nuevo desde el principio sobrescribiendo las partes marcadas como inactivas.

Mitos Logs de Transacciones

El naranja representa el registro lógico, el azul es la parte truncada del registro de transacciones en línea listo para sobrescribirse

Mitos Logs de Transacciones fue aclarado.

Mito: Tener múltiples archivos de log de transacciones de SQL Server en línea aumentará el rendimiento

Este mito se basa en la creencia de que tener múltiples archivos de log de transacciones en línea dará como resultado la escritura paralela de transacciones en los archivos y, por lo tanto, dará como resultado un aumento del rendimiento. SQL Server no puede funcionar con más de un archivo de log de transacciones en línea a la vez, por lo que no es posible ningún tipo de E / S paralela.

Es necesario tener varios archivos de log de transacciones solo en las situaciones en las que el log de transacciones inicial de SQL Server no puede registrar más transacciones debido a la falta de espacio libre en el disco.

Mito: el log de transacciones de SQL Server no crecerá si la base de datos está en el modelo de recuperación simple

Sin embargo, ocurre solo en algunas situaciones específicas, cuando hay una transacción de larga duración o una transacción que crea muchos cambios.

En el modelo de recuperación simple, el log de transacciones en línea se borra automáticamente. SQL Server recupera automáticamente el espacio de log para mantener los requisitos de espacio pequeños, pero eso no significa que no crecerá. El log de transacciones en línea debe proporcionar suficiente información para una reversión de la base de datos, por lo tanto, debe proporcionar suficiente espacio para toda la información necesaria. Como todas las transacciones deben escribirse en el log de transacciones en línea, en caso de una gran cantidad de cambios en una transacción, es posible que no haya suficiente espacio en el log, por lo que debe expandirse.

Mito: una copia de seguridad del log de transacciones de SQL Server tendrá el mismo tamaño que el log de transacciones en línea

El log de transacciones en línea debe tener suficiente información para revertir las transacciones activas, por lo que se reserva un espacio para eventuales reversiones. Si se produce una reversión, SQL Server no quiere expandir el log de transacciones en línea porque si la expansión falla, la base de datos de SQL Server puede volverse inconsistente o pasar al modo Sospechoso. Es por eso que el log de transacciones en línea tiene un espacio reservado y generalmente es más grande que la copia de seguridad del log de transacciones de SQL Server.

Además, una copia de seguridad del log de transacciones contiene solo las transacciones realizadas después de la última copia de seguridad del log de transacciones. Si el log de transacciones en línea contiene las transacciones que ya se han respaldado, no estarán presentes en la nueva copia de seguridad del log de transacciones de SQL Server, por lo tanto, la copia de seguridad del log de transacciones será más pequeña para esa cantidad de espacio.

Mito: una copia de seguridad de base de datos completa o diferencial borra el log de transacciones en línea

Las partes inactivas del log de transacciones en línea de SQL Server están marcadas para borrarse solo cuando se crea una copia de seguridad del log de transacciones.

Las copias de seguridad completas y diferenciales de la base de datos no contienen mucha información del log de transacciones de SQL Server, solo las transacciones necesarias para recuperar la base de datos en un estado coherente. Estas transacciones no son una copia de seguridad del log de transacciones en línea, por lo tanto, estas transacciones no están marcadas para sobrescribirse en el log de transacciones en línea.

Mito: los comandos TRUNCATE TABLE y DROP TABLE no se registran en el log de transacciones en línea

Los valores eliminados exactos no se registran en el log de transacciones en línea de SQL Server, solo se registran los ID de las páginas que contenían los registros truncados. Estas páginas están marcadas para sobrescribirse en el archivo de datos de la base de datos y los datos truncados desaparecerán para siempre cuando las nuevas transacciones se escriban en estas páginas.

Este mito también se basa en el hecho de que estos comandos tardan poco tiempo en ejecutarse, son casi instantáneos.

Mito: Mi SQL Server está demasiado ocupado, no quiero hacer copias de seguridad del log de transacciones de SQL Server

Una de las mayores operaciones intensivas de rendimiento en SQL Server es un evento de crecimiento automático del archivo de log de transacciones en línea. Al no hacer copias de seguridad del log de transacciones con la frecuencia suficiente, el log de transacciones en línea se llenará y tendrá que crecer. El tamaño de crecimiento predeterminado es del 10%. Cuanto más ocupada esté la base de datos, más rápido crecerá el log de transacciones en línea si no se crean copias de seguridad del log de transacciones.

Crear una copia de seguridad del log de transacciones de SQL Server no bloquea el log de transacciones en línea, pero sí lo hace un evento de crecimiento automático. Puede bloquear toda la actividad en el log de transacciones en línea.

Mito: no se necesita una copia de seguridad del log de transacciones de SQL Server para una restauración en un momento determinado. Una copia de seguridad completa de la base de datos es suficiente

Este mito proviene del uso del comando RESTORE con la cláusula STOPAT para restaurar desde una copia de seguridad completa de la base de datos. La cláusula STOPAT especifica un punto en el tiempo para el comando RESTORE LOG, y funciona bien cuando se usa con una copia de seguridad del log de transacciones. El hecho de que se pueda usar con una copia de seguridad completa de la base de datos le hace creer que las copias de seguridad del log de transacciones no son necesarias para recuperarse en un momento específico.

Un ejemplo de código T-SQL para restaurar la base de datos AdventureWorks al 31 de diciembre de 2018 13:46 p.m.

RESTORE DATABASE AdventureWorks FROM DISK = ‘D:\AdventureWorks.bak’
WITH NORECOVERY
GO
RESTORE LOG AdventureWorks FROM DISK = ‘D:\AdventureWorks.bak’
WITH RECOVERY,
STOPAT = ‘Dec 31, 2018 13:46:00 PM’
GO

Aunque la base de datos de SQL Server no se puede restaurar a un punto en el tiempo, SQL Server no identifica claramente el problema y le permite usar la cláusula STOPAT sin una copia de seguridad del log de transacciones especificada.

RESTORE DATABASE procesó con éxito 24436 páginas en 5.498 segundos (34.722 MB / seg). Este conjunto de copia de seguridad contiene logs que se registraron antes del punto designado en el tiempo. La base de datos se deja en el estado de restauración para que se pueda realizar más avance. RESTORE LOG procesó con éxito 4 páginas en 0.088 segundos (0.338 MB / seg).

 

Mito: las copias de seguridad del log de transacciones de SQL Server no son necesarias para una recuperación ante desastres exitosa si la copia de seguridad completa de la base de datos se toma diariamente

También depende de la cantidad de datos que puede perder. Si puede permitirse perder hasta 24 horas de datos, entonces no necesita copias de seguridad del log de transacciones y debe usar el modelo de recuperación simple.

Si la información que puede perder se mide en minutos y horas, son necesarias copias de seguridad del log de transacciones, ya que lo máximo que perderá es el tiempo entre las copias de seguridad del log de transacciones.

Mito: la reducción del log de transacciones de SQL Server hará espacio libre en el log de transacciones en línea, por lo que no necesito crear copias de seguridad del log de transacciones.

La operación de reducción no es una buena práctica de mantenimiento porque no resuelve el problema del tamaño del log de transacciones de forma permanente. Después de la reducción inicial, el log de transacciones volverá a crecer. Como el evento de crecimiento automático es una de las operaciones más intensivas de SQL Server, debe evitarse. El método recomendado para mantener el tamaño del log de transacciones en línea es crear copias de seguridad del log de transacciones regularmente. O, cambiando al modelo de recuperación simple, si puede tolerar la pérdida de datos..