Share This
Contacto
Desplazarse hacia abajo
Categorias
//SQL Server Internals: como funciona el transaction log

SQL Server Internals: como funciona el transaction log

SQL Server® ha existido durante más de 30 años y he estado trabajando con él durante casi el mismo tiempo. He visto muchos cambios a lo largo de los años (¡y décadas!) En este increíble producto. En estas publicaciones, compartiré con ustedes cómo veo algunas de las características o aspectos de SQL Server, a veces con un poco de perspectiva histórica. En esta publicación, les daré algunos de los jugosos datos sobre cómo trabajar con el registro de transacciones que comparto con mis clases de SQL Server Internals. En la primera parte, les contaré sobre los VLF, y en la segunda parte, les contaré por qué la reducción es un tema tan problemático. SQL Server divide su registro físico en varios fragmentos de administración denominados Archivos de registro virtuales o VLF. (La cuestión de cómo SQL Server determina cuántos de ellos existen y qué tan grandes son, no forma parte de los temas de esta publicación). El gráfico siguiente muestra seis VLF en un archivo de registro físico. Este sería el archivo .ldf que crea para su base de datos. SQL Server realiza un seguimiento del comienzo de la transacción activa más antigua, que es a lo que se refiere el LSN mínimo o Número de secuencia de registro. Todo lo que se encuentre entre el LSN mínimo y la posición actual en la que SQL Server está escribiendo (el final del registro) se considera el registro “activo”. Las transacciones activas incluyen más que solo transacciones abiertas. La primera transacción activa puede ser una transacción marcada para replicación que aún no se ha procesado, el comienzo de una operación de copia de seguridad del registro o el comienzo de un análisis de diagnóstico interno que SQL Server realiza periódicamente. Durante 20 años, desde los días de SQL Server 7.0, la herramienta principal que usamos para ver nuestros VLF fue un comando DBCC no documentado llamado DBCC LOGINFO, que devuelve una fila por cada VLF. Aunque técnicamente no está documentado, muchas personas escribieron sobre él en blogs a lo largo de los años. La mayoría de estos escritores incluyeron una imagen similar a la de abajo, que es la que uso en mis clases y talleres.

A partir de SQL Server 2017, existe una nueva herramienta documentada que proporciona la misma información que DBCC LOGINFO y un poco más. Es una función llamada sys.dm_db_log_info (). Usted puede leer sobre ello aquí. Lo bueno de tener una función con valores de tabla es que podemos consultar los resultados y limitar las columnas en la salida solo a lo que nos interesa. Me doy cuenta de que mucha gente no está ejecutando SQL Server 2017, así que al final de esta publicación, mostraré cómo puede capturar la salida DBCC LOGINFO en una tabla para realizar consultas. Quiero decirte cómo pensar en el estado de cada VLF. Los metadatos devueltos por DBCC LOGINFO o sys.dm_db_log_info incluyen una columna llamada status (o vlf_status) con un valor de 2, que significa ‘activo’. Pero algunas personas encuentran el término ‘activo’ un poco confuso, así que defino cuatro ‘estados diferentes ‘para un VLF. Activo Como se muestra en la figura anterior, la parte activa del registro comienza en el LSN mínimo que representa una transacción activa. La parte activa del registro termina en el último LSN escrito. Cualquier VLF que contenga cualquier parte del registro activo se considera VLF activo. Recuperable La parte del registro que precede a la transacción activa más antigua es necesaria solo para mantener una secuencia de copias de seguridad del registro para restaurar la base de datos a un estado anterior. Reutilizable Si no se mantienen las copias de seguridad del registro de transacciones o si ya ha realizado una copia de seguridad del registro, los VLF anteriores a la transacción activa más antigua no son necesarios y pueden reutilizarse. Truncar o hacer una copia de seguridad del registro de transacciones cambiará los VLF recuperables en VLF reutilizables.

No usado

Es posible que uno o más VLF en el extremo físico de los archivos de registro no se hayan usado todavía si no se ha realizado suficiente actividad registrada o si los VLF anteriores se marcaron como reutilizables y luego se reutilizaron antes de que SQL Server use los que están al final.

Cualquier VLF que contenga cualquier parte del registro activo se considera activo. En mi gráfico, serían cuatro de mis seis VLF, del segundo al quinto. Pero, ¿cuál es el estado de los primeros y los últimos?

Si un VLF no forma parte del registro activo, ¿aún lo necesitamos? La respuesta es, por supuesto, “Depende”. Depende de si hemos hecho una copia de seguridad del registro o no. Si no hemos hecho una copia de seguridad del registro, todavía tenemos que mantener el VLF, y luego está en lo que llamo recuperable o en el estado 2. El gráfico anterior no indica si se ha realizado una copia de seguridad del primer VLF o no. , por lo que no podemos saber si está en el estado 2 o en el estado 3.

