SQLShack

Este artículo explora el uso de varios archivos de Registro de transacciones de SQL Server y el proceso de eliminación del archivo de registro de transacciones secundario.

Introducción

De forma predeterminada, una base de datos SQL Server contiene un archivo de datos primario y un archivo de registro de transacciones. Es una buena práctica configurar varios archivos de datos y dividir los datos en varios archivos de datos. Podemos tener estos archivos de datos en unidades de almacenamiento separadas para tener múltiples IO. Ayuda a la gestión de datos, mejora el rendimiento, planifica los enfoques de copia de seguridad de acuerdo con los grupos de archivos.

También podemos tener varios archivos de registro de transacciones para una base de datos.

SQL Server utiliza varios archivos de registro de transacciones en orden secuencial. Supongamos que tenemos dos archivos de registro de transacciones. SQL Server utiliza un archivo de registro a la vez y, una vez que está lleno, SQL Server utiliza otro archivo de registro. Dado que SQL Server no lo usa en paralelo, no obtenemos ningún beneficio de rendimiento de él. Idealmente, deberíamos tener solo un archivo de registro de transacciones por base de datos.

Supongamos que tenemos una base de datos de producción y recibimos una alerta crítica de que el disco se está quedando sin espacio. Después de la investigación, hemos descubierto que esta unidad contiene un archivo de registro de transacciones. Debido a las transacciones activas, este archivo de registro de transacciones de SQL Server está lleno. Hemos intentado reducir el archivo de registro, pero no funcionó. También hacemos una copia de seguridad del registro de transacciones, pero debido a las transacciones activas, no pudo liberar el espacio necesario. Agregar otro archivo de registro de transacciones en un disco separado con espacio libre resolverá este problema.

Dado que SQL Server utiliza el modo serie para escribir datos en un archivo de registro de transacciones, debemos eliminar el archivo de registro adicional más adelante. Exploremos el proceso de eliminar un archivo de registro adicional.

Crear una base de datos con varios archivos de registro de transacciones de SQL Server

Conéctese a una instancia de SQL en SQL Server Management Studio. Haga clic con el botón derecho en el nodo Bases de datos en el panel Explorador de objetos y haga clic en el comando Nueva base de datos:

Se abre la Nueva ventana base de datos. Especifique un nombre de base de datos y agregue otro archivo de registro de transacciones. Para fines de prueba, hemos desactivado el crecimiento automático de los archivos de registro de transacciones. El tamaño inicial del archivo de registro de transacciones es de 8 MB:

Después de configurar todo, haga clic en el botón Aceptar para crear una base de datos en un directorio de archivos de registro/datos predeterminado. Crear una tabla e insertar datos en:

Ver el estado del archivo de registro virtual en archivos de registro de transacciones de SQL Server

En el artículo, Arquitectura de registro de transacciones de SQL Server, exploramos el interno del archivo de registro de transacciones. Cada archivo de registro de transacciones consta de varios archivos de registro virtuales. Un archivo de registro de transacciones es una combinación de varios archivos de registro virtuales (VLF). La siguiente captura de pantalla muestra la arquitectura física y lógica del archivo de registro:

SQL Server stats una base de datos con un VLF mínimo basado en el tamaño inicial del registro y el archivo de crecimiento automático (basado en la configuración de crecimiento automático). En la siguiente imagen, podemos ver el archivo de registro de transacciones de SQL Server:

En el caso de un único archivo de registro de transacciones, SQL Server utiliza una ruta de archivo de registro virtual circular. Podemos comprobar el número de archivos de registro virtuales y su estado utilizando lo siguiente:

1. DBCC LOGINFO (‘Base de datos’) – Es una instrucción antigua y funciona con todas las versiones de SQL Server
2. Vista de administración dinámica sys.dm_db_log_info (DBID). Está disponible en SQL Server 2016 SP2 o posterior

Cualquiera de los comandos se puede utilizar para la comprobación VLF. Para este artículo, utilizaremos la vista de gestión dinámica (DMV):

En la captura de pantalla anterior, hemos verificado que nuestra base de datos de muestra contiene dos archivos de registro de transacciones (file_id 2 y file_id 3).

• File_id 2 tiene VLF activo (vlf_active=1 y vlf_status=2)
• File_id 3 no tiene ningún VLF activo (vlf_active = 0 y vlf_status=2)

Insertemos algunos registros más en la tabla para que el VLF activo cambie:

El monitoreo del uso del espacio del registro de transacciones y el estado VLF se realizarán con DMV (sys. dm_db_log_space_usage):

• VLF estado

  1 2	SELECCIONE DB_name() como DatabaseName,File_ID como transaction_log_file_ID, vlf_active , vlf_status FROM sys.dm_db_log_info(DB_ID(‘MultileLogFiles’));

• registro de Transacciones de uso (utilizado y el espacio libre)

  1 2 3 4 5

  SELECCIONE total_log_size_in_bytes*1.0/1024/1024 total_log_size_in_MB, used_log_space_in_bytes*1.0/1024/1024 used_log_space_in_MB, (total_log_size_in_bytes – used_log_space_in_bytes)*1.0/1024/1024 COMO free_log_space_in_MB FROM sys.dm_db_log_space_usage;

se obtiene el siguiente resultado de las consultas anteriores:

