SQL Server FAQ: ¿Qué es el Modo de Recuperación o Modo de Registro? Modos de Recuperación Simple, de Registro Masivo (BulkLogged), y Completo (Full). Operaciones de Registro Mínimo

El Modo de Recuperación o Modo de Registro de SQL Server (Recovery Model) es quizás una de las configuraciones de base de datos más sensibles, y quizás una configuración relativamente desconocida: muchos conocen que existe la configuración de Modo de Recuperación en SQL Server, pero no se tiene realmente claro que implica dicha configuración de Modo de Recuperación, su impacto en el uso del LOG, copias de seguridad del LOG (Backup LOG), rendimiento de Operaciones de Registro Mínimo, etc. De hecho, quiero incluirme en este grupo de administradores (DBAs), pues admito que la principal razón por la que he decidido preparar este artículo, es dedicar el tiempo necesario para aclararme algunos conceptos que aún tenía pendientes, y realizar las pruebas necesarias en el Laboratorio de GuilleSQL (siempre he dicho, que el principal usuario de mi Web, soy yo mismo ;-). Y por supuesto, quiero aprovechar para compartirlo.

Operaciones de Registro Mínimo en SQL Server

Antes de continuar es importante introducir el concepto de Operaciones de Registro Mínimo. En SQL Server existen algunas operaciones denominadas de Registro Mínimo, debido a que realizan un registro mínimo de información en el LOG de SQL Server (minimizando las escrituras en el LOG de SQL Server, así como el tamaño y crecimiento del LOG de SQL Server), bajo ciertas condiciones que ahora veremos. Este tipo de operaciones, minimizan sensiblemente la cantidad de escrituras realizadas en el LOG de SQL Server, lo cual, puede resultar beneficioso desde el punto de vista del Rendimiento de SQL Server.

Un ejemplo típico son las sentencias DELETE y TRUNCATE. La sentencia DELETE registrará en el LOG la información correspondiente al borrado de cada fila afectada (realizará un borrado fila a fila). Por el contrario, la sentencia TRUNCATE tan sólo registrará las páginas de datos afectadas por su ejecución (es decir, registrará sólo qué páginas de datos han dejado de estar asignadas a qué tabla, sin registrar el contenido de dichas páginas). También es cierto que en una sentencia DELETE es posible utilizar una cláusula WHERE, mientras que en una sentencia TRUNCATE no es posible utilizar una cláusula WHERE. La conclusión es clara: la sentencia TRUNCATE es inmediata incluso con tablas con cientos de millones de filas, mientras que la operación DELETE es muy costosa y requerirá escribir en el LOG de SQL Server una gran cantidad de información (elevado tiempo de ejecución y riesgo de llenar el fichero de LOG e incluso el disco duro). Existen más detalles que diferencian a DELETE y TRUNCATE, cómo es la deasignación de páginas, etc., que quedan fuera del alcance de este artículo.

¿A que ahora queda más claro la diferencia de las Operaciones de Registro Mínimo con las operaciones que no lo son? Bien, pues debo admitir que aunque el ejemplo de las sentencias DELETE y TRUNCATE es muy didáctico, la realidad es que TRUNCATE no es una Operación de Registro Mínimo. De hecho, TRUNCATE se comporta con la misma eficiencia independientemente del Modo de Recuperación de la base de datos. Sin embargo, las Operaciones de Registro Mínimo tiene la peculiaridad de que en el Modo de Recuperación Completo se registran de forma completa en el LOG, mientras que en los Modos de Recuperación Simple y de Registro Masivo (Bulk-Logged) realizan un registro mínimo en el LOG de SQL Server (salvo en algunos casos muy específicas que ahora veremos). Evidentemente, la mejora de rendimiento y minimización de uso de LOG, sólo se producirá al ejecutar dichas Operaciones de Registro Mínimo en el Modo de Recuperación Simple o de Registro Masivo (en el Modo de Recuperación Completo, nos "comeremos un colín" ;-)

