Cuando SQL Server arranca después de una caída, arranca una fase de recuperación o recovery. Pero no es que se ponga a “comprobar tablas” como quien revisa una hoja de Excel después de un apagón. Tampoco compara alegremente el MDF con el LDF buscando diferencias a ojo. Lo que hace es mucho más serio, ejecuta un proceso de recuperación basado en el transaction log, en los LSN y en unas reglas bastante estrictas que permiten devolver cada base de datos a un estado transaccionalmente consistente.
Aquí, la explicación habitual de las tres fases (Analysis, Redo y Undo) sirve para una introducción, pero si nos quedamos ahí, estamos mirando solo la superficie e ignorando lo interesante. Y, como suele pasar en SQL Server, lo interesante vive en el barro: páginas, log records, checkpoints, cadenas de transacciones, dirty pages, pageLSN, Compensation Log Records y decisiones internas que explican por qué una base puede tardar segundos en recuperarse o dejarte mirando el error log sin poder hacer nada mientras sientes la presión de tener al jefe mirándote a ti (a todos nos ha pasado).
Antes de entender el recovery hay que entender una verdad incómoda, SQL Server puede tener datos confirmados que aún no están en los data files, y puede tener datos no confirmados que ya están en los data files. Si esta frase te asusta, mejor sigue leyendo.
El modelo real: steal/no-force y por qué necesitamos Redo y Undo
SQL Server usa un modelo de escritura que, simplificando mucho pero sin deformarlo, se puede describir como steal/no-force.
No-force significa que cuando una transacción hace COMMIT, SQL Server no está obligado a escribir inmediatamente en el MDF o NDF todas las páginas de datos modificadas por esa transacción. Lo que debe quedar persistido es el log necesario para garantizar la durabilidad. La página de datos puede seguir en memoria como dirty page y escribirse más tarde, cuando el checkpoint, el lazy writer u otro mecanismo decida que toca sacarla del buffer pool.
Esto, que tanto optimiza el rendimiento, explica también por qué existe el Redo. Si una transacción hizo commit, su log record de commit llegó a disco, pero las páginas modificadas no se habían vaciado todavía al data file, después de una caída SQL Server debe rehacer esos cambios. No porque le apetezca repetir trabajo, sino porque el log promete que esa transacción sobrevivió.
Steal significa que SQL Server puede escribir a disco una página modificada por una transacción que aún no ha hecho commit. Esto ocurre en casos de presión de memoria porque el buffer pool no es infinito. Si una página sucia necesita salir de memoria, puede escribirse al data file siempre que se cumpla la regla de Write-Ahead Logging.
Esto explica por qué existe el Undo. Si una transacción estaba abierta cuando el servidor cayó, algunas de sus modificaciones podrían estar ya en los data files. Como esa transacción no se confirmó, esos cambios deben desaparecer durante la recuperación.
Aquí está la clave que muchos resúmenes no cuentan: Redo existe por no-force; Undo existe por steal. Sin esas dos ideas, Analysis, Redo y Undo son solo tres palabras bonitas que memorizas para presumir en una entrevista técnica.
Write-Ahead Logging: el log manda, el data file obedece
La regla básica de Write-Ahead Logging es sencilla: antes de que una página de datos modificada pueda escribirse en disco, los log records que describen esos cambios deben haberse “endurecido” (persistido, escrito) en el transaction log. Primero el log, luego la página de datos. Siempre. El motor puede retrasar la escritura del data file, pero no puede escribir una página con cambios cuyo log correspondiente no haya llegado antes a disco.
Esto permite que el recovery sea posible. Si después de una caída SQL Server encuentra una página en disco que no tiene ciertos cambios, puede rehacerlos desde el log. Si encuentra una página en disco que sí tiene cambios de una transacción incompleta (no está el commit en el log), puede deshacerlos también usando el log. El LDF no es un fichero auxiliar sin importancia; es la fuente de verdad de la recuperación. El MDF contiene páginas. El LDF contiene la historia necesaria para saber qué hacer con ellas.
Log Sequence Number: Un identificado con mucho significado
Cada log record tiene un LSN (Log Sequence Number) que identifica una posición lógica dentro del transaction log. En herramientas internas o no documentadas podemos verlo en formato de tres partes, algo parecido a 0000002a:000001f0:0001. No hace falta enamorarse del formato, pero sí entender el concepto, los LSN ordenan la historia de la base de datos.
Las páginas de datos también guardan un LSN en su cabecera, conocido como pageLSN. Ese valor indica el último log record aplicado a esa página. Durante Redo, esta comparación es fundamental. Si el log record tiene un LSN mayor que el pageLSN, probablemente esa modificación no está en la página y debe aplicarse. Si el pageLSN ya es igual o superior, SQL Server puede saltarse ese redo para esa página.
Ese detalle es clave para la velocidad de recuperación. Redo no es “aplicar todo a lo bruto”. Redo es idempotente. Puede recorrer log records y decidir si una página necesita o no necesita esa operación. Esto evita aplicar dos veces el mismo cambio y convertir la recuperación en una trituradora de consistencia. SQL Server no repite una modificación porque sí, compara la historia del log con la historia que la página dice tener.
¿Qué hay realmente en el transaction log?
El transaction log no guarda simplemente “sentencias SQL”. No esperes encontrar un bonito UPDATE Clientes SET Saldo = Saldo – 100 WHERE Id = 7 esperando a que alguien lo vuelva a ejecutar. Sería mucho más sencillo de entender pero no, el log trabaja a un nivel mucho más físico y orientado al motor (por suerte).
Podemos encontrar registros de inicio de transacción, commit, abort, modificaciones de filas, cambios en páginas, asignaciones de extents, cambios en estructuras de índices, formateos de páginas, operaciones sobre metadatos y muchas otras entradas. Algunas son más físicas, otras más lógicas, y algunas dependen del tipo de operación. El motor registra lo que necesita para poder rehacer y deshacer de forma fiable que al final es el objetivo, no que nosotros lo entendamos de un vistazo.
Una transacción no aparece como un bloque bonito y compacto. Sus log records se enlazan mediante una cadena. Cada log record de una transacción conoce, de una forma u otra, cuál fue el log record anterior de esa misma transacción. Ese enlace permite recorrer hacia atrás la actividad de una transacción cuando toca hacer Undo.
Esto es importante porque Undo no recorre el log entero hacia atrás como quien rebobina una cinta VHS desde el final hasta el principio (si entendiste la referencia una pregunta, ¿tu también haces ruiditos al levantarte?). Undo trabaja sobre las transacciones perdedoras, las que estaban activas en el momento de la caída, y sigue sus cadenas de log records para deshacer lo que haya que deshacer.
Si alguna vez has mirado fn_dblog (esa función no documentada que todos decimos usar “solo en laboratorio”), habrás visto columnas como Current LSN, Previous LSN, Transaction ID, Operation, Context, Page ID, Begin Time, End Time o Transaction Name. No es una interfaz soportada para montar procesos, pero ayuda mucho a entender que el log es una secuencia estructurada de hechos, no una transcripción amable del SQL original.
Consultando el log
Un ejemplo típico de investigación en laboratorio sería algo así:
SELECT
[Current LSN],
[Previous LSN],
[Transaction ID],
[Operation],
[Context],
[Page ID],
[Description]
FROM sys.fn_dblog(NULL, NULL)
WHERE [Transaction ID] IS NOT NULL;
No recomiendo basar una operativa de producción en esto. Pero como herramienta para aprender cómo respira el motor, tiene mucho valor.
Checkpoint: no es un backup ni un commit
El checkpoint es otro concepto maltratado con una constancia admirable. Y vamos a dejarlo claro desde el principio. Un checkpoint no confirma transacciones. No garantiza que “todo esté guardado”. No sustituye a un backup. Un checkpoint ayuda a reducir el trabajo que tendría que hacer SQL Server durante recovery escribiendo dirty pages al disco y registrando en el log un punto útil desde el que iniciar el análisis.
Cuando se ejecuta un checkpoint, SQL Server intenta vaciar a disco páginas sucias de la memora. También escribe registros de checkpoint en el log. En un modelo tipo ARIES (Algorithms for Recovery and Isolation Exploiting Semantics), el checkpoint es “fuzzy”, porque no detiene el mundo mientras todo queda perfectamente quieto. La actividad puede continuar mientras el checkpoint se produce. Esto implica que el checkpoint no representa una fotografía congelada e impecable de la base de datos, sino un punto de referencia operativo.
Aquí conviene ser muy preciso. Decir “SQL Server empieza desde el último checkpoint” es una simplificación aceptable para una conversación de pasillo, pero incompleta. La fase de Analysis usa la información del checkpoint para reconstruir qué transacciones estaban activas y qué páginas estaban sucias. A partir de ahí, Redo puede necesitar empezar en el recLSN más antiguo de las páginas sucias, que puede ser anterior al propio final del checkpoint.
Por eso no basta con pensar “checkpoint reciente igual recovery trivial”. Ayuda, claro. Pero si hay páginas sucias antiguas, transacciones largas o mucho log activo, el recovery puede tener más trabajo del que uno esperaría mirando solo la hora del último checkpoint.
Tipos de checkpoints
También hay que distinguir entre checkpoints automáticos, manuales e indirect checkpoints. Hablamos de este tema aquí. Con indirect checkpoints, SQL Server intenta cumplir un objetivo de tiempo de recuperación por base de datos, marcado mediante TARGET_RECOVERY_TIME. Esto no significa que garantice por contrato divino que toda recuperación durará ese tiempo. Significa que el motor regula la escritura de páginas sucias para intentar limitar el volumen de Redo. Undo es otra historia, especialmente si hay transacciones largas. Sí, se puede configurar un objetivo precioso y luego estrellarse contra una transacción monstruosa. SQL Server no negocia con tu optimismo.
Arranca la instancia: ¿qué ocurre antes de que veas la base ONLINE?
Ahora si, llegamos a lo que estabas esperando, cuando el servicio de SQL Server arranca después de una caída, primero debe recuperar lo necesario para que la instancia pueda funcionar. Las bases de sistema tienen prioridad obvia. La base de datos master debe estar operativa la primera porque ahí vive la información fundamental de la instancia. Por su lado, model importa porque se usa para crear tempdb. Y tempdb, por su parte, no se recupera como una base normal, se recrea en cada arranque. Nadie debería diseñar nada persistente en tempdb, aunque hay quien lo ha intentado, estos ojos lo han visto.
Después llega la recuperación de las bases de usuario. Cada base tiene su propio transaction log, su propio estado y su propio proceso de recovery. Una base puede estar en RECOVERING, otra ya en ONLINE, otra en RECOVERY_PENDING porque no puede acceder a algún fichero, y otra en SUSPECT si la recuperación falla por problemas graves. No hay una única película para toda la instancia, hay una película por base de datos.
Podemos ver estados básicos desde master:
SELECT
name,
state_desc,
recovery_model_desc,
user_access_desc,
is_read_committed_snapshot_on,
is_accelerated_database_recovery_on
FROM sys.databases
ORDER BY name;
Durante una recuperación en curso, también puede aparecer actividad en sys.dm_exec_requests con comandos relacionados con startup o recovery. Pero ojo, no siempre veremos lo que queremos, no siempre durante mucho tiempo, y no siempre con el detalle que nos gustaría. Bienvenido al mundo real, donde los DMVs ayudan pero no hacen milagros.
SELECT
session_id,
command,
status,
database_id,
percent_complete,
wait_type,
wait_time,
last_wait_type,
blocking_session_id,
estimated_completion_time,
total_elapsed_time
FROM sys.dm_exec_requests
WHERE command LIKE '%DB%'
OR command LIKE '%RECOVERY%'
OR command = 'DB STARTUP';
El error log sigue siendo una fuente básica. SQL Server suele informar del progreso de recovery con mensajes en el error log del tipo “Recovery of database X is Y% complete”. Cuando una base tarda, el error log es el primer sitio donde mirar antes de convocar una reunión de emergencia con gente opinando fuerte.
EXEC xp_readerrorlog 0, 1, N'Recovery of database';
No es elegante, pero es útil. Y en operaciones, lo útil gana a lo elegante casi siempre.
Analysis: reconstruir el campo de batalla
Pero, volvamos un momento al principio. Ya sabemos todo lo que pasa, pero esto no sale de la nada. Hay una primera fase en la que SQL Server analiza la situación y decide que es lo que necesita para recuperarse. Esta fase se conoce como Analysis.
En la fase de Analysis el motor recorre el transaction log desde el último checkpoint relevante y reconstruye dos estructuras internas fundamentales: la Dirty Page Table y la Active Transaction Table.
La Dirty Page Table, o DPT, contiene páginas que estaban sucias en el momento del fallo. Para cada página interesa especialmente el recLSN, es decir, el LSN más antiguo que pudo haber ensuciado esa página y que todavía puede requerir Redo. Ese recLSN marca desde dónde podría ser necesario rehacer cambios para esa página.
La Active Transaction Table, o ATT, contiene las transacciones que estaban activas cuando se produjo la caída. Esas transacciones son candidatas a Undo, salvo que durante el análisis se encuentre que terminaron correctamente. En terminología clásica, las transacciones confirmadas antes del fallo son winners. Las transacciones activas en el momento del fallo son losers. El motor no se emociona con los nombres, pero la clasificación es bastante clara: unas sobreviven y otras se deshacen.
Subfases de Analysis
Durante Analysis, SQL Server procesa log records para entender qué transacciones empezaron, cuáles hicieron commit, cuáles hicieron rollback, qué páginas se modificaron y qué trabajo quedó en el aire. No está aplicando cambios a páginas todavía. Está reconstruyendo la situación exacta necesaria para decidir después.
Esta fase también identifica el punto desde el que Redo debe empezar. Y este matiz es importante, Redo no tiene por qué empezar exactamente en el último registro de checkpoint. Empieza en el menor recLSN de la DPT. Si una página lleva sucia desde antes del checkpoint y todavía no ha llegado a disco en el estado esperado, el log necesario puede arrancar antes de lo que una explicación superficial sugeriría.
Aquí se ve por qué el tamaño del log activo y la antigüedad de ciertas páginas sucias importan. No hablamos solo de “cuánto pesa el LDF”. Un LDF enorme lleno de VLFs inactivos puede ser menos problemático que una porción activa relativamente menor pero llena de historia que el motor necesita analizar. El tamaño impresiona, el LSN manda.
Para inspeccionar información útil del log moderno, tenemos sys.dm_db_log_stats, que aporta una foto bastante mejor que jugar a adivinar mirando el tamaño del fichero:
SELECT
database_id,
recovery_model,
log_min_lsn,
log_end_lsn,
current_vlf_sequence_number,
total_vlf_count,
active_vlf_count,
active_log_size_mb,
log_truncation_holdup_reason,
log_checkpoint_lsn,
log_since_last_checkpoint_mb,
log_recovery_size_mb,
recovery_vlf_count
FROM sys.dm_db_log_stats(DB_ID(N'TuBaseDeDatos'));
No hace falta obsesionarse con cada columna, pero sí entender la idea, el motor no recupera “un fichero”. Recupera una secuencia de log records relevante para llevar la base a consistencia. Los VLFs, el log activo y los LSN son parte de esa conversación.
Dirty Page Table: no todas las páginas sucias son iguales
Una dirty page es una página modificada en memoria que aún no se ha escrito al data file con esos cambios. Esa definición parece simple, pero tenerlo claor evita muchos disparates.
Una dirty page puede contener cambios de una transacción ya confirmada. Si cae el servidor antes de que esa página llegue a disco, Redo deberá rehacer esos cambios desde el log.
También una dirty page puede contener cambios de una transacción no confirmada. Si esa página llegó a disco antes del fallo, Undo tendrá que deshacer esos cambios. Si no llegó a disco, quizá Redo los repita primero para reconstruir la historia y luego Undo los quite. Esto suena raro la primera vez: ¿para qué rehacer algo que luego vas a deshacer? Porque el algoritmo de recuperación repite la historia hasta el punto del fallo y después revierte los losers. Es más robusto y permite que el proceso sea reiniciable.
Una dirty page puede contener mezcla de cambios de varias transacciones, unas confirmadas y otras no. SQL Server no puede tratar la página como una unidad transaccional simple. Tiene que mirar log records, LSNs, estado de transacciones y aplicar o deshacer lo que corresponda. Quien diga “esa página estaba sucia, luego era dato sin commit” está mezclando churras con merinas.
La DPT no existe para decorar la fase de Analysis. Existe para que Redo no tenga que revisar el universo entero. Si una página no estaba sucia en el momento crítico o su pageLSN demuestra que ya contiene un cambio, el motor puede evitar trabajo innecesario. El recovery eficiente depende mucho de no rehacer tonterías. Ya tenemos bastante con las que ejecutan algunas aplicaciones.
Active Transaction Table: el censo de los culpables
La ATT contiene transacciones activas durante la recuperación. Para cada transacción interesa saber su estado, su último LSN y la cadena de log records que permitirá deshacerla si hace falta.
Durante Analysis, una transacción puede aparecer como activa porque se ve un LOP_BEGIN_XACT. Si después aparece un commit endurecido en el log, esa transacción pasa a ser winner. Si no aparece commit ni abort completo antes del final del log disponible, será loser y entrará en Undo.
Aquí hay un detalle crítico, una transacción solo se considera confirmada de verdad si el commit está en el log persistido. Si la aplicación envió un COMMIT, pero el servidor cayó antes de endurecer el log y antes de confirmar al cliente, esa transacción no existe como confirmada a efectos de recovery. Si el cliente recibió confirmación de commit, SQL Server ya debía haber endurecido el log necesario. Esa es la promesa de durabilidad, la D de ACID.
Esto también explica algunas confusiones después de una caída. El usuario jura que “le dio a guardar”. La aplicación quizá envió algo. La red quizá hizo de las suyas. El servidor quizá no llegó a confirmar. Recovery no trabaja con sensaciones del usuario. Trabaja con log records persistidos. Bastante hace.
Redo: repetir la historia, incluso la parte que luego se va a borrar
Acabamos de explicar la fase de Redo cómo “reaplicar transacciones confirmadas”. Es una forma cómoda de contarlo, pero a bajo nivel es imprecisa. En un enfoque tipo ARIES, Redo repite la historia desde el punto necesario. Eso puede incluir cambios de transacciones que estaban sin confirmar en el momento del fallo. Luego Undo se encargará de revertirlas.
¿Por qué hacer eso? Porque así el motor reconstruye el estado de las páginas tal y como podía estar en el momento del crash, basándose en el log. Después aplica la lógica transaccional para eliminar los efectos de las transacciones perdedoras. Este enfoque es idempotente, es decir facilita que la recuperación sea correcta aunque se interrumpa y tenga que reiniciarse. Sí, recovery también puede fallar a medias si se vuelve a caer el servidor. El diseño contempla ese escenario porque, sorprendentemente, el hardware no lee nuestras ventanas de mantenimiento.
Durante Redo, SQL Server recorre log records desde el redoLSN determinado por Analysis. Para cada registro relevante sobre una página, puede comprobar si esa operación ya está reflejada en la página usando el pageLSN.
La lógica conceptual sería algo así:
Si logRecord.LSN > page.pageLSN:
- aplicar operación de redo
- actualizar page.pageLSN
Si logRecord.LSN <= page.pageLSN:
- saltar, la página ya contiene ese cambio o uno posterior
Evidentemente, el motor real es bastante más complejo, porque no todos los log records son simples modificaciones de una fila en una página de datos. Hay allocation pages, metadatos, estructuras de índices, operaciones mínimamente registradas bajo ciertas condiciones, cambios en páginas IAM, PFS, GAM, SGAM, etc. Pero la idea del pageLSN como mecanismo de idempotencia es fundamental.
Undo: caminar hacia atrás por las transacciones loser
Undo trabaja sobre las transacciones que Analysis dejó marcadas como incompletas. Para cada una, SQL Server recorre su cadena de log records hacia atrás usando los enlaces internos del log. No deshace “sentencias”, deshace efectos registrados.
Si una transacción modificó diez páginas, generó muchos log records y quedó abierta, Undo tendrá que revertir lo necesario en orden inverso. Esto puede implicar borrar filas insertadas, restaurar valores anteriores, revertir asignaciones o deshacer cambios de estructuras internas. Depende de lo que la transacción hubiera hecho.
Aquí entran los Compensation Log Records, o CLRs (si otra vez Microsoft reutilizando las mismas siglas en el mismo producto para dos cosas distintas). Cuando SQL Server deshace una operación, registra en el log que ha hecho esa compensación. Esto puede sonar extraño: “¿también loguea el rollback?”. Sí. Y menos mal.
Los CLRs hacen que Undo sea reiniciable. Si el servidor vuelve a caerse en mitad del Undo, al arrancar de nuevo SQL Server no empieza desde cero como si nada hubiera pasado. Puede ver los CLRs y saber qué partes del undo ya se realizaron. Además, los CLRs suelen incluir información como el siguiente LSN que debe deshacerse, el famoso undoNextLSN en la literatura de ARIES.
Un CLR se rehace durante Redo si hace falta, pero no se deshace otra vez durante Undo. Esto evita bucles absurdos donde el motor deshace el deshacer del deshacer hasta que alguien apaga el CPD para que deje de sufrir.
Esta parte explica por qué matar una sesión que está haciendo rollback no te salva. Si una transacción enorme empieza a revertir, SQL Server tiene que dejar la base consistente. Puedes reiniciar la instancia con la esperanza de “cancelar el rollback”, claro. Pero no servirá de nada, al volver, SQL Server retomará el trabajo necesario, porque el log conserva la obligación de terminarlo.
Ejemplos concretos
Pongamos un caso simple. Una transacción inserta una fila y hace commit. El log de la inserción y el commit están en disco. La página de datos modificada sigue en memoria. Cae el servidor. Al arrancar, Analysis detecta lo necesario, Redo encuentra el log record de la inserción, lee la página desde el data file, comprueba que su pageLSN es anterior y reaplica la inserción. La transacción era winner, así que Undo no la toca. Resultado: la fila queda.
Ahora otro caso. Una transacción inserta una fila, no hace commit, pero la página se escribe al data file antes de la caída. Al arrancar, Redo puede no necesitar aplicar esa inserción porque la página ya la contiene. Pero Analysis detecta que la transacción era loser. Undo seguirá la cadena de log records de esa transacción y eliminará la fila. Resultado: la fila desaparece.
Tercer caso. Una transacción inserta una fila, no hace commit y la página no llegó a disco. Redo puede reaplicar la inserción para repetir la historia. Luego Undo la elimina. Resultado final: la fila desaparece igualmente. Parece absurdo hasta que entiendes que el algoritmo prioriza una recuperación reiniciable y consistente sobre una intuición humana de “no hagas lo que vas a deshacer”. El motor no está intentando parecer eficiente en una pizarra, está intentando ser correcto en escenarios complejos.
Otro ejemplo práctico
Vamos a ver un ejemplo completo de manera práctica. Imaginemos una base con una tabla Ventas. Tenemos estas operaciones:
BEGIN TRAN;
UPDATE dbo.Ventas
SET Importe = Importe + 100
WHERE VentaId = 10;
INSERT dbo.Ventas(VentaId, Importe)
VALUES (99, 500);
-- No hay COMMIT todavía
Mientras esa transacción sigue abierta, SQL Server genera log records para el inicio de transacción, el update y el insert. Las páginas afectadas quedan sucias en memoria. Puede ocurrir que una de esas páginas se escriba a disco antes del commit. No pasa nada mientras el log correspondiente se haya escrito antes, cumpliendo WAL.
Ahora cae el servidor.
Durante Analysis, SQL Server ve que esa transacción empezó, ve sus operaciones y no encuentra commit. La mete en la lista de losers.
Durante Redo, SQL Server revisa los log records relevantes. Si la página del update no tiene aplicado el cambio según su pageLSN, lo rehace. Si la página del insert ya lo tenía, lo salta. Al final de Redo, la base puede parecerse al estado físico del momento de la caída, incluyendo cambios no confirmados.
Durante Undo, SQL Server recorre hacia atrás la transacción loser. Primero deshace el insert de VentaId = 99. Después revierte el update de VentaId = 10. Por cada compensación escribe CLRs. Al terminar, la transacción queda abortada de forma consistente.
Resultado: ningún cambio de esa transacción queda visible como confirmado. Pero durante el camino, el motor quizá rehizo cosas que luego deshizo. Y eso está bien. El objetivo no es que el algoritmo coincida con nuestra intuición simplificada; el objetivo es que sobreviva al desastre.
¿Por qué una base puede tardar tanto en recuperarse?
Cuando una base tarda en recovery, normalmente hay que pensar en tres grandes fases de trabajo: Analysis, Redo y Undo. Cada una puede doler por motivos distintos.
Analysis puede tardar si hay mucho log que recorrer, muchos VLFs activos, un checkpoint lejano o una estructura de log poco saludable. No es solo tamaño en GB. Un log con miles y miles de VLFs puede penalizar operaciones de gestión y recuperación. Desde hace años SQL Server ha mejorado la creación de VLFs, pero los entornos viejos, los crecimientos pequeños y frecuentes, y los logs mal dimensionados siguen existiendo. La mala configuración nunca muere, solo cambia de servidor.
Redo puede tardar si hay muchas modificaciones confirmadas pendientes de llegar a los data files, si el almacenamiento es lento, si hay mucha actividad que reconstruir o si el motor debe leer y escribir muchas páginas. Aquí el cuello puede estar en I/O, CPU, lectura de log, escritura de data files o una combinación antipática.
Undo, por su parte, puede tardar si había transacciones largas abiertas. Esta es una de las causas más desesperantes porque muchas veces el negocio solo ve “SQL Server no arranca la base de datos” cuando en realidad el motor está pagando la factura de una transacción enorme que alguien dejó a medias. Procesos batch gigantes, cargas masivas dentro de una única transacción, mantenimientos mal planteados, despliegues que mezclan DDL y DML como si no hubiera mañana… Luego recovery tarda y la culpa, por supuesto, es de la base de datos. Faltaría más.
Un punto fundamental, los checkpoints ayudan sobre todo a limitar Redo, pero no eliminan Undo. Si tienes una transacción abierta durante tres horas y cae el servidor, el checkpoint puede haber ido escribiendo páginas, pero esa transacción sigue siendo loser si no hizo commit. Y si sus cambios llegaron al data file, habrá que deshacerlos. El checkpoint no convierte una transacción abierta en confirmada. Esto parece obvio hasta que uno escucha ciertas teorías en producción.
Recovery y paralelismo: el log es secuencial, aunque SQL Server intente hacer trampas
Hay una pregunta muy natural cuando vemos una base recuperando durante minutos: “¿por qué no usa todos los cores y acaba antes?”. La respuesta corta es que no todo en recovery se puede paralelizar, porque el transaction log es una secuencia ordenada de hechos. Y cuando tu fuente de verdad es una cadena de LSNs, no puedes partirla alegremente como si fuera una tabla heap en un SELECT COUNT(*).
La fase de Analysis es, por naturaleza, muy secuencial. SQL Server tiene que recorrer el log hacia delante desde el punto de partida relevante, interpretar los log records en orden, reconstruir la Dirty Page Table y la Active Transaction Table, detectar transacciones que empiezan, transacciones que terminan, páginas que se ensucian y estados que cambian. El orden importa. Si lees primero el COMMIT y luego el BEGIN TRAN no serviría de nada..
Por eso Analysis no suele ser una fase que escale felizmente con más CPU. Puede estar limitada por lectura del log, por número de VLFs, por latencia de almacenamiento, por CPU interpretando registros o por una mezcla de todo. Pero no esperes ver dieciséis cores al cien por cien solo porque la instancia tenga muchos procesadores disponibles.
Paralelismo en Redo
En Redo la cosa es más interesante. Conceptualmente, Redo también recorre el log hacia delante desde el redoLSN, pero aplicar los cambios sobre páginas puede paralelizarse en ciertos escenarios. SQL Server puede tener un worker coordinador que lee, clasifica o distribuye trabajo, y workers de redo que aplican cambios sobre páginas diferentes. Ahora bien, esto no significa paralelismo infinito. Hay dependencias, hay latches, hay páginas calientes, hay orden lógico que respetar y hay I/O real contra data files..
En SQL Server 2016 se introdujo parallel redo para bases de datos secundarias en Availability Groups. Microsoft documenta que incluso en versiones con arquitectura de parallel recovery puede haber redo single-threaded, y que en determinados escenarios la instancia asigna threads de redo hasta ciertos límites. En SQL Server 2019 y anteriores había un límite de 100 threads disponibles para redo en ese contexto, y si se agotaban, algunas bases de datos podían quedarse con un único thread de redo.
SQL Server 2022 mejora este punto con ParallelRedoThreadPool, un pool de threads a nivel de instancia compartido por bases de datos con trabajo de redo. La documentación de Microsoft indica explícitamente que este pool permite que varios threads procesen log records de una misma base en paralelo, y que ParallelRedoBatchRedo mejora recovery, catch-up redo y crash recovery redo.
Pero cuidado, parallel redo no convierte el transaction log en una estructura desordenada. El log sigue siendo secuencial. Lo que se paraleliza es parte del trabajo de aplicación de cambios sobre páginas, no la semántica completa de la recuperación. Hay un orden que interpretar y dependencias que respetar. Por eso el escalado no es lineal. Pasar de uno a ocho workers no significa dividir el tiempo entre ocho, igual que subir MAXDOP no convierte una consulta mal escrita en un formula uno, solo hace que moleste en más schedulers.
Paralelismo en Undo
En Undo, el paralelismo también tiene límites claros. Undo no consiste en leer el log hacia atrás de forma global y repartirlo como cromos. SQL Server deshace transacciones loser siguiendo sus cadenas de log records, normalmente desde el último LSN de cada transacción hacia atrás. Esa cadena tiene orden. Para una transacción concreta, el motor no puede deshacer alegremente el paso 3 antes de entender el paso 4 si hay dependencias entre operaciones. Puede haber trabajo concurrente cuando existen varias transacciones que deshacer, pero una transacción monstruosa sigue siendo una transacción monstruosa. Y si esa transacción concentra el trabajo, el paralelismo tiene poco donde rascar.
VLFs: el log físico también importa
El transaction log está dividido internamente en Virtual Log Files, VLFs. SQL Server escribe log de forma secuencial, pero gestiona reutilización y truncado a nivel de VLFs. Un número razonable de VLFs facilita la vida. Un número disparatado puede empeorar tiempos de arranque, recovery, backups de log, restores y otras operaciones.
El problema clásico aparece cuando un log crece muchas veces en incrementos pequeños. Cada crecimiento crea VLFs nuevos. Repite eso durante años y terminas con una colección absurda de VLFs. Luego llega un failover o un reinicio y todo el mundo descubre que el LDF tenía historia clínica.
Podemos revisar los VLFs con:
SELECT
file_id,
vlf_begin_offset,
vlf_size_mb,
vlf_sequence_number,
vlf_active,
vlf_status,
vlf_parity
FROM sys.dm_db_log_info(DB_ID(N'TuBaseDeDatos'))
ORDER BY vlf_sequence_number;
No se trata de perseguir un número mágico universal. Se trata de evitar extremos ridículos. Un log de cientos de GB con pocos VLFs enormes puede no ser ideal. Un log con decenas de miles de VLFs pequeños suele ser una invitación al sufrimiento. La solución pasa por dimensionar bien el log, configurar crecimientos sensatos y, si ya está destrozado, planificar una corrección reduciendo y creciendo de forma controlada. Y no, hacer un shrink diario no cuenta como mantenimiento.
Para profundizar sobre este tema pasate por este otro artículo que escribí hace un tiempo.
El papel del pageLSN: la pieza que evita el caos
Cada página de datos de SQL Server tiene una cabecera con metadatos. Entre ellos está el LSN de la última modificación aplicada. Ese pageLSN permite al motor saber hasta qué punto del log está actualizada esa página.
Durante Redo, SQL Server no puede limitarse a aplicar cada log record ciegamente. Imagina que una página ya fue escrita al data file antes de la caída con ciertos cambios. Si Redo aplicase otra vez esos cambios sin comprobar nada, duplicaría inserciones, repetiría modificaciones o rompería estructuras internas.
La comparación entre el LSN del log record y el pageLSN permite decidir. Si la página ya refleja ese cambio o uno posterior, se salta. Si no, se aplica. Esta propiedad hace que Redo sea seguro aunque el estado físico de las páginas sea heterogéneo: unas estaban actualizadas en disco, otras no, unas contenían cambios confirmados, otras cambios sin confirmar.
Podemos inspeccionar páginas con DBCC PAGE, aunque esto es un procedimiento no documentado y entra ya en terreno de laboratorio y con cuidado. Primero habría que activar la salida al cliente:
DBCC TRACEON(3604);
GO
DBCC PAGE
(
N'TuBaseDeDatos',
1,
12345,
3
);
GO
El tercer parámetro sería el número de página. No es algo para ejecutar alegremente en producción porque “lo he visto en un blog”. Pero entender que las páginas tienen cabecera, identificadores, tipo, m_lsn y metadatos internos ayuda a dejar de ver la base como un conjunto abstracto de tablas flotando en el éter.
Allocation pages también participan en la fiesta
Cuando hablamos de recovery solemos pensar en filas de usuario, pero SQL Server también debe recuperar estructuras de asignación. Páginas PFS, GAM, SGAM, IAM y otras páginas internas registran qué extents y páginas están asignados, qué páginas tienen espacio libre, qué objetos poseen qué estructuras y demás información imprescindible. Hablamos de estas páginas de asignación en este artículo.
Si una transacción asignó páginas para insertar datos y luego cayó sin commit, Undo puede tener que revertir no solo la fila, sino también efectos de asignación si procede. Si una operación confirmó asignaciones pero las páginas de asignación no llegaron a disco, Redo tendrá que reconstruirlas. El recovery no consiste en “poner filas bien”; consiste en dejar toda la base internamente coherente.
Esto importa especialmente en operaciones intensivas como cargas masivas, creación o reconstrucción de índices, operaciones mínimamente registradas, cambios de esquema, particionado, movimientos de datos. Muchas de estas operaciones generan patrones de log distintos a un update trivial de una fila. Si una caída ocurre en mitad de una operación de mantenimiento grande, el recovery puede tener bastante trabajo aunque el usuario solo vea “estaba reconstruyendo un índice”.
Aún hay quien lanza mantenimientos masivos en una única ventana sin mirar log, VLFs, almacenamiento, HA ni plan de vuelta atrás. Luego aparece una recuperación larga y se sorprende. La sorpresa es humana, la causa, bastante menos misteriosa.
Minimally logged no significa “sin log”
Otro malentendido habitual, las operaciones mínimamente registradas. En ciertos modos de recuperación y bajo ciertas condiciones, SQL Server puede registrar menos información para operaciones como bulk load, SELECT INTO, creación de índices o algunas operaciones masivas. Pero “mínimamente registrado” no significa “no registrado”.
El motor sigue necesitando información suficiente para garantizar consistencia y recuperación. Puede no registrar cada fila del mismo modo que en una operación completamente registrada, pero registra asignaciones, cambios estructurales y lo necesario para rehacer o revertir según corresponda. Si no pudiera recuperarlo, no sería una optimización, sería una ruleta rusa.
Esto tiene impacto en backups, restores y recovery. En modo bulk-logged, por ejemplo, ciertas operaciones pueden afectar a la recuperación a un punto en el tiempo si hay operaciones mínimamente registradas implicadas. No es el foco de este artículo, pero conviene recordar que el log siempre está en la conversación..
Fast Recovery: online no siempre significa recovery terminado
En determinados escenarios y solo en SQL Server Enterprise, SQL Server puede poner una base online después de completar Redo, mientras Undo continúa en segundo plano. Esto se conoce como Fast Recovery. La idea es permitir acceso a la base antes de que termine todo el rollback de transacciones loser.
Pero cuidado con la interpretación. Que la base esté online no significa que todo sea gratis. Las transacciones que siguen en Undo pueden mantener bloqueos o generar comportamientos que hay que entender. El motor protege la consistencia, no concede un pase VIP a datos que deberían deshacerse.
Este matiz es importante porque a veces se interpreta ONLINE como “ya está todo hecho”. En recovery, ONLINE puede significar “ya puedes entrar, pero todavía estoy limpiando el desastre de alguien”. Es como abrir la tienda mientras aún están fregando el pasillo donde alguien tiró aceite. Puedes pasar, pero no conviene hacer piruetas.
ADR: cuando Undo deja de ser el monstruo de siempre
Accelerated Database Recovery cambia bastante la conversación. Introducido en SQL Server 2019, ADR rediseña la forma en la que SQL Server aborda rollback y recovery para que el tiempo de recuperación sea mucho menos dependiente del tamaño de la transacción más larga.
ADR se apoya en varios componentes, pero los dos que más nos interesan aquí son el Persistent Version Store y el logical revert. En lugar de depender exclusivamente de deshacer físicamente una cadena enorme de cambios durante Undo, ADR mantiene versiones persistentes dentro de la propia base de datos. Esto permite que el rollback lógico sea mucho más rápido desde el punto de vista de disponibilidad.
Con ADR, una transacción larga cancelada puede revertirse de forma casi instantánea para el usuario, aunque internamente luego haya limpieza asíncrona. Durante recovery, el impacto de transacciones largas se reduce de manera drástica. Esto no elimina Analysis ni Redo, pero cambia el peso relativo de Undo y evita muchas situaciones históricas donde una transacción monstruosa dejaba la base atrapada durante demasiado tiempo.
ADR también introduce el concepto de sLog, un stream secundario usado para acelerar la recuperación registrando operaciones no versionadas necesarias. Durante recovery, SQL Server puede usar ese sLog para rehacer la parte crítica con menos coste. No es simplemente “Undo más rápido”; es un cambio de arquitectura en cómo se conserva y usa la información necesaria para volver a consistencia.
Eso sí, ADR no es magia gratuita. Almacenar versiones persistentes tiene coste. Hay limpieza del PVS. Hay efectos sobre espacio, log y determinadas cargas de trabajo. Como casi todo en SQL Server, arregla problemas reales a cambio de introducir cosas que hay que monitorizar.
Activar ADR
Lo primero sería saber si tenemos ADR habilitado o no. Esto en Azure SQL es fácil, viene activado y no lo podemos quitar así que lo tendremos activado. En SQL Server On-Premise podemos ver si ADR está activado en una base con:
SELECT
name,
is_accelerated_database_recovery_on
FROM sys.databases
WHERE database_id > 4
ORDER BY name;
Podemos activarlo (cuando proceda y después de probarlo como adultos responsables) con:
ALTER DATABASE TuBaseDeDatos
SET ACCELERATED_DATABASE_RECOVERY = ON;
No lo conviertas en una receta universal sin medir. En Azure SQL Database, ADR forma parte del comportamiento moderno del servicio. En SQL Server on-premises, hay que entender versión, edición, carga y consecuencias. Nada sustituye a probar con una carga representativa. Sí, representativa de verdad, no tres inserts en una tabla de juguete.
Además, controla el crecimiento del PVS o puedes estar activando una bomba lógica y no una optimización.
Recovery tras restore no es exactamente lo mismo que crash recovery
Conviene separar conceptos. Cuando restauramos una base, también hablamos de recovery. Pero no todo recovery viene de un crash. En un restore con NORECOVERY, SQL Server deja la base preparada para recibir más backups de log o diferenciales. Con RECOVERY, ejecuta la fase final que deja la base consistente y online.
En un crash recovery, SQL Server parte de los archivos existentes de una base que sufrió una parada no limpia. Por su parte, un restore, parte de una secuencia de backups que se aplican en orden. En ambos casos el log es esencial, pero el contexto operativo cambia.
Durante restore, Redo aplica cambios de backups de log para avanzar la base hasta un punto concreto. Undo revierte transacciones incompletas en ese punto final, salvo que dejemos la base en NORECOVERY para seguir aplicando más logs. Por eso una base en NORECOVERY no está disponible: todavía no se ha ejecutado la recuperación final completa que resolvería las transacciones abiertas en el punto restaurado.
Esto explica también el modo STANDBY, donde SQL Server permite acceso de solo lectura entre restores usando un undo file para poder revertir temporalmente transacciones no confirmadas y luego rehacerlas al aplicar más logs. Es una mecánica diferente, pero refuerza la misma idea, Redo y Undo son operaciones reales sobre la historia transaccional.
Recovery Pending y Suspect: cuando ni siquiera podemos empezar bien
No todas las recuperaciones terminan bien y ahí es cuando el DBA empieza a sudar. Pero tranquilo, antes de alarmarnos vamos a entender que es lo que está pasando.
Lo primero es que no todas las recuperaciones fallidas significan corrupción lógica. A veces SQL Server no puede iniciar recovery porque falta un fichero, no tiene permisos, el disco no está disponible o el log no se puede abrir. En esos casos podemos ver RECOVERY_PENDING. La base necesita recuperación, pero el motor no puede comenzarla.
SUSPECT es peor. Indica que SQL Server intentó recuperar la base y falló. Puede deberse a corrupción, problemas de I/O, inconsistencias graves o imposibilidad de completar el proceso. Aquí hay que respirar antes de hacer barbaridades. Poner la base en emergency mode y ejecutar DBCC CHECKDB pero con cabeza. REPAIR_ALLOW_DATA_LOSS no es un plan, es una última bala cuando no hay backup válido y ya hemos aceptado pérdida de datos. El propio nombre lo dice bastante claro, aunque parece que algunos leen solo hasta REPAIR.
El camino serio ante una base suspect empieza por revisar error log, visor de eventos, almacenamiento, disponibilidad de ficheros, backups, última restauración válida y DBCC CHECKDB. Si hay backup bueno, restaurar suele ser más profesional que improvisar.
Qué mirar cuando una base está recuperando
Cuando una base tarda en volver, yo empezaría por el error log. Ahí suele aparecer el avance de recovery y mensajes específicos de cada fase. Después miraría el estado de la base, si el proceso aparece en sys.dm_exec_requests, waits relevantes y métricas del log.
Un primer bloque sencillo sería:
SELECT
name,
state_desc,
recovery_model_desc,
log_reuse_wait_desc,
is_accelerated_database_recovery_on
FROM sys.databases
ORDER BY name;
Luego intentaría ver actividad asociada:
SELECT
r.session_id,
r.command,
DB_NAME(r.database_id) AS database_name,
r.status,
r.percent_complete,
r.wait_type,
r.last_wait_type,
r.wait_time,
r.total_elapsed_time,
r.estimated_completion_time
FROM sys.dm_exec_requests AS r
WHERE r.command = N'DB STARTUP'
OR r.command LIKE N'%RECOVERY%'
OR r.database_id > 4
ORDER BY r.total_elapsed_time DESC;
Y si la base permite consultar estadísticas de log:
SELECT
DB_NAME(database_id) AS database_name,
recovery_model,
total_vlf_count,
active_vlf_count, active_log_size_mb,
log_recovery_size_mb,
recovery_vlf_count,
log_truncation_holdup_reason,
log_checkpoint_lsn,
log_end_lsn
FROM sys.dm_db_log_stats(DB_ID(N'TuBaseDeDatos'));
No siempre tendrás toda la información disponible mientras la base está recuperando. Tampoco siempre tendrás permisos. Y lo peor, no siempre la DMV te dará una respuesta con la claridad que deseas. Pero este enfoque es infinitamente mejor que reiniciar el servicio “a ver si espabila”. Reiniciar un recovery largo sin entender qué está haciendo puede devolverte al mismo punto, solo que con más presión y menos tiempo o volver a empezar, lo que es peor.
El papel de las transacciones largas: el enemigo de siempre
Las transacciones largas son veneno para muchas cosas: bloqueos, version store, crecimiento del log, truncado del log, replicación, disponibilidad y recovery. En este contexto, son especialmente desagradables porque aumentan el trabajo de Undo y pueden mantener activa una parte antigua del log.
Una transacción abierta impide truncar log más allá de su LSN de inicio o de lo que necesite el motor para mantener consistencia. Si además modifica muchas páginas, genera una cadena larga de log records que habrá que deshacer si no llega a commit. Da igual que el servidor tenga discos rápidos y mucha memoria, Undo seguirá teniendo que respetar la historia.
Podemos detectar transacciones activas en una base en funcionamiento con DMVs como:
SELECT
at.transaction_id,
at.name,
at.transaction_begin_time,
at.transaction_type,
at.transaction_state,
dt.database_id,
DB_NAME(dt.database_id) AS database_name,
dt.database_transaction_begin_lsn,
dt.database_transaction_last_lsn,
dt.database_transaction_log_record_count,
dt.database_transaction_log_bytes_used,
dt.database_transaction_log_bytes_reserved,
dt.database_transaction_next_undo_lsn
FROM sys.dm_tran_active_transactions AS at
JOIN sys.dm_tran_database_transactions AS dt
ON at.transaction_id = dt.transaction_id
WHERE dt.database_id = DB_ID(N'TuBaseDeDatos')
ORDER BY at.transaction_begin_time;
En un sistema sano, esta consulta no debería mostrar transacciones de horas salvo que haya una razón muy justificada. Y aviso: “el proceso siempre ha funcionado así” no es una razón justificada.
Recovery y alta disponibilidad: failover no significa gratis
En Always On Availability Groups, mirroring o escenarios similares, recovery también aparece en distintos momentos. Una réplica secundaria aplica log constantemente para mantenerse sincronizada. En un failover, la base debe quedar en un estado consistente para aceptar trabajo como primaria. Dependiendo del modo, del retraso, de la cola de redo y de la carga, esto puede tener impacto.
En Availability Groups hablamos mucho de send queue y redo queue. La cola de redo indica log recibido pero aún no aplicado en la réplica secundaria. Si esa cola es grande, el failover puede no ser tan inmediato como algunos prometen. Alta disponibilidad no significa que la física se haya rendido. Significa que has diseñado mecanismos para reducir indisponibilidad, siempre que el sistema pueda procesar el trabajo pendiente.
Este punto conecta directamente con lo anterior. Si generas log a una velocidad que las réplicas no pueden rehacer, acumulas deuda. Las operaciones masivas sin mirar redo queue, acumulan deuda. Si tienes almacenamiento lento en secundarias, acumulas deuda. Luego llega el failover y alguien pregunta por qué tarda. La respuesta suele estar en las métricas que nadie miró antes.
Qué prácticas reducen el dolor del recovery
La primera práctica es diseñar transacciones razonables. No metas millones de cambios en una única transacción si puedes trocear de forma segura. Tampoco dejes transacciones abiertas esperando interacción humana. Y mucho menos, envuelvas procesos batch enormes en una transacción global porque “así si falla se revierte todo”. Esa frase ha causado más logs inflamados que muchas malas configuraciones.
La segunda es dimensionar bien el transaction log. Un log debe tener tamaño suficiente para la carga normal y los picos previstos. Debe crecer en incrementos razonables, no en migajas de 1 MB para una base que mueve cientos de GB. Debe tener backups de log frecuentes si está en full recovery. Y debe vigilarse como un componente crítico, no como ese fichero incómodo que ocupa disco.
La tercera es controlar los checkpoints. En bases modernas, indirect checkpoints con TARGET_RECOVERY_TIME ayudan a regular el volumen de Redo esperado. Pero hay que entender su coste, escribir dirty pages de forma más agresiva puede incrementar I/O de fondo. No se configura mirando solo recovery, se configura mirando carga, almacenamiento y objetivos reales.
Podemos revisar el objetivo de recuperación por base con:
SELECT
name,
target_recovery_time_in_seconds
FROM sys.databases
WHERE database_id > 4
ORDER BY name;
Y cambiarlo, si procede, así:
ALTER DATABASE TuBaseDeDatos
SET TARGET_RECOVERY_TIME = 60 SECONDS;
La cuarta es evaluar ADR donde tenga sentido. En sistemas con transacciones largas, rollbacks dolorosos o requisitos estrictos de disponibilidad tras failover, ADR puede ser una mejora enorme. Pero hay que medir espacio, PVS, carga de escritura y comportamiento global. Nada de activar cosas porque suenan modernas.
La quinta es probar restores y escenarios de recuperación. No basta con tener backups. Hay que restaurarlos, medir tiempos, validar consistencia y entender cuánto tardan Redo y Undo en situaciones reales. Un backup que nunca se ha restaurado es una promesa. Y en producción, las promesas pesan poco cuando el CFO pregunta por la aplicación caída.
Conclusión
SQL Server recovery no es un proceso místico. Es una consecuencia directa de cómo el motor escribe cambios (primero log, después páginas), commit sin forzar data pages, páginas no confirmadas que pueden llegar a disco, recuperación basada en LSN, DPT, ATT, pageLSN y cadenas de log records.
Analysis reconstruye el mapa, define qué páginas podían estar sucias y qué transacciones estaban vivas. Redo repite la historia necesaria para que los data files alcancen un estado coherente con el log. Undo recorre hacia atrás las transacciones perdedoras y compensa sus efectos, registrando CLRs para que incluso el propio rollback sea recuperable.
Cuando entiendes esto, dejas de decir cosas como “Redo aplica solo commits” o “dirty page significa dato sin confirmar”. También dejas de creer que un checkpoint es una varita mágica o que reiniciar una instancia bloqueada en recovery es una estrategia. El motor sabe lo que tiene que hacer. La pregunta es si nosotros hemos diseñado el sistema para que ese trabajo sea razonable.
