Cómo optimizar los registros de transacciones en Microsoft SQL Server

Los registros de transacciones son un componente vital pero a menudo pasado por alto en la arquitectura de bases de datos. A menudo se olvidan porque no son algo que se mantenga activamente como el esquema contenido dentro de una base de datos. En este artículo examinaremos cómo se utilizan los registros de transacciones en Microsoft SQL Server, el mantenimiento y los posibles problemas con ellos, cómo se pueden utilizar para restaurar una base de datos y finalmente, cómo optimizarlos para obtener un mejor rendimiento.

Registros de transacciones en SQL Server

Un registro de transacciones es un registro secuencial de todos los cambios realizados en la base de datos, mientras que los datos reales se encuentran en un archivo separado. El registro de transacciones contiene suficiente información para deshacer todos los cambios realizados en el archivo de datos como parte de cualquier transacción individual. El registro registra el inicio de una transacción, todos los cambios considerados como parte de ella y luego la confirmación final o la cancelación de la transacción. Cada base de datos tiene al menos un registro físico de transacciones y un archivo de datos exclusivo para esa base de datos en la que se creó.

SQL Server mantiene un búfer de todos los cambios en los datos por razones de rendimiento. Escribe los elementos en el registro de transacciones de inmediato, pero no escribe los cambios en el archivo de datos de inmediato. Se escribe un punto de control en el registro de transacciones para indicar que se ha escrito completamente una transacción específica desde el búfer hasta el archivo de datos. Cuando se reinicia SQL Server, busca el punto de control más reciente en el registro de transacciones y aplica todas las transacciones que ocurrieron a partir de ese punto, ya que no se garantiza que se hayan escrito en el archivo de datos hasta que se ingrese un punto de control en el registro de transacciones. Esto evita que se pierdan transacciones que estaban en el búfer pero que aún no se habían escrito en el archivo de datos.

Recursos adicionales

SQL Server Consolidation.

En esta presentación, Brian Knight discute diferentes enfoques para administrar un entorno de SQL Server empresarial y reducir costos mediante la consolidación de SQL Server.

SQL Server Date and Time.

Cómo utilizar la recursión en T-SQL para calcular el factorial - TechRepublic

En los últimos meses, ha habido varias preguntas en el foro relacionadas con la fecha y hora en SQL Server. La información en este artículo de Database Journal puede resultar útil para aquellos que puedan estar luchando con el concepto.

Mantenimiento de los registros de transacciones

Los registros de transacciones pueden presentar problemas porque a menudo se olvida de ellos hasta que ocurre un problema. El registro continúa creciendo a medida que se realizan operaciones dentro de la base de datos. A medida que el registro sigue creciendo, el espacio en disco disponible disminuye. A menos que se tomen medidas rutinarias para evitarlo, el registro de transacciones eventualmente consumirá todo el espacio disponible asignado a él. Si el registro está configurado para crecer indefinidamente como es el valor predeterminado, crecerá hasta consumir todo el espacio físico disponible en el disco donde se almacena. Cualquiera de los escenarios hace que la base de datos deje de funcionar.

Las copias de seguridad regulares del registro de transacciones ayudarán a evitar que consuma todo el espacio en disco. El proceso de copia de seguridad trunca los registros de registro antiguos que ya no son necesarios para la recuperación. El proceso de truncamiento implica marcar los registros antiguos como inactivos para que puedan ser sobrescritos, lo que evita que el registro de transacciones crezca demasiado grande. Si no se realizan copias de seguridad frecuentes, entonces la base de datos debe configurarse con la opción "modelo de recuperación simple". El modelo de recuperación simple obligará a que el registro de transacciones se trunque automáticamente cada vez que se procese un punto de control.

El proceso de truncamiento que ocurre como resultado de una copia de seguridad o del punto de control marcará los registros de registro antiguos como inactivos para que puedan ser sobrescritos, pero no reduce el espacio en disco real asignado al registro de transacciones. Los registros mantendrán el espacio asignado incluso si no se utiliza. Aquí es donde la reducción de tamaño entra en juego en el mantenimiento. La reducción del registro implica eliminar los registros inactivos y reducir el tamaño físico del archivo de registro.

