Rexistro de transaccións de MSSQL cheo: Estratexias de prevención e recuperación rápida

Para os Administradores de Bases de Datos (DBAs) e enxeñeiros de DevOps que xestionan Microsoft SQL Server, poucas alertas provocan tanta ansiedade inmediata como o Erro 9002: O rexistro de transaccións para a base de datos ‘X’ está cheo. Cando o rexistro de transaccións se enche e non pode medrar, a base de datos convértese efectivamente en só lectura. Todas as operacións INSERT, UPDATE e DELETE detéñense, as transaccións da aplicación fallan e a produción detense por completo.

Comprender a arquitectura subxacente do rexistro de transaccións de SQL Server, diagnosticar con precisión a causa raíz e executar procedementos de recuperación rápidos son habilidades críticas para manter unha alta dispoñibilidade. Esta guía completa explora a mecánica do rexistro de transaccións, como resolver un rexistro cheo nunha emerxencia e as mellores prácticas arquitectónicas para evitar que volva suceder.

Comprender a arquitectura do rexistro de transaccións de SQL Server

Para solucionar eficazmente un rexistro de transaccións cheo, primeiro debes entender como SQL Server escribe e xestiona os datos.

Rexistro de escritura anticipada (WAL)

SQL Server utiliza un protocolo de Rexistro de escritura anticipada (Write-Ahead Logging, WAL). Sempre que ocorre unha modificación de datos, o cambio escríbese primeiro no rexistro de transaccións na memoria e, a continuación, envíase ao ficheiro de rexistro físico no disco antes de que as páxinas de datos reais se actualicen nos ficheiros da base de datos (MDF/NDF). Isto garante o cumprimento de ACID (Atomicidade, Consistencia, Illamento, Durabilidade), asegurando que, en caso de fallo, SQL Server poida repetir (avanzar) ou desfacer (reverter) as transaccións.

Ficheiros de rexistro virtuais (VLF) e rexistro circular

Internamente, o ficheiro de rexistro de transaccións físico (LDF) divídese en segmentos lóxicos máis pequenos chamados Ficheiros de rexistro virtuais (Virtual Log Files, VLF). O rexistro de transaccións funciona de forma circular. A medida que se escriben os rexistros, énchese un VLF e pásase ao seguinte.

Cando o rexistro chega ao final do ficheiro físico, intenta volver ao principio. Non obstante, só pode sobrescribir un VLF se ese VLF está marcado como inactivo. Se todos os VLF están activos (o que significa que conteñen rexistros que SQL Server aínda necesita), o rexistro non pode continuar. Se o crecemento automático está activado e hai espazo en disco dispoñible, o ficheiro físico medra. Se o disco está cheo ou o crecemento automático está restrinxido, atoparás o Erro 9002.

Truncamento de rexistro vs. Redución de rexistro

Unha idea errónea común é que truncar o rexistro reduce o tamaño do ficheiro físico.
* Truncamento de rexistro: O proceso de marcar os VLF activos como inactivos, facendo que o espazo estea dispoñible para a súa reutilización. Non reduce o tamaño do ficheiro LDF no disco.
* Redución de rexistro (Shrinking): O proceso de reducir fisicamente o tamaño do ficheiro LDF e devolver espazo ao sistema operativo.

No modelo de recuperación completa (Full Recovery), o truncamento do rexistro só ocorre cando se completa con éxito unha copia de seguridade do rexistro de transaccións (asumindo que ningún outro proceso mantén o rexistro activo).

Diagnosticar o erro “Rexistro de transaccións cheo” (Erro 9002)

Cando o rexistro está cheo, o teu primeiro paso non debe ser engadir espazo en disco ou reducir ficheiros sen máis. Debes identificar por que o rexistro non pode truncarse. SQL Server ofrece un mecanismo integrado para dicirche exactamente que está impedindo a reutilización do rexistro a través da vista de catálogo sys.databases.

Executa o seguinte comando T-SQL para identificar o pescozo de botella:

SELECT 
    name AS DatabaseName, 
    recovery_model_desc AS RecoveryModel, 
    log_reuse_wait_desc AS LogReuseWaitReason
FROM sys.databases
WHERE name = 'YourDatabaseName';

Tamén podes comprobar o uso actual do espazo dos teus rexistros de transaccións usando:

DBCC SQLPERF(LOGSPACE);

Estados comúns de log_reuse_wait_desc

1. LOG_BACKUP: A base de datos está no modelo de recuperación completa ou rexistrada masivamente (Bulk-Logged), e non se realizou unha copia de seguridade do rexistro de transaccións recentemente. Esta é a causa máis común.
2. ACTIVE_TRANSACTION: Unha transacción de longa duración (por exemplo, unha reconstrución masiva de índices ou unha transacción esquecida sen confirmar) mantén o rexistro activo.
3. REPLICATION / CDC: A replicación transaccional ou a captura de datos modificados (CDC) está activada, e o Axente de lectura de rexistros aínda non procesou as transaccións.
4. AVAILABILITY_REPLICA: Nun grupo de dispoñibilidade AlwaysOn, unha réplica secundaria está desconectada ou sincronizándose demasiado lentamente, o que obriga á réplica primaria a conservar os rexistros ata que se confirmen na secundaria.