• En el lado izquierdo, vemos que una vez que el archivo de registro principal (file_id 2) se llena, se mueve al siguiente archivo de registro (file_id 3). En este punto, tenemos VLF activo en archivos de registro primarios y secundarios. En una base de datos con un modelo de recuperación completo, necesitamos una copia de seguridad del registro de transacciones para que marque VLF como inactivo
• La salida del lado derecho (utilizando DMV) muestra el espacio utilizado del archivo de registro de transacciones 8.51 MB. Como recordarás, hemos establecido un tamaño de 8 MB para cada archivo de registro de transacciones y hemos deshabilitado el crecimiento automático del registro. Cuando el archivo de registro primario está lleno (a 8 MB), SQL Server cambiará al archivo de registro de transacciones secundario

Eliminar el archivo de registro de transacciones secundario de SQL Server

Para eliminar el archivo de registro de transacciones secundario (id de archivo 3), usaremos la instrucción Alter database editada.

Agregaremos la cláusula REMOVE FILE y especificaremos el archivo que queremos eliminar:

La ejecución de esta instrucción dará lugar al siguiente mensaje de error:

Nota: El archivo de registro de transacciones activo no se puede eliminar.

Anteriormente, vimos que una vez que el archivo de registro primario se llena, SQL Server utiliza el archivo de registro secundario. Tenemos que hacer un registro de transacciones secundario vacío, para poder eliminarlo.

En la base de datos SQL con un modelo de recuperación completo, utilizamos copias de seguridad de registros de transacciones para que SQL Server pueda truncar los registros. Es posible que necesitemos múltiples copias de seguridad de registros para esto dependiendo del tamaño del registro de transacciones, el VLF activo y la transacción activa.

Vamos a realizar una copia de seguridad completa de la base de datos y el registro de transacciones:

• Copia de seguridad completa de la base de datos

  1	archivos de registro múltiple de la base de datos de copia de seguridad a disk=’C:\Temp\MultipleLogFiles.bak’

• Copia de seguridad del registro de transacciones

  1	archivos de registro múltiple de copia de seguridad a disk = ‘C:\Temp\MultipleLogFiles_log.trn’

Una vez completada la copia de seguridad, verifique el estado de VLF. El VLF activo debe estar en el primer archivo de registro(primario) para que podamos eliminar el archivo de registro secundario. Hemos verificado que solo el archivo de registro primario (file_id 2) está activo (Estado VLF 2):

Ahora, el archivo de registro de transacciones secundario debe eliminarse sin problemas. Ejecutemos de nuevo la instrucción Alter database editada. Se eliminará el archivo de registro de transacciones secundario:

Una vez eliminado el archivo de registro de transacciones secundario, verifíquelo utilizando la interfaz gráfica de usuario y T-SQL con system view sys.archivos de base de datos:

El archivo de registro de transacciones secundario eliminado sigue presente según la siguiente captura de pantalla:

Ahora haga clic con el botón derecho en la base de datos y vea los archivos existentes. También vemos el archivo de registro de transacciones eliminado en la interfaz gráfica de usuario. ¿Pero por qué?

Ejecutemos de nuevo la instrucción Alter database editada y veamos si SQL Server elimina de nuevo el archivo de registro de transacciones.

Recibimos el mensaje de que SQL Server no pudo encontrar el archivo de registro especificado:

SQL Server elimina el archivo de registro de transacciones después de una copia de seguridad de registro posterior. Tomemos otra copia de seguridad de registro y verifiquemos que el archivo de registro de transacciones aún existe:

Verifique el archivo de registro de transacciones en métodos GUI y T-SQL. Vemos que el archivo de registro de transacciones eliminado no aparece ahora.

Conclusión

En este artículo, exploramos el uso del registro de transacciones secundario de SQL Server y el proceso de eliminarlo. Debe evitar utilizar varios archivos de registro de transacciones, especialmente en la base de datos de producción. Debe realizar una copia de seguridad de la base de datos antes de planificar cualquier actividad y hacerlo en horas que no sean de productividad.

• Autor
• Publicaciones recientes

Como capacitador certificado por MCSA y Microsoft en Gurgaon, India, con 13 años de experiencia, Rajendra trabaja para una variedad de grandes empresas que se centran en la optimización del rendimiento, el monitoreo, la alta disponibilidad y las estrategias e implementación de recuperación ante desastres. Es autor de cientos de artículos de autoridad sobre SQL Server, Azure, MySQL, Linux, Power BI, Ajuste de rendimiento, AWS/Amazon RDS, Git y tecnologías relacionadas que han sido vistos por más de 10 millones de lectores hasta la fecha.
Es el creador de una de las mayores colecciones gratuitas en línea de artículos sobre un solo tema, con su serie de 50 partes en grupos de disponibilidad Always On de SQL Server. En base a su contribución a la comunidad de SQL Server, ha sido reconocido con varios premios, incluido el prestigioso «Mejor autor del año» de forma continua en 2020 y 2021 en SQLShack.
Raj siempre está interesado en nuevos desafíos, por lo que si necesita ayuda de consultoría sobre cualquier tema cubierto en sus escritos, se puede contactar con él en rajendra.gupta16 @ gmail.com
Ver todos los mensajes de Rajendra Gupta

Últimos mensajes de Rajendra Gupta (ver todos)

• Usar plantillas ARM para implementar instancias de contenedores de Azure con imágenes de SQL Server Linux – 21 de diciembre de 2021
• Acceso a escritorio remoto para AWS RDS SQL Server con Amazon RDS Personalizado – 14 de diciembre de 2021
• Almacenar archivos de SQL Server en Almacenamiento persistente para Instancias de contenedores de Azure-Diciembre 10, 2021