Los registros de transacciones en todas las versiones de SQL Server (a partir de SQL Server 2008 y hasta SQL Server 2019) tienden a crecer con el tiempo, lo que a veces puede llenar todo el espacio libre en el disco del servidor. Para evitar esto, SQL Server tiene una operación de truncado del registro de transacciones. El proceso de truncamiento se usa para liberar espacio mediante la eliminación de todos los archivos de registro virtuales (VLF) inactivos de los registros de transacciones de SQL Server.
Registros de transacciones de SQL Server y modelo de recuperación de base de datos
Nota. Los registros de transacciones se utilizan para registrar todas las transacciones antes de enviar los datos al archivo de datos de la base de datos. Los archivos de registro de transacciones son necesarios para revertir la base de datos al estado anterior. Por lo general, el diario almacena el número de secuencia, el tipo de cambio, las operaciones realizadas, etc. Lo más probable es que pueda usar su servidor SQL sin el registro de transacciones, pero existe la posibilidad de perder la base de datos en el futuro en caso de falla. .
Un registro transaccional consta de pequeños elementos lógicos llamados VLF (archivo de registro virtual). Puede averiguar su número ejecutando la siguiente consulta en el contexto de la base de datos de SQL Server:
DBCC LOGINFO
El número de líneas devueltas indica cuántos archivos virtuales está segmentado el registro. Él Estado El campo indica el estado actual del segmento. un valor de 0 significa que el segmento está actualmente libre y se puede reutilizar. 2 significa que el segmento está en uso. Si no hay segmentos libres y el crecimiento del registro de transacciones está permitido en la configuración de la base de datos de SQL Server, se incrementará y se crearán nuevos VLF. Si el tamaño del registro de transacciones es fijo o no hay suficiente espacio en disco, todas las operaciones para modificar la estructura de la base de datos o su contenido dejarán de estar disponibles. Lo más probable es que obtenga uno de los siguientes errores:
El registro de transacciones de la base de datos está lleno debido a 'OLDEST_PAGE'.
o
Error ODBC: (42000) – [Microsoft][ODBC SQL Server Driver][SQL Server]El archivo de registro de la base de datos 'base de datos' está lleno. Realice una copia de seguridad del registro de transacciones de la base de datos para liberar algo de espacio de registro.
En la mayoría de los casos, los administradores de SQL Server se encuentran con un desbordamiento de los registros de transacciones en SQL si no se realiza una copia de seguridad regular del registro de transacciones y el modelo de recuperación es BULK LOGGED o FULL.
Los archivos de registro se truncan automáticamente, según el modelo de recuperación utilizado en la configuración de SQL Server:
- Sencillo modelo de recuperación: los archivos de registro se truncan automáticamente después de llegar al punto de control (la opción más simple que requiere la administración de la base de datos). Cuando se utiliza el modelo de recuperación simple, el registro de transacciones se borra inmediatamente después de que se completa la transacción. El proceso de truncamiento es automático. En este modo, puede revertir su base de datos solo al tiempo de creación de la copia de seguridad completa de la base de datos;
- Lleno modelo de recuperación: el registro de transacciones no se borrará hasta que se complete una copia de seguridad del registro de transacciones. Sin truncamiento automático de registros, el registro de transacciones se truncará solo después de hacer una copia de seguridad del registro de transacciones: BACKUP LOG dbname TO DISK = 'dblog.bak' . Este modo brinda la mejor posibilidad de recuperación de datos después de una falla. En el modo Completo, el registro de transacciones (LDF) puede crecer (porque los cambios de la base de datos se acumulan en este registro). En el modelo de recuperación completa, todas las transacciones de SQL se escriben en los archivos de registro en el disco y se almacenan allí hasta que se crea la copia de seguridad. El almacenamiento de registros le permite volver a una copia anterior de la base de datos si es necesario, y puede realizar una restauración para cada transacción. En este caso, se restaura la copia de seguridad completa y luego puede resumir los registros en el momento que lo necesite (la base de datos se puede restaurar en casi cualquier momento);
- Registro masivo — este modo permite reducir el uso del espacio de registro mediante el uso de configuraciones de registro mínimas. En este modo, los archivos de registro de transacciones también se borran hasta que se ejecuta una copia de seguridad y tampoco hay truncamiento automático de registros.
Cuando se utiliza el modelo de recuperación completa para la base de datos, es necesario realizar una copia de seguridad del registro de transacciones con regularidad. De lo contrario, crecerá en exceso hasta que ocupe todo el espacio en disco y SQL Server comience a informar el error.
Puede verificar las estadísticas de uso del espacio del registro de transacciones para todas las bases de datos usando el siguiente comando T-SQL:
DBCC SQLPERF (LOGSPACE); GO
- Tamaño de registro (MB) – muestra el tamaño actual del registro de transacciones para la base de datos;
- Espacio de registro utilizado (%) – muestra el porcentaje ocupado por la transacción en el archivo de registro.
Consejo. Para garantizar un alto rendimiento de la base de datos de SQL Server, se recomienda colocar los registros de transacciones en discos separados con nivel RAID 1. Además, las mejores prácticas de la base de datos recomiendan preasignar espacio para los archivos de registro de transacciones. Esto ayudará a evitar eventos de crecimiento automático innecesarios.
Pero hay situaciones en las que el trabajo de truncado automático de registros de SQL por algún motivo no funciona y los registros ocupan todo el espacio disponible en el disco. Siempre sucede de repente en situaciones en las que necesita urgentemente espacio libre. En este caso, puede encontrar archivos de registro *.ldf de gran tamaño en el disco.
¿Cómo truncar registros de transacciones en MS SQL Server?
En este caso, este error aparece cuando se conecta a la base de datos MS SQL:
Proveedor OLE de Microsoft para SQL Server: el registro de transacciones de la base de datos "SuNombreDB" está lleno. Para averiguar por qué no se puede reutilizar el espacio en el registro, consulte la columna log_reuse_wait_desc es sys.database
HRESULT=80040E14, SQLSTATE=4 2000, nativo=9002
Esto significa que la unidad, donde se almacena el registro de transacciones de SQL, no tiene espacio y SQL no puede escribir nuevos datos de transacción. En este caso, puede truncar los archivos de registro SQL manualmente (usando la consulta SQL o desde la GUI de Management Studio).
Consejo. Debe realizar copias de seguridad de los registros de transacciones en un servidor SQL productivo con regularidad para evitar el desbordamiento del archivo de registro de transacciones y las operaciones de crecimiento automático.
Esta situación suele ocurrir cuando se utiliza un modelo de recuperación completa. En este modelo, los archivos de registro no se pueden borrar hasta que todas las transacciones no estén presentes en la copia de seguridad. Es necesario asegurarse de que está utilizando un número de secuencia de registro continuo (LSN) en los registros. En consecuencia, para el truncado, debe hacer una copia de seguridad completa de la base de datos o (más fácil y rápido) cambiarla temporalmente a Sencillo modo de recuperación. Es posible cambiar el modelo de recuperación de MS SQL Server sobre la marcha, pero para reducir los riesgos, es conveniente cambiar la base de datos al modo de solo lectura y realizar una copia de seguridad del registro de transacciones (si es posible).
Para truncar los registros de transacciones de SQL, abra el Estudio de administración de SQL Server (SSMS), seleccione la base de datos deseada (con un registro de transacciones grande), haga clic derecho sobre ella y seleccione Propiedades del menú contextual. Ir a Opciones y cambie el modelo de recuperación de la base de datos a Sencillo.
Luego, en el mismo menú contextual, vaya a la sección Tareas > Encoger > archivos. En Tipo de archivo seleccione Iniciar sesión, en Nombre del archivo campo especifique el nombre del archivo de registro. En acción de contracción escoger Reorganizar páginas antes de liberar espacio no utilizadoestablezca el tamaño deseado del archivo y haga clic en DE ACUERDO.
Puede encontrar tres opciones de reducción aquí:
- Liberar espacio no utilizado — esta opción recuperará el espacio no utilizado en el archivo de registro de transacciones y reducirá el archivo a la última extensión asignada. Permite reducir el tamaño del archivo sin mover datos;
- Reorganizar páginas antes de liberar espacio no utilizado — recupera espacio no utilizado e intenta reubicar filas en páginas no asignadas;
- Archivo vacío migrando los datos a otros archivos en el mismo grupo de archivos — se utiliza para mover todos los datos del archivo especificado a otros archivos en el mismo grupo de archivos. El archivo vacío se eliminará más tarde.
Después de completar una operación, cambie la base de datos Modo de restauración volver a Completo.
Uso del registro de transacciones truncado de Transact-SQL
Lo mismo se puede hacer desde SQL Query Analyzer con un script simple (el script funciona en todas las versiones de Microsoft SQL Server a partir de SQL Server 2008):
USE ″YourDBName″ ALTER DATABASE ″YourDBName″ SET RECOVERY SIMPLE; GO DBCC SHRINKFILE (″YourDBName_log″, ″Desired_size_in_MB″); --
Por ejemplo, para reducir un archivo de registro a 4 GiB (4096 MiB), use el comando:DBCC SHRINKFILE(MyDatabase_Log, 4096)
ALTER DATABASE ″YourDBName″ SET RECOVERY FULL GO
Consejo. Puede averiguar el nombre lógico del archivo de registro con la siguiente consulta SQL:
SELECT name FROM sys.master_files WHERE type_desc="LOG"
Asegúrese de hacer una copia de seguridad de su base de datos en el modelo de recuperación completa.
Otra forma de reducir el registro de transacciones de SQL es hacer una copia de seguridad de los registros de la base de datos con el comando:
BACKUP LOG YourDBName TO BackupDevice
Consejo. Después de truncar el registro de transacciones y reducirlo, asegúrese de hacer una copia de seguridad completa de su base de datos.
Para cambiar automáticamente todas las bases de datos (excepto las del sistema) al Sencillo modo de recuperación y ejecutar la reducción de registros de transacciones, puede usar el siguiente script Transact SQL:
declare @db_name nvarchar(100)
declare cursor_size_srv cursor for
SELECT name AS DBName
FROM sys.databases
where name not in ('tempdb','master','msdb','model)
ORDER BY Name;
OPEN cursor_size_srv
FETCH NEXT FROM cursor_size_srv INTO @db_name
WHILE (@@FETCH_STATUS=0)
BEGIN
exec ('declare @logname nvarchar(100)
USE [' + @db_name + ']
SELECT @logname = name FROM sys.database_files where type = 1
ALTER DATABASE ' + @db_name + ' SET RECOVERY SIMPLE
DBCC SHRINKFILE (@logname , 10, TRUNCATEONLY)')
ALTER DATABASE ' + @db_name + ' SET RECOVERY FULL
FETCH NEXT FROM cursor_size_srv INTO @db_name
END
CLOSE cursor_size_srv
DEALLOCATE cursor_size_srv
Además, puede encontrar la opción "Reducción automática" en las propiedades de su base de datos MS SQL. Cuando habilita esta opción, SQL Server verificará periódicamente el espacio no utilizado y reducirá el tamaño de la base de datos y los archivos de registro. Microsoft no recomienda usar esta opción para las bases de datos típicas y, si decidió usar la reducción automática, su base de datos debería estar ejecutándose en el modo de recuperación completa.
Puede habilitar esta opción en los parámetros de la base de datos en el Automático sección. Solo cambia el Reducción automática valor del parámetro a Verdadero. Después de habilitar la reducción automática, MS SQL realizará la compresión automática solo si el espacio no utilizado ocupa más del 25 % del tamaño total del volumen.
Este método es aplicable a todas las versiones compatibles de SQL Server: 2005, 2008, 2012, 2014, 2016, 2017 y 2019.
Esta es solo una de las formas de reducir rápidamente el tamaño de los registros. No es el mejor, pero es muy simple y efectivo.
Además, tenga en cuenta que cuando trunca los registros de transacciones de SQL según esta guía, todas las copias de seguridad anteriores realizadas en el modelo de recuperación completa se pueden descartar. Es por eso que este método debe usarse solo en casos críticos cuando no es posible limpiar el espacio en disco de otras maneras.
En los cursos de capacitación, Microsoft recomienda que solo se use el modo completo para bases de datos productivas. Sin embargo, muchos administradores avanzados configuran deliberadamente el modo de recuperación simple para sus bases de datos. En este caso, hay un aumento significativo en el rendimiento para operaciones de inserción masiva y cuando se trabaja con datos binarios grandes, lo que justifica cierta disminución en las posibilidades de respaldo y recuperación. Qué es más importante para su tarea: opciones de recuperación adicionales o rendimiento máximo, así que decida usted mismo.
En este artículo, analizamos cómo borrar los archivos de registro de transacciones de SQL Server para liberar rápidamente espacio en disco en su host de SQL Server.
Recomendado para ti