Estratexias de recuperación rápida: Resolver o problema na produción

Dependendo do log_reuse_wait_desc devolto, a túa resposta de emerxencia variará. Aquí tes as estratexias de recuperación rápida para os escenarios máis comúns.

Escenario 1: Copias de seguridade do rexistro perdidas ou fallidas (LOG_BACKUP)

Se o tipo de espera é LOG_BACKUP, a solución é directa: debes facer unha copia de seguridade do rexistro de transaccións.

BACKUP LOG [YourDatabaseName] 
TO DISK = 'N:BackupsYourDatabaseName_EmergencyLog.trn' 
WITH COMPRESSION, STATS = 10;

Unha vez que se complete a copia de seguridade, os VLF inactivos truncaranse e SQL Server reanudará as operacións normais. Se a túa unidade de copia de seguridade está chea, quizais necesites facer a copia nun recurso compartido de rede temporal ou nun dispositivo nulo (moi desaconsellado a menos que a base de datos sexa facilmente reproducible, xa que rompe a cadea de rexistros):

-- AVISO: Isto rompe a cadea de rexistros e compromete a recuperación nun punto temporal.
-- Úsao só se é absolutamente necesario e fai inmediatamente unha copia de seguridade COMPLETA.
BACKUP LOG [YourDatabaseName] TO DISK = 'NUL';

Escenario 2: Transaccións activas de longa duración (ACTIVE_TRANSACTION)

Se unha única transacción estivo executándose durante horas, impide o truncamento do rexistro durante todo o tempo. Primeiro, identifica a transacción infractora:

DBCC OPENTRAN('YourDatabaseName');

Este comando devolve a transacción activa máis antiga e o seu ID de proceso de servidor (SPID). Podes obter máis detalles sobre o que está facendo o SPID consultando as vistas de xestión dinámica (DMV):

SELECT 
    s.session_id,
    s.login_name,
    s.host_name,
    r.start_time,
    r.status,
    r.command,
    t.text AS QueryText
FROM sys.dm_exec_sessions s
JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE s.session_id = <SPID_FROM_DBCC_OPENTRAN>;

Se a transacción é unha consulta errónea ou un proceso bloqueado, quizais necesites finalizala para liberar o rexistro.

KILL <SPID>;

Nota: Matar unha transacción masiva activará unha reversión (rollback), que pode levar moito tempo e xerará temporalmente actividade de rexistro adicional. Non reinicies o servizo de SQL Server durante unha reversión, ou a base de datos entrará en modo de recuperación ao reiniciar.

Escenario 3: Asignación de espazo de emerxencia (O disco está ao 100% cheo)

Se o ficheiro LDF consumiu toda a unidade, nin sequera podes executar unha copia de seguridade porque SQL Server require unha pequena cantidade de espazo de rexistro para rexistrar o propio evento de copia de seguridade. Neste escenario, debes engadir un ficheiro de rexistro secundario noutra unidade con espazo dispoñible.

ALTER DATABASE [YourDatabaseName]
ADD LOG FILE 
(
    NAME = N'YourDatabaseName_Log2',
    FILENAME = N'E:TempLogsYourDatabaseName_Log2.ldf',
    SIZE = 5GB,
    MAXSIZE = 50GB,
    FILEGROWTH = 1GB
);

Isto dálle inmediatamente a SQL Server un respiro. Unha vez que a base de datos estea en liña, fai unha copia de seguridade do rexistro de transaccións, baleira o ficheiro de rexistro secundario e elimínao:

-- 1. Fai unha copia de seguridade do rexistro para truncalo
BACKUP LOG [YourDatabaseName] TO DISK = '...';

-- 2. Baleira o ficheiro de rexistro temporal
DBCC SHRINKFILE (N'YourDatabaseName_Log2', EMPTYFILE);

-- 3. Elimina o ficheiro de rexistro temporal
ALTER DATABASE [YourDatabaseName] REMOVE FILE [YourDatabaseName_Log2];

Mellores prácticas para a prevención e xestión do rexistro de transaccións

A resolución de problemas reactiva é estresante e afecta aos SLA. Implementar mellores prácticas arquitectónicas e operativas proactivas é esencial para a estabilidade da base de datos empresarial.

1. Implementar unha estratexia de copia de seguridade robusta e automatizada

Se unha base de datos está no modelo de recuperación completa, as copias de seguridade frecuentes do rexistro de transaccións son obrigatorias. Dependendo do teu Obxectivo de Punto de Recuperación (RPO) e do volume de transaccións, as copias de seguridade do rexistro deberían realizarse cada 5 a 15 minutos.