El objetivo de las Operaciones de Registro Mínimo en SQL Server es muy claro: permitir recuperar la transacción, sin permitir la recuperación a un momento dado, pero con grandes ventajas de rendimiento y de necesidades de almacenamiento del LOG de SQL Server. Es decir, si a la mitad de la ejecución de una Operación de Registro Mínimo se produce una caída de la instancia de SQL Server, en el siguiente arranque de SQL Server, se podrá volver a un estado consistente de la base de datos, ya que SQL Server será capaz de deshacer (RollBack) o rehacer (ReDo) las transacciones necesarias (gracias a la información almacenada en el LOG de SQL Server) para garantizar la consistencia de la base de datos. Sin embargo, al realizar un Registro Mínimo no se podrá recuperar una base de datos a un momento del tiempo, es decir, una sentencia RESTORE DATABASE STOPAT ó RESTORE LOG STOPAT.

Claro está que ahora surge la siguiente duda: Entonces ¿Qué operaciones son designadas como Operaciones de Registro Mínimo? Pues las siguientes (tomado de los Libros en Pantalla - Books On Line ó BOL):

• Operaciones de Importación Masiva (BCP.EXE, OPENROWSET BULK, y BULK INSERT), siempre y cuando se especifique la opción TABLOCK, la tabla no se esté replicando, y dependiendo también de la definición de índices de la tabla, y de la existencia o no de datos en la tabla, en particular:
  • Si la tabla no tiene índices, el registro de las páginas de datos será mínimo.
  • Si la tabla no tiene índices agrupados sino uno o más índices no agrupados, el registro de las páginas de datos siempre será mínimo. Sin embargo, el modo en que se registran las páginas de índice será mínimo sólo si la tabla está vacía (si la tabla no está vacía, el registro de las páginas de índice será completo).
  • Si la tabla tiene un índice agrupado y está vacía, tanto los datos como las páginas de índice se registrarán de forma mínima. En cambio, si la tabla tiene un índice agrupado pero no está vacía, tanto las páginas de datos como las de índice se registrarán de forma completa, independientemente del modelo de recuperación utilizado.
  Dicho de otro modo, y por poner un ejemplo, una carga con BCP.EXE en una base de datos en Modo de Recuperación Simple sin utilizar la opción TABLOCK, no se ejecutará como una Operación de Registro Mínimo, con el consecuente consumo de recursos y espacio en disco.
• Operaciones text, ntext e image con las instrucciones WRITETEXT y UPDATETEXT al insertar o anexar datos nuevos (al actualizar datos existentes, no se realiza un Registro Mínimo). Estas instrucciones desaparecerán en próximas versiones, por lo que es muy conveniente evitar su utilización.
• Actualizaciones parciales a tipos de datos de valores grandes mediante cláusulas .WRITE de la instrucción UPDATE.
• Operaciones SELECT INTO. Este tipo de operaciones (SELECT INTO) es vital al trabajar con grandes cantidades de datos, como por ejemplo, ocurre en entornos de Data Warehouse y Business Intelligence, donde SELECT INTO resulta ser muy querido: la manera más rápida de cargar una tabla, es a través de SELECT INTO en un Modo de Recuperación Simple o de Registro Masivo, especialmente, si la base de datos está correctamente dimensionada (tanto ficheros de datos como ficheros de LOG).
• Operaciones INDEX DDL como CREATE INDEX, ALTER INDEX REBUILD, DBCC REINDEX, DROP INDEX. El hecho de que estas operaciones puedan ser tratadas como Operaciones de Registro Mínimo, implica una sensible mejora de rendimiento en la ejecución de Planes de Mantenimiento y otras tareas de administración de base de datos que impliquen operaciones de INDEX DDL, además de minimizar el espacio ocupado en los ficheros de LOG de SQL Server.

Modos de Recuperación de SQL Server (Modos de Registro o Recovery Model)

Ahora que ya entendemos qué es una Operación de Registro Mínimo en SQL Server y sabemos qué operaciones y en que escenarios son consideradas Operaciones de Registro Mínimo, estamos en situación de explicar las diferentes alternativas del Modo de Recuperación de SQL Server (también conocido como Modelo de Recuperación o Modo de Registro), y su impacto en las estrategias de Copia de Seguridad y Restauración (Plan de Contingencias - Backup y Restore), así como de crecimiento de LOG.

