Categories
Database Backup

** Discover expert strategies for preventing and resolving MSSQL transaction log full errors (Error 9002). Learn rapid recovery techniques, VLF management, and architectural best practices for DBAs.

Per als administradors de bases de dades (DBA) i els enginyers de DevOps que gestionen Microsoft SQL Server, poques alertes provoquen tanta ansietat immediata com l’Error 9002: El registre de transaccions per a la base de dades ‘X’ està ple. Quan el registre de transaccions s’omple i no pot créixer, la base de dades esdevé efectivament de només lectura. Totes les operacions INSERT, UPDATE i DELETE s’aturen, les transaccions de l’aplicació fallen i la producció s’atura completament.

Entendre l’arquitectura subjacent del registre de transaccions de SQL Server, diagnosticar amb precisió la causa arrel i executar procediments de recuperació ràpida són habilitats crítiques per mantenir l’alta disponibilitat. Aquesta guia exhaustiva explora la mecànica del registre de transaccions, com resoldre un registre ple en una emergència i les millors pràctiques arquitectòniques per evitar que torni a passar.

Entendre l’arquitectura del registre de transaccions de SQL Server

Per solucionar eficaçment un registre de transaccions ple, primer heu d’entendre com SQL Server escriu i gestiona les dades.

Registre d’escriptura prèvia (WAL)

SQL Server utilitza un protocol de registre d’escriptura prèvia (Write-Ahead Logging, WAL). Sempre que es produeix una modificació de dades, el canvi s’escriu primer al registre de transaccions a la memòria i, a continuació, s’aboca al fitxer de registre físic al disc abans que les pàgines de dades reals s’actualitzin als fitxers de la base de dades (MDF/NDF). Això garanteix el compliment ACID (Atomicitat, Consistència, Aïllament, Durabilitat), assegurant que en cas d’error, SQL Server pugui reproduir (avançar) o desfer (revertir) les transaccions.

Fitxers de registre virtuals (VLF) i registre circular

Internament, el fitxer de registre de transaccions físic (LDF) es divideix en segments lògics més petits anomenats fitxers de registre virtuals (VLF). El registre de transaccions funciona de manera circular. A mesura que s’escriuen els registres, omplen un VLF i passen al següent.

Quan el registre arriba al final del fitxer físic, intenta tornar al principi. Tanmateix, només pot sobreescriure un VLF si aquest VLF està marcat com a inactiu. Si tots els VLF estan actius (és a dir, contenen registres que SQL Server encara necessita), el registre no pot tornar a començar. Si el creixement automàtic està activat i hi ha espai al disc, el fitxer físic creix. Si el disc està ple o el creixement automàtic està restringit, trobareu l’Error 9002.

Truncament del registre vs. Reducció del registre

Una idea errònia comuna és que truncar el registre redueix la mida del fitxer físic.
* Truncament del registre: El procés de marcar els VLF actius com a inactius, fent que l’espai estigui disponible per a la seva reutilització. No redueix la mida del fitxer LDF al disc.
* Reducció del registre (Shrinking): El procés de reduir físicament la mida del fitxer LDF i retornar espai al sistema operatiu.

En el model de recuperació completa (Full Recovery), el truncament del registre només es produeix quan es completa correctament una còpia de seguretat del registre de transaccions (suposant que cap altre procés mantingui el registre actiu).

Diagnosticar l’error «Registre de transaccions ple» (Error 9002)

Quan el registre està ple, el primer pas no és afegir espai al disc o reduir fitxers a cegues. Heu d’identificar per què el registre no es pot truncar. SQL Server proporciona un mecanisme integrat per dir-vos exactament què impedeix la reutilització del registre mitjançant la vista de catàleg sys.databases.

Executeu la següent comanda T-SQL per identificar el coll d’ampolla:

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

També podeu comprovar l’ús actual de l’espai dels vostres registres de transaccions utilitzant:

DBCC SQLPERF(LOGSPACE);

Estats comuns de log_reuse_wait_desc

  1. LOG_BACKUP: La base de dades està en el model de recuperació completa o de registre massiu, i no s’ha fet cap còpia de seguretat del registre de transaccions recentment. Aquesta és la causa més comuna.
  2. ACTIVE_TRANSACTION: Una transacció de llarga durada (p. ex., una reconstrucció massiva d’índexs o una transacció no confirmada oblidada) manté el registre actiu.
  3. REPLICATION / CDC: La replicació transaccional o la captura de dades modificades (CDC) està activada, i l’agent de lectura de registre encara no ha processat les transaccions.
  4. AVAILABILITY_REPLICA: En un grup de disponibilitat AlwaysOn, una rèplica secundària està desconnectada o se sincronitza massa lentament, forçant la rèplica primària a conservar els registres fins que s’hagin consolidat a la secundària.

