En este artículo, vamos a cubrir las copias de seguridad del registro de transacciones de SQL Server, truncar y reducir las operaciones con una descripción general y algunos ejemplos que cubran todo lo discutido.

Si este artículo es el primero que está viendo a la serie del Registro de transacciones de SQL Server, le recomiendo que consulte los artículos anteriores (consulte la tabla de contenido a continuación), en los cuales describimos la estructura interna del Registro de transacciones de SQL Server, la función vital que desempeña el uso del Registro de transacción para mantener la base de datos en un estado coherente y así poder recuperar la base de datos corrupta o la tabla modificada por error en un momento específico. Así también discutimos en este artículo los tres modelos de recuperación, completo, simple y de registro masivo, que controlan cómo se escribirán las transacciones en el archivo de registro de transacciones de SQL Server y, finalmente, cómo poder administrar y monitorear el crecimiento del registro de transacciones de SQL Server.

Al compilar toda la información básica de los artículos anteriores, Nosotros ahora ya estamos listos para analizar en profundidad en este artículo la diferencia entre los conceptos de copia de seguridad, truncamiento y reducción del registro de transacciones de SQL Server y cómo poder realizar estas operaciones.

Copia de seguridad del registro de transacciones

Al tratar de configurar su base de datos con el modelo de recuperación simple, el registro de transacciones de SQL Server se va a marcar como inactivo y se va a truncar automáticamente después de confirmar la transacción activa. Este no es el caso con los modelos de recuperación de base de datos completa y de registro masivo. Cuando la base de datos se configura con el modelo de recuperación completa, el registro de transacciones de SQL Server en el archivo de registro de transacciones se marcará como inactivo después de confirmar la transacción, sin truncarse automáticamente, ya que estará esperando que se lleve a cabo una copia de seguridad del registro de transacciones. Tiene que recordar que solo es la copia de seguridad del registro de transacciones, pero NO la copia de seguridad completa de la base de datos, truncará los registros de transacciones del archivo de registro de transacciones y lo pondrá a disposición para su reutilización. Si no tomamos en cuenta una copia de seguridad del Registro de transacciones de la base de datos, el archivo del Registro de transacciones crecerá continuamente, sin truncamiento, hasta que se quede sin espacio libre.

La copia de seguridad del registro de transacciones de SQL Server solo se puede obtener de la base de datos cuando el modelo de recuperación de esa base de datos está lleno o se ha registrado en bloque. El modelo de recuperación de la base de datos se puede verificar desde la pestaña Opciones de la ventana Propiedades de la base de datos, como se muestra a continuación:

Si se intenta realizar una copia de seguridad del Registro de transacciones para una base de datos configurada con el modelo de recuperación Simple, la operación de copia de seguridad va a fallar con el siguiente mensaje de error:

Aparte de eso, la copia de seguridad del registro de transacciones va a requerir que se tome al menos una copia de seguridad completa de esa base de datos como punto de partida para la nueva cadena de copia de seguridad. Si usted intenta obtener una copia de seguridad del Registro de transacciones de una base de datos sin antes tener una copia de seguridad completa tomada previamente, la operación de copia de seguridad va a fallar y nos mostrara el siguiente mensaje de error:

Tenemos que hacer una copia de seguridad completa de la base de datos para poder realizar una copia de seguridad del registro de transacciones para esa base de datos. Vamos a usar el comando BACKUP DATABASE T-SQL para poder realizar la operación de copia de seguridad completa de la base de datos en nuestro ejemplo. Para poder obtener más información sobre las distintas formas y opciones para realizar copias de seguridad de bases de datos en SQL Server, puede consultar la serie de copias de seguridad y restauración de SQL Server. La copia de seguridad completa de la base de datos se puede obtener utilizando el script T-SQL a continuación:

Una vez que se haya realizado la copia de seguridad completa de la base de datos, vamos a comenzar a tomar las copias de seguridad del Registro de transacciones para la base de datos. La primera copia de seguridad del registro de transacciones va a obtener una copia de seguridad de todas las transacciones que ocurrieron en la base de datos desde la última copia de seguridad completa. La copia de seguridad del registro de transacciones se puede obtener utilizando el comando BACKUP LOG T-SQL a continuación:

Por otro lado, las copias de seguridad del Registro de transacciones que siguen a la primera copia de seguridad del Registro de transacciones van a tomar copias de seguridad de todas las transacciones que ocurrieron en la base de datos desde el momento en que se detuvo la última copia de seguridad del Registro de transacciones. La copia de seguridad completa y todas las siguientes copias de seguridad del registro de transacciones hasta que se tome una nueva copia de seguridad completa se denomina cadena de copia de seguridad. Esta cadena de respaldo es muy importante para poder recuperar la base de datos en un punto específico en el tiempo, en el caso de cualquier cambio realizado incorrectamente o corrupción de la base de datos. La frecuencia de la copia de seguridad del registro de transacciones va a depender de la importancia de sus datos, el tamaño de la base de datos y el tipo de carga de trabajo a la que sirve esta base de datos. En las bases de datos altamente transaccionales, se nos recomienda el poder aumentar la frecuencia de la copia de seguridad del Registro de transacciones, para así poder minimizar la pérdida de datos y truncar los Registros de transacciones para que así estén disponibles para su reutilización.

Si la base de datos está dañada, se nos recomienda el crear una copia de seguridad de registro de cola que nos va a permitir restaurar la base de datos al momento actual. Se usa una copia de seguridad de registro de cola para poder capturar todos los registros de registro que aún no se han realizado. Esto nos ayudara a poder prevenir cualquier pérdida de datos y a siempre mantener completa la cadena de registro.

Suponga que ha ejecutado la siguiente declaración DELETE por error sin proporcionar la cláusula WHERE. Esto quiere decir que se van a eliminar todos los registros de la tabla:

Si ha diseñado una solución de copia de seguridad adecuada, los datos se pueden recuperar fácilmente restaurando la base de datos al punto específico en el tiempo antes de ejecutar la instrucción DELETE. Esto puede hacerlo desde la ventana Restaurar base de datos, el Servidor SQL va a devolver la cadena de copia de seguridad completa que se toma de esa base de datos. Si usted conoce el archivo exacto que se toma directamente antes de la eliminación de datos, puede detenerse en ese archivo específico, como se muestra a continuación:

Pero si usted conoce el momento exacto de ejecución de la declaración DELETE, puede restaurar la base de datos a ese punto específico en el tiempo antes de la ejecución de la declaración DELETE, sin tener la necesidad de saber qué archivo de Registro de transacciones contiene ese punto en el tiempo. Esto se puede lograr haciendo clic en la opción Línea de tiempo y especifique el tiempo, como se muestra a continuación:

Registro de transacciones truncado

El truncamiento del Registro de transacciones de SQL Server es el proceso en el que todos los VLF que están marcados como inactivos se van a eliminar del archivo de Registro de transacciones de SQL Server y ya estarán disponibles para su reutilización. Si hay un único registro del registro activo en un VLF, el VLF general se considerará como registro activo y no se puede truncar.

El registro de transacciones de SQL Server, para la base de datos configurada con el modelo de recuperación simple, se puede truncar automáticamente si:

• Un punto de control del operador se dispara
• La transacción de la base de datos está confirmada

El registro de transacciones de SQL Server, para la base de datos que está configurada con el modelo de recuperación completo o de registro masivo, se puede llegar a truncar automáticamente:

• Después de poder realizar un proceso de copia de seguridad del Registro de transacciones, y el Registro de transacciones no está esperando una transacción activa o ninguna función de alta disponibilidad, como Reflejo, Replicación o Grupo de disponibilidad siempre activo.

• Cambie el modelo de recuperación de la base de datos a Simple
  Como por ejemplo, si modificamos a continuación el modelo de recuperación de la base de datos a Simple y realizamos un Punto de control directamente, el registro de Transacción se va a truncar automáticamente y estará disponible para su reutilización como se muestra a continuación:

  