¿Qué pasa con el último VLF en el gráfico? Es posible que ya se haya utilizado, por lo que podría estar en estado recuperable o reutilizable. O podría estar completamente sin usar. (Dado que comenzamos a escribir desde el principio físico del archivo, no hay forma de que el primer VLF no se utilice).

¿Qué puede decirnos sobre los estados de los VLF de las herramientas DBCC LOGINFO y sys.dm_db_log_info? Las herramientas no distinguen entre mis estados 1 y 2. Si un VLF no se puede sobrescribir, se llama “activo” y muestra un valor de estado de 2 tanto en DBCC LOGINFO como en sys.dm_db_log_info. Pero estos dos estados se comportan de manera diferente, así que quiero diferenciarlos. No podemos saber a partir de los metadatos si un VLF con estado 2 contiene transacciones activas o si está esperando una copia de seguridad. La única forma de saberlo es hacer una copia de seguridad, y si el VLF no estaba activo, su estado cambiará a 0, lo que significa que el VLF ahora es reutilizable. Técnicamente, no es la copia de seguridad lo que hace que cambie el estado. Es el hecho de que cuando finaliza la copia de seguridad, SQL Server realiza una operación TRUNCATE LOG. TRUNCATE LOG cambia todos los VLF que estaban en el estado Recuperable al estado Reutilizable.

Por supuesto, existe la situación en la que no nos importan las copias de seguridad de los registros y nunca vamos a hacer una. En ese caso, podemos poner la base de datos en un modelo de recuperación SIMPLE, y el registro se truncará cada vez que ocurra un punto de control. Nuevamente, eso cambiará todos los VLF recuperables a reutilizables.

Sin embargo, hay una forma de reconocer un VLF no utilizado. Si nunca se ha utilizado un VLF, tendrá un número de secuencia de archivo de 0. Los números de secuencia de archivo se asignan cuando se utiliza un VLF y se asigna un nuevo valor cuando se reutiliza. El número no es estático. Pero un VLF no utilizado tendrá un número de secuencia de archivo de 0 hasta la primera vez que se utilice. En DBCC LOGINFO, este valor está en la columna FSeqNo, y en sys.dm_db_log_info, la columna es vlf_sequence_number.

Hay algunas otras diferencias entre la herramienta antigua y la nueva. En DBCC LOGINFO, el tamaño de los VLF se muestra en bytes, pero en sys.dm_db_log_info, el tamaño se muestra en MB. La nueva herramienta también tiene una columna que muestra el primer LSN utilizado en cada VLF, pero ese es un tema para otro momento.

Una vez que tenga la información de VLF en forma tabular, puede consultarla. Por ejemplo, podría contar cuántos VLF tiene. O puede ver qué porcentaje son reutilizables. Una consulta un poco más compleja podría determinar en qué parte del registro estaba el número de secuencia de archivo más alto, lo que le daría una idea de cuán “encogible” es el registro. (Veremos más sobre la reducción en la segunda parte de esta serie). La nueva función en SQL Server 2017 devuelve resultados tabulares y es agradable y consultable. La siguiente consulta cuenta el número de VLF y devuelve los nombres de las bases de datos con más de 100 VLF.


SELECT [name], COUNT(l.database_id) AS 'vlf_count'
FROM sys.databases s
CROSS APPLY sys.dm_db_log_info(s.database_id) l
GROUP BY [name] HAVING COUNT(l.database_id) > 100;

Si no está ejecutando SQL Server 2017, puede usar la siguiente secuencia de comandos para crear una tabla que pueda capturar la salida de DBCC LOGINFO.

CREATE TABLE sp_LOGINFO
(RecoveryUnitId tinyint,
FileId tinyint,
FileSize bigint,
StartOffset bigint,
FSeqNo int,
Status tinyint,
Parity tinyint,
CreateLSN numeric(25,0) );
GO

Luego puede insertarlo en la tabla con el siguiente código:

TRUNCATE TABLE sp_LOGINFO;
INSERT INTO sp_LOGINFO
EXEC (‘DBCC LOGINFO’);
GO

Como DBA, o cualquier persona responsable de cualquier dato en una base de datos, sentirse cómodo con DBCC LOGINFO o el nuevo sys.dm_db_log_info será de gran ayuda para ayudarlo a comprender lo que está sucediendo en sus registros de transacciones. Este conocimiento puede ayudarlo a administrar los registros y mantenerlos en un estado óptimo.

  • 30 views
  • 0 Comment

Leave a Reply

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

DBA Developeres 2021 / All rights reserved.

Contacto
Close