Estratègies de recuperació ràpida: Resoldre el problema en producció

Depenent del log_reuse_wait_desc retornat, la vostra resposta d’emergència variarà. Aquí teniu les estratègies de recuperació ràpida per als escenaris més comuns.

Escenari 1: Còpies de seguretat del registre perdudes o fallides (LOG_BACKUP)

Si el tipus d’espera és LOG_BACKUP, la solució és senzilla: heu de fer una còpia de seguretat del registre de transaccions.

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

Un cop finalitzada la còpia de seguretat, els VLF inactius es truncaran i SQL Server reprendrà les operacions normals. Si la vostra unitat de còpia de seguretat està plena, potser haureu de fer la còpia en un recurs compartit de xarxa temporal o en un dispositiu nul (molt desaconsellat tret que la base de dades sigui fàcilment reproduïble, ja que trenca la cadena de registre):

-- ADVERTÈNCIA: Això trenca la cadena de registre i compromet la recuperació puntual.
-- Utilitzeu-ho només si és absolutament necessari i seguiu immediatament amb una còpia de seguretat COMPLETA.
BACKUP LOG [YourDatabaseName] TO DISK = 'NUL';

Escenari 2: Transaccions actives de llarga durada (ACTIVE_TRANSACTION)

Si una sola transacció s’ha estat executant durant hores, impedeix el truncament del registre durant tota la durada. Primer, identifiqueu la transacció infractora:

DBCC OPENTRAN('YourDatabaseName');

Aquesta comanda retorna la transacció activa més antiga i el seu ID de procés de servidor (SPID). Podeu obtenir més detalls sobre què està fent l’SPID consultant les vistes de gestió 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>;

Si la transacció és una consulta errònia o un procés bloquejat, potser haureu de finalitzar-la per alliberar el registre.

KILL <SPID>;

Nota: Matar una transacció massiva activarà una reversió (rollback), que pot trigar una quantitat de temps significativa i generarà temporalment activitat de registre addicional. No reinicieu el servei de SQL Server durant una reversió, o la base de dades entrarà en mode de recuperació en reiniciar-se.

Escenari 3: Assignació d’espai d’emergència (el disc està al 100% ple)

Si el fitxer LDF ha consumit tota la unitat, ni tan sols podeu executar una còpia de seguretat perquè SQL Server requereix una petita quantitat d’espai de registre per registrar l’esdeveniment de còpia de seguretat en si. En aquest escenari, heu d’afegir un fitxer de registre secundari en una unitat diferent amb espai disponible.

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

Això proporciona immediatament a SQL Server una mica d’aire. Un cop la base de dades estigui en línia, feu una còpia de seguretat del registre de transaccions, buideu el fitxer de registre secundari i elimineu-lo:

-- 1. Feu una còpia de seguretat del registre per truncar-lo
BACKUP LOG [YourDatabaseName] TO DISK = '...';

-- 2. Buideu el fitxer de registre temporal
DBCC SHRINKFILE (N'YourDatabaseName_Log2', EMPTYFILE);

-- 3. Elimineu el fitxer de registre temporal
ALTER DATABASE [YourDatabaseName] REMOVE FILE [YourDatabaseName_Log2];

Millors pràctiques per a la prevenció i gestió del registre de transaccions

La resolució de problemes reactiva és estressant i afecta els SLA. Implementar millors pràctiques arquitectòniques i operatives proactives és essencial per a l’estabilitat de les bases de dades empresarials.

1. Implementeu una estratègia de còpia de seguretat automatitzada i robusta

Si una base de dades està en el model de recuperació completa, les còpies de seguretat freqüents del registre de transaccions són obligatòries. Depenent del vostre objectiu de punt de recuperació (RPO) i del volum de transaccions, les còpies de seguretat del registre haurien de produir-se cada 5 a 15 minuts.