• Modo de Recuperación Simple o Sencillo (Simple Recovery Model).
  • Las Operaciones de Registro Mínimo realizarán un registro mínimo en el LOG de SQL Server, minimizando las escrituras en LOG y maximizando el Rendimiento de SQL Server.
  • Todas las transacciones serán registradas en LOG, sin embargo, una vez que la transacción finaliza, el espacio de LOG utilizado por dicha transacción podrá ser reutilizado para el registro de otras transacciones. No es necesario realizar copias de seguridad del LOG para poder reutilizar su espacio.
  • Sólo se permiten Copias de Seguridad Completas o Diferenciales. No se puede recuperar (RESTORE) a un momento en el tiempo (STOPAT).



• Modo de Recuperación Completo (Full Recovery Model).
  • Las Operaciones de Registro Mínimo no se comportarán como tal, realizándose siempre un registro completo en el LOG de SQL Server.
  • Todas las transacciones serán registradas en LOG. Sólo la ejecución de una sentencia BACKUP LOG permitirá reutilizar el espacio de LOG ocupado por transacciones antiguas almacenadas en el Backup del LOG. Es decir, hasta que una transacción del LOG de SQL Server no se guarda en un BACKUP, no se permite su eliminación del LOG, con la excepción de la sentencia BACKUP LOG WITH TRUNCATE_ONLY (o BACKUP LOG WITH NO_LOG), la cual permite vaciar el LOG sin realizar físicamente una copia de seguridad (ojo: sólo se debe usar en caso de emergencia). Si no se realizan copias de seguridad de LOG periódicamente, el LOG de SQL Server crecerá indefinidamente.
  • Se permite cualquier tipo de Copia de Seguridad (BACKUP): Completas, Diferenciales, de Fichero, de Grupo de Ficheros (FILEGROUP), y de LOG. Es posible recuperar (RESTORE) a un momento del tiempo (STOPAT).



• Modo de Recuperación de Registro Masivo (Bulk-Logged Recovery Model). Este Modo de Recuperación sólo debe ser utilizado de forma intermitente o eventual para mejorar el rendimiento de las Operaciones de Registro Mínimo.
  • Las Operaciones de Registro Mínimo realizarán un registro mínimo en el LOG de SQL Server, minimizando las escrituras en LOG y maximizando el Rendimiento de SQL Server.
  • Todas las transacciones serán registradas en LOG. Sólo la ejecución de una sentencia BACKUP LOG permitirá reutilizar el espacio de LOG ocupado por transacciones antiguas almacenadas en el Backup del LOG. Es decir, hasta que una transacción del LOG de SQL Server no se guarda en un BACKUP, no se permite su eliminación del LOG, con la excepción de la sentencia BACKUP LOG WITH TRUNCATE_ONLY (o BACKUP LOG WITH NO_LOG), la cual permite vaciar el LOG sin realizar físicamente una copia de seguridad (sólo usar en caso de emergencia). Si no se realizan copias de seguridad de LOG periódicamente, el LOG de SQL Server crecerá indefinidamente.
    Debe tenerse en cuenta, que al realizar una copia del LOG (Backup LOG) durante un periodo de tiempo en el que han ocurrido Operaciones de Registro Mínimo, la copia del LOG almacenará tanto el contenido del LOG (o Transaction LOG), como el contenido de las páginas de datos afectadas (que se leerá de los correspondientes ficheros de datos). Es decir, tendremos un tamaño de LOG relativamente pequeño, pero sin embargo, la copia del LOG (Backup LOG) podría tener un tamaño considerablemente grande.
  • Se permite cualquier tipo de Copia de Seguridad (BACKUP): Completas, Diferenciales, de Fichero, de Grupo de Ficheros (FILEGROUP), y de LOG, pero no se puede recuperar (RESTORE) a un momento en el tiempo (STOPAT).

Una conocida estrategia de configuración de bases de datos SQL Server es mantener un Modo de Recuperación Completo (FULL) durante toda la jornada, y en periodos de carga masiva o de operaciones de mantenimiento, utilizar un Modo de Recuperación de Registro Masivo (Bulk Logged), con el objetivo de mejorar el rendimiento de Operaciones de Registro Mínimo (SELECT INTO, BULK INSERT, BCP.EXE, operaciones INDEX DDL como CREATE INDEX o DROP INDEX, etc.). La utilización de una estrategia mixta de Modo de Registro Completo y Registro Masivo (FULL y Bulk-Logged) probablemente requiera una actualización del Plan de Contingencias o Políticas de Backups y Restores de SQL Server.

