Problemas de consumo de espacio en disco tras cambiar el modelo de recuperación de la base de datos MASTER

Artikel
09/16/2010

NOTA: No es nuestra intención en este artículo entrar en profundidad sobre los tres tipos de modelo de recuperación de una base de datos SQL Server o sobre el comportamiento del registro de transacciones. Para más detalles sobre estos temas recomendamos consultar los enlaces de referencia incluidos al final del texto.

Escenario inicial

Las versiones actualmente soportadas de SQL Server permiten la modificación del modelo de recuperación (Recovery Model) de todas sus bases de datos de sistema entre las tres opciones posibles: SIMPLE, BULK-LOGGED y FULL. Esto es está permitido por el sistema para todas las bases de datos de sistema, salvo la base de datos temporal tempdb la cual siempre está configurada con el modelo SIMPLE. De hecho, si intentamos cambiar tempdb de modelo usando la interfaz gráfica, obtendremos el siguiente mensaje error:

Imagen 1: Error 5058 "Option 'RECOVERY' cannot be set in database 'tempdb'"

Independientemente de si se trata de una base de datos de sistema o una base de datos de usuario, siempre que se modifique el modelo de recuperación de una base de datos cuya configuración inicial fuera SIMPLE a una configuración BULK-LOGGED o FULL, es necesario realizar una copia de seguridad completa (Full Backup) o diferencial (Differential Backup) para que esta modificación sea efectiva. En caso contrario la base de datos se seguirá comportándose como si estuviera configurada con el modelo SIMPLE. En cambio, la reconfiguración de un modelo FULL o BULK-LOGGED a SIMPLE es inmediata, sin necesidad de ninguna copia de seguridad.

Imagen 2: Mensaje de error al intentar hacer una copia de seguridad del registro de transacciones de una base de datos cuyo modelo de recuperación es FULL o BULK-LOGGED pero que sigue comportándose como SIMPLE.

Una vez efectiva la configuración del modelo de recuperación a FULL o BULK-LOGGED, es necesario realizar siempre una copia de seguridad periódica del registro de transacciones. Esta operación, aparte de permitir una restauración en un punto en el tiempo concreto, es necesaria para indicar al sistema que puede reutilizar espacio de los ficheros de la base de datos. Esa copia de seguridad, como se ha dicho, es necesaria aunque no suficiente, ya que pueden existir varios motivos que impidan la reutilización del espacio en los ficheros del registro de transacciones como transacciones abiertas durante mucho tiempo o datos pendientes de replicar a otros servidores.

Independientemente del detalle sobre el comportamiento del registro de transacciones, es muy importante recordar tres cosas:

Sin una copia de seguridad del registro de transacciones, el espacio de los ficheros de registro de transacciones de la base de datos nunca es reutilizado. En el caso en el que la base de datos tenga permitido solicitar más espacio al sistema operativo para sus ficheros de registro de transacciones lo hará hasta el límite configurado o hasta consumir todo el espacio en el volumen donde se encuentra. En cualquiera de los casos, en el momento en el que no pueda obtener más espacio, la base de datos dejará de aceptar transacciones y por lo tanto dejará de funcionar.
Ningún otro tipo de copia de seguridad permiten la reutilización del espacio en los ficheros de registro de transacciones. Sólo la copia de seguridad del registro de transacciones o el cambio de modelo de recuperación a SIMPLE, con las implicaciones que esto último supone, pueden permitir la reutilización.
Este comportamiento es igual para todas las bases de datos que permiten cambio a modelo FULL o BULK-LOGGED, sean estas de sistema o de usuario.

NOTA: En el apartado de referencias, al final del artículo, incluimos un enlace a nuestros libros en línea de ayuda para identificar los motivos por los que no se reutiliza el espacio en los ficheros del registro de transacciones.

Por otro lado, otro aspecto importante es que:

Las versiones actualmente soportadas de SQL Server no permiten la copia de seguridad del registro de transacciones para la base de datos de sistema MASTER.

Entonces ¿cuál es la consecuencia de la combinación de estos factores? Para conocerla, ejecutemos en una instancia de SQL Server de prueba (para este caso he utilizado SQL Server 2008 R2 y SQL Server 2005 Developer Edition) esta serie de comandos:

USE [master]

ALTER DATABASE [master] SET RECOVERY FULL

BACKUP DATABASE [master] TO DISK = N'C:\Temp\master.bak'

El resultado será aproximadamente el siguiente:

Processed 384 pages for database 'master', file 'master' on file 1.

Processed 3 pages for database 'master', file 'mastlog' on file 1.

BACKUP DATABASE successfully processed 387 pages in 0.584 seconds (5.177 MB/sec).

Después, para asegurarnos que el registro de transacciones contiene información nueva, ejecutamos otra copia completa de la base de datos:

BACKUP DATABASE [master] TO DISK = N'C:\Temp\master.bak' WITH FORMAT

El resultado será aproximadamente el siguiente:

Processed 368 pages for database 'master', file 'master' on file 1.

Processed 2 pages for database 'master', file 'mastlog' on file 1.

BACKUP DATABASE successfully processed 370 pages in 0.650 seconds (4.663 MB/sec).

Si ejecutamos la siguiente consulta y comprobamos que el modelo de recuperación de la base de datos MASTER (recovery_model_desc) es FULL y que el registro de transacciones (log_reuse_wait_desc) indica que la base de datos está esperando a una copia de seguridad del registro de transacciones: LOG_BACKUP.

USE [master]

SELECT [name], recovery_model, recovery_model_desc, log_reuse_wait, log_reuse_wait_desc

FROM sys.databases

WHERE name = N'master'

Resultados:

Imagen 3: Vista del estado de la base de datos del sistema MASTER en modelo de recuperación FULL

De hecho, si, por ejemplo, ejecutamos repetidas veces la copia completa de la base de datos MASTER o realizamos alguna transacción en las tablas del sistema, se puede comprobar a través del explorador del sistema operativo como el fichero de registro de transacciones crece continuamente.

Ahora si intentamos realizar una copia de seguridad del registro de transacciones de la base de datos MASTER a través de la consola de administración comprobamos que la opción está deshabilitada en la interfaz. De hecho, se puede comprobar que ni siquiera muestra la opción de una copia diferencial:

Imagen 4: Ventana de copia de seguridad de la base de datos MASTER en la consola de administración.

Si de todas formas intentamos hacer la misma operación utilizando Transact-SQL, recibiremos un mensaje de error indicando que esta acción no está permitida:

USE [master]

BACKUP LOG [master] TO DISK = N'C:\Temp\masterlog.trn'

Resultado

Imagen 5: Mensaje de error 4212 "Cannot back up the log of the master database".

Como se ha comentado al principio del artículo, pese a que el mensaje anterior recomienda realizar una copia completa de la base de datos,esta operación no va conseguir que el sistema reutilice el espacio para el registro de transacciones ya que es absolutamente necesaria una copia de seguridad del registro. De hecho, cuantas más copias de seguridad completas se realicen, más espacio se consumirá en el registro de transacciones.

El resultado es una base de datos MASTER cuyo registro de transacciones no para de crecer y a la que no se puede realizar una copia de seguridad del mismo.

Resolución

El motivo por el que no está permitida la copia de seguridad del registro de transacciones de la base de datos MASTER se debe al propio diseño de SQL Server: en un escenario de recuperación tras un desastre, a fin de mantener la estabilidad del servicio y la integridad de su información de sistema, no es posible que la base de datos MASTER se quede en ningún momento fuera de línea o no operativo.

Si, por ejemplo, se intenta restaurar la base de datos MASTER y mantenerla fuera de línea o de sólo lectura recibiremos el siguiente error:

USE [master]

RESTORE DATABASE [master] FROM DISK=N'C:\temp\master.bak'

WITH NORECOVERY

Resultado:

Imagen 6: Mensaje de error 3109 "Master can only be restored and fully recovered in a single step using a full database backup".

De hecho si intentamos hacer una copia de seguridad diferencial de MASTER también recibiríamos un mensaje de error, en este caso más explícito que el de la copia de seguridad del registro de transacciones:

USE [master]

BACKUP DATABASE [master] TO DISK = N'C:\Temp\master.bak'

WITH FORMAT, DIFFERENTIAL

Resultado

Imagen 7: Mensaje de error 3024 "You can only perform a full backup of the master database".

Dado que el modelo de recuperación FULL es "de facto" incompatible con el funcionamiento de la base de datos MASTER, la única opción posible es cambiar y mantener el modelo de recuperación a SIMPLE, bien modificándola a través de la consola de administración o bien a través del siguiente comando:

USE [master]

ALTER DATABASE [master] SET RECOVERY SIMPLE

Posteriormente podremos liberar espacio compactando el registro de transacciones bien a través de las tareas de la consola de administración o bien a través del siguiente comando de ejemplo:

USE [master]

DBCC SHRINKFILE (N'mastlog' , 0, TRUNCATEONLY)

Para prevenir que en SQL Server 2000 se modifique el modelo recuperación de MASTER se puede desarrollar una tarea programada que evalúe y corrija la configuración. En SQL Server 2005 se puede además desarrollar un desencadenador a nivel de instancia (DDL Trigger) que evite el cambio de modelo. Ya en SQL Server 2008 y SQL Server 2008R2 es posible definir y exportar una política para que se evite esta modificación. La siguiente sección contiene información adicional respecto a estas opciones.