Les solucions de còpia de seguretat empresarials com CloudSave simplifiquen aquest procés significativament. En integrar-se directament amb SQL Server mitjançant VDI (Virtual Device Interface), CloudSave permet als DBA configurar còpies de seguretat del registre de transaccions d’alta freqüència basades en polítiques. Això garanteix que els registres es truncin contínuament, s’encriptin de manera segura i s’emmagatzemin fora del lloc o en emmagatzematge al núvol immutable, evitant l’estat d’espera LOG_BACKUP sense necessitat de treballs complexos de l’Agent SQL personalitzats.

2. Dimensioneu correctament el registre de transaccions i gestioneu els VLF

Confiar en el creixement automàtic per gestionar la mida del vostre registre de transaccions és un patró anti-perillós. Les operacions de creixement automàtic són costoses i pausen el processament de transaccions mentre el disc s’inicialitza a zero (tret que estigui activada la inicialització instantània de fitxers, que no s’aplica als fitxers de registre).

A més, els creixements automàtics freqüents i petits (p. ex., créixer un 10% o 50 MB cada vegada) condueixen a la fragmentació VLF. Un registre de transaccions amb milers de VLF petits degradarà greument els temps d’inici de la base de dades, el rendiment de la còpia de seguretat i la latència de replicació.

  • Pre-dimensioneu el registre: Analitzeu les vostres operacions de manteniment més grans (com les reconstruccions d’índexs) i pre-dimensioneu el fitxer LDF per adaptar-s’hi sense créixer.
  • Establiu un creixement automàtic fix: Canvieu el creixement automàtic d’un percentatge a una mida fixa (p. ex., 1 GB o 5 GB) per assegurar-vos que els VLF es creïn amb una mida saludable.

Podeu comprovar el vostre recompte de VLF utilitzant la següent consulta (per a 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'));

Si el vostre recompte de VLF és superior a 500, considereu esperar un període tranquil, reduir el registre a una mida mínima i tornar-lo a fer créixer manualment fins a la mida requerida en grans blocs.

3. Optimitzeu les operacions de manteniment d’índexs

Les reconstruccions d’índexs són operacions totalment registrades, fins i tot en el model de recuperació de registre massiu (depenent del tipus d’índex). Reconstruir un índex de 500 GB generarà almenys 500 GB de registres de transaccions.

Per mitigar l’inflament del registre durant el manteniment:
* Utilitzeu SORT_IN_TEMPDB = ON quan reconstruïu índexs. Això descarrega la fase d’ordenació a TempDB, reduint la càrrega al registre de transaccions de la base de dades de l’usuari.
* Canvieu de reconstruccions d’índexs a reorganitzacions d’índexs sempre que sigui possible, ja que les reorganitzacions són més eficients en el registre i es poden interrompre sense revertir tota l’operació.
* Feu operacions DELETE o UPDATE grans per lots. En lloc d’eliminar 10 milions de files en una sola transacció, elimineu-les en blocs de 50.000, confirmant i permetent que les còpies de seguretat del registre el truncin entre lots.

4. Monitoritzeu les topologies d’alta disponibilitat i replicació

En els grups de disponibilitat AlwaysOn, la rèplica primària no pot truncar el seu registre fins que els registres s’hagin consolidat en totes les rèpliques secundàries síncrones i asíncrones.

Si una rèplica secundària es desconnecta, o si l’amplada de banda de la xarxa no pot seguir el ritme de generació de transaccions de la primària, la cua d’enviament de la primària creixerà i el registre s’omplirà (tipus d’espera AVAILABILITY_REPLICA).

Implementeu un monitoratge robust per al comptador de rendiment SQLServer:Replica > Log Send Queue. Si una rèplica secundària es perd permanentment, heu d’eliminar-la del grup de disponibilitat o suspendre el moviment de dades per permetre que el registre primari es trunqui.

Conclusió

Trobar-se amb un registre de transaccions ple és un ritu de pas per als administradors de bases de dades, però no ha de resultar en un temps d’inactivitat prolongat. En entendre la mecànica del registre d’escriptura prèvia i els VLF, podeu diagnosticar ràpidament la causa arrel utilitzant sys.databases i aplicar l’estratègia de recuperació ràpida correcta.

L’estabilitat a llarg termini depèn d’allunyar-se de les solucions reactives. Pre-dimensionar els vostres fitxers de registre, optimitzar les rutines de manteniment i utilitzar plataformes de còpia de seguretat de nivell empresarial com CloudSave per aplicar horaris de còpia de seguretat de registre estrictes i automatitzats garantirà que els vostres registres de transaccions romanguin saludables, truncats i preparats per donar suport a càrregues de treball de producció d’alt rendiment.

Categories