Un registro se reduce cuando se ejecuta una instrucción DBCC SHRINKDATABASE en la base de datos propietaria, se ejecuta DBCC SHRINKFILE en el registro de transacciones específico o se produce una operación de reducción automática si está habilitada en la base de datos. Al reducir el registro, primero se trunca para marcar los registros inactivos y luego se eliminan dichos registros inactivos. Dependiendo de cómo se intente reducir el registro, es posible que vea o no resultados inmediatos. Idealmente, la reducción debe realizarse de manera programada para que ocurra en momentos en los que la utilización de la base de datos sea menor.

Restauración de una base de datos

Las copias de seguridad del registro de transacciones se pueden utilizar para restaurar una base de datos a un punto específico en el tiempo. Una copia de seguridad del registro de transacciones por sí sola no es suficiente para restaurar una base de datos. También se requiere una copia de seguridad del archivo de datos. Primero se restauran las copias de seguridad del archivo de datos. Se restaura una copia de seguridad completa del archivo de datos y luego se restauran las copias de seguridad diferenciales del archivo de datos. Es muy importante, al restaurar la copia de seguridad del archivo de datos, no marcar la recuperación como completa, ya que de lo contrario no se podrán restaurar las copias de seguridad del registro de transacciones. Una vez que se completa la restauración del archivo de datos, se aplican las copias de seguridad de los registros de transacciones para devolver la base de datos a su estado deseado. Si ha habido varias copias de seguridad del registro de transacciones desde la última copia de seguridad de la base de datos, entonces las copias de seguridad del registro de transacciones deben restaurarse en el orden en que se crearon.

Cómo crear un formulario de entrada de datos en Microsoft Access

Existe otro proceso conocido como envío de registros (log shipping) que se puede utilizar para mantener una base de datos en espera caliente disponible. Cuando se configura el envío de registros, se copia una copia de seguridad completa de la base de datos a otro servidor. A partir de ese momento, los registros de transacciones se envían periódicamente al otro servidor, donde se restauran automáticamente en la base de datos en espera. Esto mantiene una copia caliente del servidor disponible. También se utiliza comúnmente para mantener actualizado un servidor de informes con los cambios de datos recientes. Se puede configurar otro servidor, conocido como servidor de monitoreo, para que supervise el envío y se asegure de que se realice en el intervalo programado. Si por alguna razón no se realiza, el servidor de monitoreo registrará un evento en el registro de eventos. Esto hace que el envío de registros sea una opción popular para incluirlo como parte de la planificación de recuperación ante desastres.

Optimización para obtener un mejor rendimiento

Los registros de transacciones juegan un papel vital en el funcionamiento de una base de datos. Como resultado, pueden tener un impacto directo en el rendimiento general del sistema. Hay ciertas configuraciones que se pueden hacer para optimizar el rendimiento de los registros de transacciones. El registro de transacciones es una escritura secuencial en el disco físico y no se realizan lecturas como parte del proceso de registro. Por lo tanto, si los registros se aíslan en un disco separado, se optimizará el rendimiento porque no habrá nada que interfiera con la escritura del registro de transacciones.

Otra optimización se relaciona con el crecimiento del tamaño del registro de transacciones. El registro se puede configurar para crecer como un porcentaje del tamaño total o a una tasa física establecida. Independientemente de la opción de crecimiento, el tamaño del crecimiento debe ser lo suficientemente grande como para evitar que el registro necesite expandirse continuamente. Si la tasa de crecimiento se establece como baja, es posible que el registro se vea obligado a expandirse continuamente, lo que ralentizará el rendimiento de la base de datos.

En Newsmatic nos especializamos en tecnología de vanguardia, contamos con los artículos mas novedosos sobre Gestión de datos, allí encontraras muchos artículos similares a Cómo optimizar los registros de transacciones en Microsoft SQL Server , tenemos lo ultimo en tecnología 2023.