• TRUNCATE_ONLY: Opción de copia de seguridad del registro de transacciones, que rompe la cadena de copia de seguridad de la base de datos y trunca los registros de transacciones disponibles. (Disponible solo antes de SQL Server 2008.)
  Si usted trata de truncar el Registro de transacciones de la base de datos utilizando la opción TRUNCATE_ONLY en una instancia de SQL Server en la versión 2008 y posterior, la declaración fallará y nos mostrará el siguiente mensaje de error:

  

Reducción del registro de transacciones

Cuando el archivo de registro de transacciones de la base de datos se nos trunca, el espacio truncado se liberará y estará disponible para su reutilización. Pero el tamaño del archivo del Registro de transacciones no se va a reducir, ya que el espacio truncado no se desasignará. Entonces, el proceso de recuperación del espacio de registro de transacciones por desasignar los VLFs libres y devolverlo de nuevo al sistema operativo se llama una operación de registro de transacciones de contracción.

La operación de reducción del archivo del Registro de transacciones solo se puede llevar acabo si hay espacio libre en el archivo del Registro de transacciones, que puede estar disponible la mayor parte del tiempo después de truncar la parte inactiva del Registro de transacciones. Una operación de reducción puede ser útil después de realizar una operación que crea una gran cantidad de registros de transacciones.

El archivo de registro de transacciones de una base de datos se puede reducir haciendo clic derecho en la base de datos y elija la opción Reducir -> Archivos en el menú Tareas, como se muestra a continuación:

En la página reducir archivo, tiene que cambiar el Tipo de archivo a Registro y elija el archivo de Registro de transacciones que logre reducir. En esta página, usted tiene tres opciones:

• Liberar espacio no utilizado en el archivo de registro de transacciones al sistema operativo y reduce el archivo a la última extensión asignada. Esto reduce el tamaño del archivo sin mover ningún dato.
• Liberar espacio no utilizado en el archivo de registro de transacciones al sistema operativo e intente reubicar filas en páginas no asignadas. Aquí, un valor debe ser especificado.

• Mueva todos los datos del archivo especificado a otros archivos en el mismo grupo de archivos, para poder eliminar el archivo vacío más adelante.

  

El mismo archivo de registro de transacciones se puede reducir usando la siguiente instrucción DBCC SHRINKFILE T-SQL:

No es posible reducir el archivo de registro de transacciones a un tamaño menor que el tamaño del archivo de registro virtual, incluso si este espacio no se utiliza. Esto se debe al hecho de que el archivo de registro de transacciones solo puede reducirse al límite del VLF. En este caso, el Motor de base de datos de SQL Server nos liberará tanto espacio como sea posible y luego va a emitir un mensaje informativo, como se muestra a continuación:

En el próximo artículo de esta serie, vamos analizar las mejores prácticas que se deben aplicar al registro de transacciones para poder obtener un rendimiento óptimo. ¡Estén atentos!

Table of contents

• Acerca de
• Últimas entradas

Ahmad Yaseen

Ahmad Yaseen is a Microsoft Big Data engineer with deep knowledge and experience in SQL BI, SQL Server Database Administration and Development fields.

He is a Microsoft Certified Solution Expert in Data Management and Analytics, Microsoft Certified Solution Associate in SQL Database Administration and Development, Azure Developer Associate and Microsoft Certified Trainer.

Also, he is contributing with his SQL tips in many blogs.

View all posts by Ahmad Yaseen

Últimas entradas de Ahmad Yaseen (ver todo)

• Restricciones en SQL Server: SQL NOT NULL, UNIQUE y SQL PRIMARY KEY - December 16, 2019
• Operaciones de copia de seguridad, truncamiento y reducción de registros de transacciones de SQL Server - November 4, 2019
• Qué elegir al asignar valores a las variables de SQL Server: sentencias SET vs SELECT T-SQL - November 4, 2019