También debe tenerse en cuenta, que determinadas configuraciones requieren del Modo de Recuperación Completo para su funcionamiento. Por ejemplo, si deseamos implementar Database Mirroring (disponible de SQL Server 2005), será necesario utilizar el Modo de Recuperación Completo.

El Modo de Recuperación y el crecimiento del LOG de SQL Server

Una incidencia típica en entornos de base de datos SQL Server, es que el LOG de alguna base de datos crezca indefinidamente, llegando incluso a llenar completamente el disco sobre el cual existe dicho fichero. Este problema de crecimiento del LOG de SQL Server ya lo hemos tratado anteriormente, pero quería aprovechar de nuevo para recalcarlo, tanto por su importancia como por su relación con el tema aquí tratado.

El motivo de esta incidencia lo comentamos un poco más arriba en este mismo artículo:

• En el Modo de Recuperación Simple, sólo se almacenan en el LOG las transacciones en curso (al finalizar las mismas y escribirse en disco - Checkpoint - queda disponible para reutilizar el espacio que ocuparon dichas transacciones en el LOG).
• Sin embargo, en los Modos de Recuperación Completo y de Registro Masivo, todas las transacciones de almacenan en el LOG. En este caso, la realización de un Backup del LOG, permitirá llevarse las transacciones antiguas a un fichero de Backup y de este modo permitir reutilizar dicho espacio del LOG de Transacciones (evitando que este tenga que seguir creciendo el LOG para poder almacenar las nuevas transacciones). He aquí el problema: configurar el Modo de Recuperación como Completo o de Registro Masivo, y no realizar copias de seguridad del LOG (Backup LOG), en muchas ocasiones, por que pensamos que con hacer un backup completo todas las noches, nos es suficiente para mantener protegida nuestra base de datos (algo que está bien pensado, pero para eso, está el Modo de Recuperación Simple... aunque claro, en la mayoría de ediciones de SQL Server, se utiliza por defecto el Modo de Recuperación Completo...).

La solución temporal (Workaround o ñapa) es vaciar el LOG a las bravas, para lo cual podemos encontrar mayor detalle en el artículo ¿Cómo se puede reducir una base de datos?. Sin embargo, la solución definitiva será configurar las correspondientes copias de seguridad del LOG, o en su defecto, cambiar al Modo de Recuperación a Simple.

Cómo configurar el Modo de Recuperación de SQL Server (ó Modo de Registro)

Antes de aprender cómo configurar el Modo de Recuperación de una base de datos SQL Server, es importante conocer cómo averiguar el Modo de Recuperación utilizado por una base de datos SQL Server, lo cual, podemos realizar utilizando Transact-SQL de varias formas, como se muestra en el siguiente ejemplo:

También es posible averiguar el Modo de Recuperación de una base de datos de forma gráfica, utilizando SQL Server Management Studio (SSMS). Para ello, es suficiente con mostrar el diálogo de Propiedades de una Base de Datos desde la ventana Explorador de Objetos (Object Explorer), y comprobar el valor de la propiedad Recovery Model de la página o pestaña Options, como se muestra en la siguiente figura:

Ahora que ya sabemos cómo averiguar el Modo de Recuperación utilizado por una base de datos SQL Server, vamos a ver la forma de configurar o establecer el Modo de Recuperación de dicha base de datos, tarea que podemos realizar utilizando Transact-SQL como se muestra en el siguiente ejemplo:

Cómo en el caso anterior, también es posible cambiar el Modo de Recuperación de una base de datos de forma gráfica, utilizando SQL Server Management Studio (SSMS). Para ello, desde el diálogo de Propiedades de una Base de Datos en el Explorador de Objetos (Object Explorer), cambiar el valor de la propiedad Recovery Model de la página o pestaña Options, como se muestra en la siguiente figura:

Por supuesto, deberemos recordar que tras realizar un cambio del Modo de Recuperación de una base de datos, es probable que el siguiente paso sea realizar un cambio en la configuración de copias de seguridad. Por ejemplo, si cambiamos del Modo de Recuperación Completo al Simple, deberemos eliminar la planificación de los Backups de LOG (y viceversa, si cambiamos del Modo de Recuperación Simple al Completo deberemos incluir Backups de LOG ;-).