Cómo evitar que el archivo de registro de transacciones en SQL Server crezca demasiado grande

A lo largo de los años, he asistido a numerosos clientes cuyo archivo de registro de transacciones se ha vuelto "demasiado grande", por lo que pensé que sería útil escribir al respecto. Este problema puede ser un problema que paraliza el sistema, pero se puede evitar fácilmente. Hoy analizaremos qué causa que tus registros de transacciones crezcan demasiado grandes y qué puedes hacer para controlar el problema.

Respaldo del registro de transacciones

Si el modelo de recuperación de tu base de datos está configurado como Completo o Registro masivo, es ABSOLUTAMENTE VITAL que realices respaldos del registro de transacciones junto con tus respaldos completos. Las bases de datos de SQL Server 2005 se configuran en el modelo de recuperación Completo de forma predeterminada, por lo que es posible que debas comenzar a crear respaldos de registro, incluso si aún no has tenido problemas. La siguiente consulta se puede utilizar para determinar el modelo de recuperación de las bases de datos en tu instancia de SQL Server.

SELECT name, recovery_model_desc

FROM sys.databases

Tu modelo de recuperación

Las bases de datos configuradas en el modo de recuperación Completo o Registro masivo retienen las transacciones inactivas en el archivo de registro de transacciones hasta que se procesa un punto de control y se realiza un respaldo del registro de transacciones. Ten en cuenta que un respaldo completo no elimina las transacciones inactivas del registro de transacciones. El respaldo del registro de transacciones realiza una troncación de la parte inactiva del registro de transacciones, lo que permite su reutilización para transacciones futuras. Esta troncación no reduce el tamaño del archivo, solo permite que el espacio en el archivo sea reutilizado (más adelante hablaremos de la reducción de archivos). Son estos respaldos del registro de transacciones los que evitan que tu archivo de registro de transacciones crezca demasiado grande. Una forma sencilla de realizar respaldos de registro de transacciones consistentes es incluirlos como parte de tu plan de mantenimiento de la base de datos.

Transacciones activas prolongadas

Una transacción prolongada puede evitar la troncación del archivo de registro de transacciones. Estos tipos de transacciones pueden variar desde transacciones bloqueadas hasta transacciones abiertas en espera de la entrada del usuario. En cualquier caso, la transacción garantiza que el registro permanezca activo desde el inicio de la transacción. Cuanto más tiempo permanezca abierta la transacción, mayor será el crecimiento del archivo de registro de transacciones. Para ver la transacción más larga en tu instancia de SQL Server, ejecuta la siguiente instrucción.

Cómo funciona el protocolo FTP y cómo resolver problemas relacionados con él

DBCC OPENTRAN

Respaldo y restauración

La troncación del registro no puede ocurrir durante una operación de respaldo o restauración. En SQL Server 2005 y versiones posteriores, es posible crear un respaldo del registro de transacciones mientras se realiza un respaldo completo o diferencial, pero el respaldo del registro no trunca el registro debido a que el registro de transacciones completo debe permanecer disponible para la operación de respaldo. Si un respaldo de la base de datos está evitando que el registro se trunque, es posible que desees cancelar el respaldo para solucionar el problema de inmediato.

Replicación transaccional

Con la replicación transaccional, la parte inactiva del registro de transacciones no se trunca hasta que las transacciones se hayan replicado en el distribuidor. Esto puede deberse a que el distribuidor está sobrecargado y tiene problemas para aceptar estas transacciones o tal vez porque el agente de lectura de registros debe ejecutarse con más frecuencia. Si DBCC OPENTRAN indica que tu transacción activa más antigua es una transacción replicada y ha estado abierta durante un período de tiempo significativo, este puede ser tu problema.

Espejo de bases de datos

El espejo de bases de datos es algo similar a la replicación transaccional, ya que requiere que las transacciones permanezcan en el registro hasta que el registro se haya escrito en disco en el servidor de espejo. Si la instancia del servidor espejo se retrasa respecto a la instancia principal, el espacio activo del registro crecerá. En este caso, es posible que debas detener el espejo de la base de datos, realizar un respaldo del registro que trunque el registro, aplicar ese respaldo del registro a la base de datos de espejo y reiniciar la replicación.

Espacio en disco

Es posible que simplemente te quedes sin espacio en disco y esto provoque un error en el registro de transacciones. Es posible que puedas liberar espacio en disco en la unidad que contiene el archivo de registro de transacciones de la base de datos mediante la eliminación o el movimiento de otros archivos. El espacio en disco liberado permitirá que el archivo de registro se agrande. Si no puedes liberar suficiente espacio en disco en la unidad que actualmente contiene el archivo de registro, es posible que debas mover el archivo a una unidad con suficiente espacio para manejar el registro. Si tu archivo de registro no está configurado para crecer automáticamente, debes considerar cambiar eso o agregar espacio adicional al archivo. Otra opción es crear un nuevo archivo de registro para la base de datos en una unidad diferente con suficiente espacio utilizando la sintaxis ALTER DATABASE YourDatabaseName ADD LOG FILE.

Redefinición del archivo

Una vez que hayas identificado el problema y hayas logrado truncar tu archivo de registro, es posible que debas reducir el tamaño del archivo a un tamaño manejable. Debes evitar reducir tus archivos de forma regular, ya que puede causar problemas de fragmentación. Sin embargo, si has realizado una troncación del registro y necesitas que tu archivo de registro sea más pequeño, necesitarás reducirlo. Puedes hacerlo a través de Management Studio haciendo clic derecho en la base de datos, seleccionando Todas las tareas, Reducir, y luego eligiendo Base de datos o Archivos. Si estoy utilizando la interfaz de Management Studio, generalmente selecciono Archivos y reduzco solo el archivo de registro.

Microsoft lanza servicio gratuito de almacenamiento en la nube para usuarios de Windows

También se puede hacer esto utilizando TSQL. La siguiente consulta encontrará el nombre de mi archivo de registro. Necesitaré esto para pasar al comando DBCC SHRINKFILE.

SELECT name

FROM sys.database_files

WHERE type_desc = 'LOG'

Una vez que tenga el nombre de mi archivo de registro, puedo usar el comando DBCC para reducir el archivo. En el siguiente comando trato de reducir mi archivo de registro a 1 GB.

DBCC SHRINKFILE ('SalesHistory_Log', 1000)

Debería su organización migrar a Google Apps desde Microsoft Exchange

Asegúrate también de que tus bases de datos NO estén configuradas para reducirse automáticamente. Las bases de datos que se reducen con intervalos continuos pueden tener problemas reales de rendimiento.

Hoy examinamos varias cosas diferentes que pueden hacer que tu archivo de registro de transacciones se vuelva demasiado grande y algunas ideas sobre cómo solucionar tus problemas. Estas soluciones van desde corregir tu código para que las transacciones no permanezcan abiertas tanto tiempo hasta crear respaldos de registro más frecuentes. Además de estas soluciones, también debes considerar agregar notificaciones a tu sistema para que te avisen cuando los archivos de tu base de datos estén alcanzando un límite determinado. Cuanto más proactivo seas en términos de alertas para este tipo de eventos, tendrás una mejor oportunidad de corregir el problema antes de que se convierta en un problema real.

En Newsmatic nos especializamos en tecnología de vanguardia, contamos con los artículos mas novedosos sobre Nube, allí encontraras muchos artículos similares a Cómo evitar que el archivo de registro de transacciones en SQL Server crezca demasiado grande , tenemos lo ultimo en tecnología 2023.