El proceso de truncado no reduce el tamaño de un archivo de registro de transacciones.
Durante el proceso de truncado, sólo la porción activa del archivo del registro de transacciones SQL Server en línea es escaneado. Algunas partes de la porción escaneada son marcadas como inactivas y serán usadas como espacio libre para escribir las nuevas transacciones. No hay un cambio en el tamaño del registro de transacciones en línea porque las partes inactivas se mantienen intactas, nada es eliminado o borrado.
Cada registro de transacciones SQL Server está hecho de Virtual Log Files (VLFs). Durante el proceso de truncado, sólo el Registro lógico es escaneado. Un Registro lógico está hecho de VLFs activos. Un Número de Secuencia de Registro (Log Sequence Number, LSN) es usado para identificar de forma única a cada transacción en el registro de transacciones en línea. El MinLSN es el punto de inicio de la transacción activa más antigua en el registro de transacciones en línea.
El archivo del registro de transacciones SQL Server en línea es circular por organización interna. Cuando el registro alcanza el final del registro de transacciones, comienza de nuevo desde el principio sobrescribiendo las partes marcadas como inactivas.
El naranja representa el Registro lógico, el azul la parte truncada del registro de transacciones en línea lista para ser sobrescrita.
Mito: Tener múltiples registros de transacciones SQL Server incrementará el desempeño
Este mito está basado en la creencia de que tener múltiples archivos de registro de transacciones en línea resultará en escritura paralela de transacciones en los archivos y por lo tanto resultará en ganancia de desempeño. SQL Server no puede operar con más de un archivo de registro de transacciones en línea al mismo tiempo, así que cualquier tipo de I/O paralelo no es posible.
Tener múltiples archivos de registro de transacciones es necesario sólo en las situaciones donde el registro de transacciones SQL Server no puede grabar más transacciones debido a la falta de espacio libre en el disco.
Mito: El registro de transacciones SQL Server no crecerá si la base de datos está en el modelo de recuperación Simple.
Sin embargo, pasa 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 registro de transacciones en línea es limpiado automáticamente. SQL Server reclama automáticamente espacio del registro para mantener los requerimientos de espacio pequeños – pero eso no significa que no crecerá. El registro de transacciones en línea debe proveer suficiente información para retrotraer una base de datos, por lo tanto debe proveer suficiente espacio para toda la información necesaria. Como todas las transacciones deben ser escritas en el registro de transacciones, en el caso de un gran número de cambios en una transacción, puede no haber suficiente espacio en el registro, así que debe ser expandido.
Mito: A SQL Server transaction log backup will be the same size as the online transaction log itself
The online transaction log must have enough information to rollback active transactions, so some space is reserved for eventual rollbacks. If a rollback occurs, SQL Server doesn’t want to expand the online transaction log because if the expanding fails, the SQL Server database can become inconsistent or go into the Suspect mode. That’s why the online transaction log has some reserved space and is usually bigger than the SQL Server transaction log backup
Moreover, a transaction log backup contains only the transactions made after the last transaction log backup. If the online transaction log contains the transactions that have already been backed up, they will not be present in the new SQL Server transaction log backup, therefore the transaction log backup will be smaller for that amount of space
Myth: Una copia de seguridad del registro de transacciones SQL Server será del mismo tamaño que la transacción en línea en sí misma
El registro de transacciones en línea debe tener suficiente información para retrotraer transacciones activas, así que algo de espacio es reservado para eventualmente retrotraer más veces. Si una acción de retrotraer ocurre, SQL Server no quiere expandir el registro de transacciones en línea porque si la expansión falla, la base de datos SQL Server puede volverse inconsistente o entrar en modo Suspect. Es por eso que el registro de transacciones en línea tiene algo de espacio reservado y es usualmente más grande que la copia de seguridad del registro de transacciones SQL Server.
Por otra parte, un registro de transacciones contiene sólo las transacciones hechas después de la copia de seguridad del registro de transacciones. Si el registro en línea contiene las transacciones que ya han sido respaldadas, entonces la copia de seguridad será más pequeña para esa cantidad de espacio.
Mito: Los comandos TRUNCATE TABLE y DROP TABLE no son registrados en el registro de transacciones
Los valores eliminados exactos no son registrados en el registro de transacciones SQL Server en línea, sólo los IDs de las páginas que contenían los registros truncados son registrados. Estas páginas son marcadas para sobrescribir en el archivo de datos de la base de datos y los datos truncados desaparecerán cuando las nuevas transacciones sean escritas en estas páginas.
El mito está también basado en el hecho de que estos comando toman poco tiempo para ejecutarse, son casi instantáneos.
Mito: Mi SQL Server está muy ocupado. No quiero hacer copias de seguridad del registro de transacciones
Uno de las mayores operaciones más grandes y que consume más recursos en SQL Server es un evento de auto crecimiento del archivo del registro de transacciones. Al no crear copias de seguridad del registro de transacciones con la frecuencia requerida, el registro de transacciones en línea se llenará y tendrá que crecer. El tamaño de crecimiento por defecto es 10%. Mientras más ocupada esté la base de datos, el registro de transacciones crecerá más rápido si no se crean copias de seguridad.
Crear una copia de seguridad del registro de transacciones no bloquea la acción de registro en línea, pero un evento de auto crecimiento sí lo hace. Puede bloquear toda la actividad en el registro de transacciones en línea.
Mito: Una copia de seguridad del registro de transacciones no es necesaria para una restauración en un punto del tiempo. Una copia de seguridad completa de la base de datos es suficiente
Este mito viene de usar el 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 RESTORES LOG, y funciona bien cuando se usa con una copia de seguridad del registro de transacciones. El hecho de que puede ser usado con una copia de seguridad completa de la base de datos le hace creer que las copias de seguridad del registro de transacciones no son necesarias para recuperar a un punto de tiempo específico.
Un ejemplo de código T-SQL para restaurar la base de datos AdventureWorks al 31 de diciembre de 2012 a las 10:59 PM:
|
1 2 3 4 5 6 7 |
RESTORE DATABASE AdventureWorks FROM DISK = 'D:\AdventureWorks.bak' WITH NORECOVERY GO RESTORE LOG AdventureWorks FROM DISK = 'D:\AdventureWorks.bak' WITH RECOVERY, STOPAT = 'Dec 31, 2013 10:59:00 PM' GO |
Aunque la base de datos SQL Server no puede ser restaurada 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 registro de transacciones especificada.
RESTORE DATABASE successfully processed 24436 pages in 5.498 seconds (34.722 MB/sec). This backup set contains records that were logged before the designated point in time. The database is being left in the restoring state so that more roll forward can be performed. RESTORE LOG successfully processed 4 pages in 0.088 seconds (0.338 MB/sec).
Mito: Las copias de seguridad del registro de transacciones SQL Server no son necesarias para una recuperación exitosa de un desastre si la copia de seguridad completa de la base de datos se toma a diario
También depende de cuántos datos usted puede perder. Si puede permitirse perder hasta 24 horas de datos, entonces no necesita copias de seguridad del registro de transacciones y debería usar el modelo de recuperación Simple.
Si la información que usted puede perder se mide en minutos y horas, se deben crear copias de seguridad del registro de transacciones regularmente, ya que lo máximo que usted perderá es el tiempo entre la creación de copias de seguridad del registro de transacciones.
Mito: El encogimiento del registro de transacciones SQL Server dejará espacio libre en el registro de transacciones, así que no necesito crear copias de seguridad
La operación de encogimiento no es una buena práctica de mantenimiento porque no resuelve el problema del tamaño del registro de transacciones permanentemente. Después del encogimiento inicial, el registro de transacciones crecerá de nuevo. Ya que el evento de auto crecimiento es una de las operaciones de SQL Server más intensas, debería evitarse. El método recomendado para mantener el tamaño del registro de transacciones es crear copias de seguridad regularmente. O cambiar al modelo de recuperación Simple, si puede tolerar la pérdida de datos.
Recursos útiles
A SQL Server DBA myth a day: (30/30) backup myths
Transaction log myths
The Myth that DROP and TRUNCATE TABLE are Non-Logged
Traductor: Daniel Calbimonte
View all posts by Ivan Stankovic
- ¿Qué es el reflejo de bases de datos SQL Server? - December 18, 2015
- ¿Qué es el transvase de registros SQL Server? - December 18, 2015
- Una guía para principiantes acerca de los registros de transacciones SQL Server - December 4, 2015