As solucións de copia de seguridade empresariais como CloudSave simplifican este proceso significativamente. Ao integrarse directamente con SQL Server a través de VDI (Virtual Device Interface), CloudSave permite aos DBAs configurar copias de seguridade de rexistros de transaccións de alta frecuencia baseadas en políticas. Isto garante que os rexistros se truncan continuamente, se cifran de forma segura e se almacenan fóra do sitio ou en almacenamento na nube inmutable, evitando o estado de espera LOG_BACKUP sen requirir complexos traballos personalizados do SQL Agent.

2. Dimensionar correctamente o rexistro de transaccións e xestionar os VLF

Confiar no crecemento automático para xestionar o tamaño do teu rexistro de transaccións é un patrón perigoso. As operacións de crecemento automático son custosas e pausan o procesamento de transaccións mentres o disco se inicializa a cero (a menos que estea activada a Inicialización de ficheiros instantánea, que non se aplica aos ficheiros de rexistro).

Ademais, os crecementos automáticos pequenos e frecuentes (por exemplo, medrar un 10% ou 50MB cada vez) levan á fragmentación de VLF. Un rexistro de transaccións con miles de VLF minúsculos degradará gravemente os tempos de inicio da base de datos, o rendemento da copia de seguridade e a latencia da replicación.

• Pre-dimensiona o rexistro: Analiza as túas operacións de mantemento máis grandes (como reconstrucións de índices) e pre-dimensiona o ficheiro LDF para acomodalas sen medrar.
• Establece un crecemento automático fixo: Cambia o crecemento automático dunha porcentaxe a un tamaño fixo (por exemplo, 1GB ou 5GB) para garantir que os VLF se creen cun tamaño saudable.

Podes comprobar o teu reconto de VLF usando a seguinte consulta (para SQL Server 2017+):

SELECT 
    db_name(database_id) AS DatabaseName,
    COUNT(vlf_sequence_number) AS VLF_Count
FROM sys.dm_db_log_info(DB_ID('YourDatabaseName'));

Se o teu reconto de VLF supera os 500, considera esperar a un período de pouca actividade, reducir o rexistro a un tamaño mínimo e medralo manualmente de novo ata o seu tamaño requirido en grandes bloques.

3. Optimizar as operacións de mantemento de índices

As reconstrucións de índices son operacións totalmente rexistradas, mesmo no modelo de recuperación rexistrada masivamente (dependendo do tipo de índice). Reconstruír un índice de 500GB xerará polo menos 500GB de rexistros de transaccións.

Para mitigar a inchazón do rexistro durante o mantemento:
* Usa SORT_IN_TEMPDB = ON ao reconstruír índices. Isto descarga a fase de ordenación en TempDB, reducindo a carga no rexistro de transaccións da base de datos do usuario.
* Cambia de reconstrucións de índices a reorganizacións de índices sempre que sexa posible, xa que as reorganizacións son máis eficientes no rexistro e pódense interromper sen reverter toda a operación.
* Procesa en lotes as operacións grandes de DELETE ou UPDATE. En lugar de eliminar 10 millóns de filas nunha transacción, elimínaas en lotes de 50.000, confirmando e permitindo que as copias de seguridade do rexistro truncuen o rexistro entre lotes.

4. Monitorizar as topoloxías de alta dispoñibilidade e replicación

Nos grupos de dispoñibilidade AlwaysOn, a réplica primaria non pode truncar o seu rexistro ata que os rexistros se confirmaron en todas as réplicas secundarias síncronas e asíncronas.

Se unha réplica secundaria queda sen conexión, ou se o ancho de banda da rede non pode seguir o ritmo de xeración de transaccións da primaria, a cola de envío da primaria medrará e o rexistro encherase (tipo de espera AVAILABILITY_REPLICA).

Implementa unha monitorización robusta para o contador de rendemento SQLServer:Replica > Log Send Queue. Se unha réplica secundaria se perde permanentemente, debes eliminala do Grupo de Dispoñibilidade ou suspender o movemento de datos para permitir que o rexistro primario se trunque.

Conclusión

Atopar un rexistro de transaccións cheo é un rito de paso para os administradores de bases de datos, pero non ten por que resultar nun tempo de inactividade prolongado. Ao comprender a mecánica do Rexistro de escritura anticipada e os VLF, podes diagnosticar rapidamente a causa raíz usando sys.databases e aplicar a estratexia de recuperación rápida correcta.

A estabilidade a longo prazo depende de afastarse das correccións reactivas. Pre-dimensionar os teus ficheiros de rexistro, optimizar as rutinas de mantemento e utilizar plataformas de copia de seguridade de nivel empresarial como CloudSave para aplicar horarios de copia de seguridade de rexistros estritos e automatizados garantirá que os teus rexistros de transaccións permanezan saudables, truncados e listos para soportar cargas de traballo de produción de alto rendemento.