1.2K
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 SQL Server 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. El 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:
The transaction log for database is full due to ‘OLDEST_PAGE’.
o
ODBC error: (42000) – [Microsoft][ODBC SQL Server Driver][SQL Server]The log file for database ‘database’ is full. Back up the transaction log for the database to free up some log space.
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:
- Simple 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:
Microsoft OLE Provider for SQL Server: The transaction log for database “YourDBName” is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column is sys.database HRESULT=80040E14, SQLSTATE=4 2000, native=9002
O
Action failed for Database MSSQL (Microsoft.SqlServer.Smo) An exception occurred while executing a Transact-SQL statement or batch (Microsoft.SqlServer.ConnectionInfo) The transaction log for database MSSQL is full due to “LOG_BACKUP” (Microsoft Sql Server, Error 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).
Cuando se produce el error 9002, todas las transacciones pendientes se revierten y SQL Server se detiene.
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 Simple 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 Simple.
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 elegir 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.
Nota. Consulte nuestro tutorial sobre cómo hacer una copia de seguridad y restaurar la base de datos MySQL usando la herramienta mysqldump.
¿Cómo mover archivos de registro de transacciones a otra unidad?
Si no puede expandir la unidad donde se almacena el registro de transacciones (LDF), puede moverlo a otra unidad que tenga suficiente espacio libre. Desafortunadamente, el registro requerirá que desconecte y adjunte una base de datos cuando se mueva (su base de datos SQL no estará disponible por un tiempo).
Ejecute el siguiente comando para obtener la ubicación actual del archivo de registro de transacciones y su tamaño máximo:
select file_id, type, type_desc, name, physical_name, state, state_desc, size from sys.database_files
En nuestro ejemplo, la base de datos tiene un archivo de registro de transacciones con la ruta E:msdbmysqldb.ldf.
Para obtener información sobre el tamaño actual del archivo de registro de transacciones y su porcentaje de uso, ejecute el comando T-SQL:
declare @logSpace table (
dbName varchar(100),
logSizeMB float,
logSpaceUsed float,
status int
)
insert into @logSpace
execute('dbcc sqlperf(''LogSpace'')')
select * from @logSpace where dbName="mysqldb"
También puede encontrar la ruta actual al registro de transacciones en las propiedades de la base de datos utilizando MS SQL Management Studio:
# Change the database to single user mode Use MASTER GO ALTER DATABASE mysqldb SET SINGLE_USER GO # Detach the DB sp_detach_db 'AdventureWorks' GO
Mueva el archivo de registro de transacciones a una nueva unidad utilizando el Explorador de archivos o cualquier administrador de archivos. Si el tamaño del registro de transacciones es muy grande, la tarea de movimiento puede llevar mucho tiempo.
Pista. Asegúrese de que la nueva unidad tenga suficiente espacio libre (asegúrese de verificar si la compresión NTFS no está habilitada en el disco de destino).
# Attach the database sp_attach_DB 'AdventureWorks', 'F:msdbmysqldb.mdf','M:msdbmysqldb.ldf' GO
Si un tiempo de inactividad prolongado de la base de datos de SQL Server no es aceptable, puede agregar un nuevo archivo de registro a la base de datos en otra unidad con suficiente espacio libre. Para agregar un archivo de registro adicional, use ALTER DATABASE [dbname]Comando AGREGAR ARCHIVO DE REGISTRO.
Por ejemplo, aquí agregaremos un archivo de registro adicional en otro disco para la base de datos mysqldb:
ALTER DATABASE mysqldb ADD LOG FILE ( NAME = mysqldb, FILENAME = 'E:mssqldatamysqldb2.ldf', SIZE = 1000MB, MAXSIZE = 2000MB, FILEGROWTH = 5% ); GO
Microsoft no recomienda el uso de varios archivos de registro para una única base de datos como solución a largo plazo. Esta solución lo ayudará a iniciar rápidamente la base de datos si se queda sin espacio en disco con los registros de transacciones. Después de investigar el motivo por el cual el registro de transacciones está lleno y no se puede truncar, debe deshabilitar dicho archivo.
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 Simple 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.
