Preguntas y respuestas sobre SQLGrandes registros de transacciones, cuándo usar la reparación, entre otros temas

Paul S. Randal

P He observado un comportamiento extraño con las copias de seguridad que espero que me pueda explicar. Muchas veces realizamos copias de seguridad de nuestra base de datos de producción de 62 GB para actualizar los datos usados por nuestros desarrolladores de aplicaciones. Siempre eliminamos la copia antigua antes de restaurar la nueva. La copia restaurada tiene el mismo tamaño que la base de datos de producción y los datos parecen los mismos, pero el proceso de restauración tarda mucho más que el proceso de copia de seguridad. ¿A qué se debe? ¿Por qué tarda mucho más tiempo en restaurar que en realizar la copia de seguridad?

R Realmente no ocurre nada extraño. Según las circunstancias, generalmente éste es el comportamiento que se espera. La diferencia en el tiempo necesario para una copia de seguridad frente a una restauración se debe a los pasos que debe realizar cada proceso.

La realización de copias de seguridad de una base de datos consta de dos pasos. Básicamente, sólo se trata de E/S de lectura de la base de datos y E/S de escritura en el dispositivo de copia de seguridad:

Paso 1 de la copia de seguridad Lea todos los datos asignados en los archivos de datos y escríbalos en el dispositivo de copia de seguridad.

Paso 2 de la copia de seguridad Lea algo del registro de transacciones y escríbalo en los dispositivos de copia de seguridad.

La cantidad exacta necesaria del registro de transacciones puede variar de forma considerable, pero es realmente la cantidad necesaria para poder recuperar la base de datos restaurada en un punto coherente en el tiempo. (Encontrará una explicación más exhaustiva al respecto en una publicación de blog disponible en sqlskills.com/blogs/paul/2008/01/31/MoreOnHowMuchTransactionLogAFullBackupIncludes.aspx.)

Por otra parte, la restauración de una base de datos puede constar de hasta cuatro pasos, y el trabajo que conlleva es más complicado que sólo las E/S de lectura y escritura:

Paso 1 de restauración Si no existen archivos de base de datos, créelos.

Paso 2 de restauración Lea todos los datos y el registro de transacciones de la copia de seguridad y escríbalos en los archivos de base de datos pertinentes.

Paso 3 de restauración Ejecute la fase REDO de la recuperación en el registro de transacciones.

Paso 4 de restauración Ejecute la fase UNDO de la recuperación en el registro de transacciones.

El tiempo total transcurrido para ambos pasos de la copia de seguridad debe ser aproximadamente el mismo que el tiempo necesario para el Paso 2 de restauración (suponiendo que tiene hardware similar y un servidor sin ninguna actividad de usuario). El paso 1 de la restauración puede tardar mucho tiempo si los archivos de datos son grandes y tienen que inicializarse en cero (que es el comportamiento en SQL Server 2000 y el comportamiento predeterminado en SQL Server 2005).

Para evitarlo, no elimine los archivos existentes antes de iniciar la restauración. O bien, como alternativa, habilite la inicialización instantánea de manera que los archivos se creen muy rápidamente (se puede obtener más información al respecto en msdn.microsoft.com/library/ms175935.aspx).

Los pasos 3 y 4 de la restauración ejecutan la recuperación en la base de datos restaurada para que sea coherente en las transacciones; éste es el mismo proceso por el que pasaría una base de datos durante la recuperación tras bloqueo. El tiempo que tardará dicha recuperación dependerá de la cantidad del registro de transacciones que se tiene que procesar. Por ejemplo, si había una transacción de muy larga ejecución en el momento en que se realizó la copia de seguridad, todo el registro de transacciones para dicha transacción estaría en la copia de seguridad y tendría que revertirse.

P Estoy intentando elegir entre el trasvase de registros y la creación de reflejo de la base de datos para hacer una copia redundante de nuestra base de datos de producción. Me preocupa la cantidad del registro de transacciones que se tendrá que enviar entre los servidores, especialmente para las operaciones de reconstrucción de índices que realizamos todas las noches. He oído que la creación de reflejos envía los comandos de reconstrucción reales en lugar del registro de transacciones y las reconstrucciones se realizan en la creación de reflejos. ¿Es cierto? Esto haría que la creación de reflejo fuera una solución incluso mejor que el trasvase de registros con el modelo de recuperación BULK_LOGGED, ¿es así?

R Lo que ha oído no es cierto. La creación de reflejo de la base de datos funciona enviando los registros de transacciones reales de la base de datos principal al servidor reflejado, donde se "vuelven a reproducir" en la base de datos reflejada. No se está produciendo ninguna traducción ni filtrado de ningún tipo, ni ninguna clase de intercepción de comandos T-SQL para una base de datos reflejada.

La creación de reflejos de bases de datos sólo admite el modelo de recuperación FULL, lo que significa que una operación de reconstrucción de índices siempre se registrará por completo. Según el tamaño de los índices implicados, esto podría significar que se generará una cantidad importante del registro de transacciones y, a su vez, un archivo de registro grande en la base de datos principal y un ancho de banda de red sustancial para enviar los registros al reflejo.

Puede pensar en la creación de reflejo de la base de datos como un trasvase de registros en tiempo real (de hecho, éste fue un nombre que se usó para la característica al principio durante el desarrollo de SQL Server 2005). En el trasvase de registros, las copias de seguridad del registro de transacciones de la base de datos principal se trasvasan con regularidad al servidor secundario y se restauran en la base de datos secundaria.

El trasvase de registros admite los modelos de la recuperación FULL y BULK_LOGGED. Para una operación de reconstrucción de índices en una base de datos de registros trasvasados con el modelo de recuperación FULL, se generará la misma cantidad del registro de transacciones que la cantidad generada para una base de datos reflejada. Sin embargo, en el escenario de la base de datos de registros trasvasados, los datos se envían a la base de datos redundante en una copia de seguridad de registro (o una serie de copias de seguridad de registro) en lugar de como un flujo continuo.

Si se usa el modelo de recuperación BULK_LOGGED en la base de datos de registros trasvasados mientras se realiza la reconstrucción de índices, sólo se generará una cantidad mínima del registro de transacciones. Sin embargo, la siguiente copia de seguridad del registro de transacciones también contendrá todas las extensiones del archivo de datos que se han cambiado por la operación de reconstrucción de índice mínimamente registrada. Esto significa que las copias de seguridad de registro que cubren la reconstrucción de índices en el modelo de recuperación BULK_LOGGED tendrán casi el mismo tamaño que las que cubren la reconstrucción de índices en el modelo de recuperación FULL.

Por tanto, la cantidad de información que se tiene que enviar a la base de datos redundante es casi la misma para una reconstrucción de índices en una base de datos reflejada y en una base de datos de registros trasvasados. La diferencia real radica en la manera en que se envía la información: de manera continua o en lotes.

Hay otros muchos factores que se deben tener en cuenta al elegir entre estos dos métodos (demasiados factores que tratar en una sola entrega de Preguntas y respuestas sobre SQL). Debería observar cómo todos estos factores coinciden con sus requisitos (como el límite aceptable de pérdida de datos y el tiempo de inactividad permisible) antes de tomar una decisión.

P Estoy ejecutando SQL Server 2005 y una de mis bases de datos tiene un registro de transacciones que no para de crecer. La base de datos se encuentra en el modo de recuperación completa y estoy realizando copias de seguridad del registro de transacciones. Pensaba que así evitaría el crecimiento del registro de transacciones. ¿Qué es lo que está fallando?

R Tiene razón al afirmar que la creación de copias de seguridad del registro de transacciones en el modo de recuperación completa es crítico. Sin embargo, hay otros factores que pueden contribuir al crecimiento del registro de transacciones. Todo se reduce a lo que el registro de transacciones exija que sea necesario (o que esté activo). Entre otros factores comunes (aparte de la falta de copias de seguridad del registro de transacciones) que pueden ser la causa de su problema se incluyen la replicación, la creación de reflejos de la base de datos y una transacción activa.

La replicación funciona leyendo de manera asincrónica sus registros del registro de transacciones y cargando a continuación las transacciones para replicar en una base de datos de distribución independiente. No se podrá liberar ningún registro del registro de transacciones que la tarea del lector de registros de réplica no haya leído todavía. Si su carga de trabajo genera muchos registros del registro de transacciones y ha establecido un gran intervalo para la frecuencia con que se ejecutará el lector de registros de replicación, se podrán acumular muchos registros y hacer que crezca el registro de transacciones.

Si ejecuta la creación asincrónica de reflejo de la base de datos, podría haber un registro de seguridad de las entradas del registro de transacciones que no se hayan enviado desde la entidad principal al reflejo (denominado la cola SEND de la creación de reflejos de la base de datos). Los registros del registro de transacciones no se pueden liberar hasta que no se hayan enviado correctamente. Con una tasa alta de generación de registros del registro de transacciones y el ancho de banda limitado en la red (u otros problemas de hardware), el registro de seguridad puede crecer bastante y hacer que crezca el registro de transacciones.

Por último, si un usuario inicia una transacción explícita (por ejemplo, usando la instrucción BEGIN TRAN) y, a continuación, realiza una modificación de algún tipo (como una instrucción de DDL o una acción de insertar/actualiza/eliminar), los registros del registro de transacciones que se generan tienen que mantenerse hasta que el usuario confirme o revierta la transacción. Lo anterior significa que tampoco se puede liberar ningún registro del registro de transacciones posterior generado por otras transacciones, ya que el registro de transacciones no se puede liberar de manera selectiva. Si dicho usuario, por ejemplo, regresa a casa sin completar la transacción, el registro de transacciones continuará creciendo conforme se generan cada vez más registros de transacciones, pero no se puede liberar.

Puede averiguar por qué el registro de transacciones no se puede liberar consultando la vista de catálogo del sistema sys.databases y examinando la columna log_reuse_wait_desc, de la siguiente manera:

SELECT name AS [Database], 
  log_reuse_wait_desc AS [Reason]
FROM master.sys.databases;

Si la causa resulta ser una transacción activa, puede usar la instrucción DBCC OPENTRAN para obtener más información acerca de la transacción:

DBCC OPENTRAN ('dbname')

P He oído que REPAIR_ALLOW_DATA_LOSS sólo debería usarse como último recurso para recuperar datos dañados; en su lugar, se debería usar la restauración de copias de seguridad. ¿Puede explicar por qué no se debería usar la "reparación para SQL Server 2005" y por qué está incluida en el producto teniendo en cuenta lo "peligroso" que es?

R En primer lugar, ciertamente fui yo quien escribió la reparación para SQL Server 2005. El problema de REPAIR_ALLOW_DATA_LOSS (al que de ahora en adelante me referiré sencillamente como reparación) es que su funcionamiento no está claro. El nombre de la opción de reparación se eligió para destacar que ejecutarla podría provocar la pérdida de datos de la base de datos. La manera en que esta característica suele reparar una estructura dañada de la base de datos es eliminando la estructura dañada y, a continuación, corrigiendo el resto de la base de datos que hacía referencia a la estructura eliminada o al que hacía referencia dicha estructura. Reparar debería ser el último recurso para conseguir que la base de datos sea coherente estructuralmente; no se trata de guardar datos de usuario. Reparar no se toma la molestia de eliminar datos de usuario ni tampoco de guardarlos.

Esto puede parecer una manera irresponsable de realizar reparaciones pero, cuando se tiene que usar la opción de reparación, ofrece el método más rápido y confiable de corregir el daño. La velocidad es de suma importancia en una situación de recuperación ante desastres y la exactitud es absolutamente necesaria. Es casi imposible desarrollar algoritmos de reparación más complejos cuyo funcionamiento se pueda probar de manera rápida y correcta en todos los casos. Hay algunos algoritmos complejos en el código de reparación diseñados para resolver el problema que se da cuando dos índices tienen, por ejemplo, la misma página o extensión asignadas, pero básicamente el algoritmo implica reparar y corregir.

Aún así, la reparación plantea algunos problemas que debería tener en mente:

• La reparación no considera las restricciones de clave externa al eliminar las estructuras dañadas, por lo que puede eliminar los registros de una tabla que tenga una relación de clave externa con otra tabla. No hay manera de determinar si esto ha sucedido sin ejecutar DBCC CHECKCONSTRAINTS después de ejecutar la reparación.
• La reparación no considera (ni puede considerar) relaciones de datos o de lógica empresarial inherente definidas en el nivel de aplicación que podrían interrumpirse por la eliminación de algún dato. De nuevo, no hay manera de determinar si se ha interrumpido algo sin ejecutar, independientemente de la comprobación de coherencia personalizada que esté integrada en la aplicación.
• No es posible replicar algunas operaciones de reparación. La ejecución de la reparación en un publicador o un nodo de una topología de punto a punto puede introducir incoherencias dentro de la topología, las cuales deberán corregirse manualmente.

Por estas razones, siempre resulta buena idea recuperar datos dañados usando una copia de seguridad en lugar de ejecutar la opción de reparación. Sin embargo, se ofrece la opción de reparación con el producto porque siempre es posible que se dé una situación en la que la base de datos está dañada, no haya copia de seguridad y necesite una solución para hacer que la base de datos vuelva a estar conectada rápidamente.

P Acabo de empezar a trabajar en una compañía nueva como administrador de bases de datos y soy responsable de varias aplicaciones y de sus bases de datos back-end. Al realizar actualizaciones, el rendimiento de una de las aplicaciones es muy bajo. Investigué y descubrí que las tablas usadas por la aplicación tienen un número enorme de índices en cada una de ellas. Después de preguntar por ahí, parece que el anterior administrador de bases de datos tenía la costumbre de agregar un índice de cada columna de tabla más algunas combinaciones. No creo que todos estos índices sean necesarios, ¿cómo puedo saber cuáles son los que puedo eliminar con seguridad? Trabajamos en SQL Server 2005.

R Como se imaginará, es muy probable que el número elevado de índices contribuya en gran medida al bajo rendimiento. Cada vez que se inserta, actualiza o elimina una fila en la tabla, se tienen que realizar las operaciones correspondientes en todos los índices no agrupados. Esto agrega una gran sobrecarga a la E/S, la utilización de la unidad de procesamiento central y la generación del registro de transacciones.

En SQL Server 2000, la única manera de determinar qué índices se usaban era empleando la creación de perfiles y examinando los planes de consulta. Con SQL Server 2005 hay una nueva vista de administración dinámica (DMV) que realiza un seguimiento del uso del índice: sys.dm_db_index_usage_stats.

Esta DMV realiza un seguimiento cada vez que se ha usado un índice (y de cómo se ha usado) desde que se inició la base de datos de la que forma parte. Cuando se apaga SQL Server, se pierde la estadística para todas las bases de datos, y la estadística para una base de datos única se pierde cuando se cierra o se separa. La idea es que si un índice no aparece en la salida significa que no se ha usado desde que se inició la base de datos.

Un método sencillo para hacer un seguimiento del uso del índice a lo largo del tiempo es crear instantáneas periódicas de los resultados de DMV y después compararlas. Un asunto en el que no piensan muchas personas es que se debe realizar un seguimiento del uso del índice en un ciclo de negocio completo. Si acaba de crear una instantánea para un solo día, puede encontrar varios índices que no se usan. Sin embargo, si se usan dichos índices, por ejemplo, como ayuda para que los informes ejecuten magnitudes con mayor rapidez, probablemente los índices no se deberían eliminar. Si realmente no se ha estado usando un índice durante un ciclo de negocio completo, probablemente podrá eliminarlo, y así recuperar espacio y mejorar el rendimiento.

Para ver código sencillo que puede usar para tomar instantáneas periódicas de la DMV, consulte mi publicación de blog en sqlskills.com/blogs/paul/2007/10/05/IndexesFromEveryAngleHowCanYouTellIfAnIndexIsBeingUsed.aspx.

Paul S. Randal es el director general de SQLskills.com y uno de los profesionales más valorados (MVP) de SQL Server. Paul escribió DBCC CHECKDB/repair para SQL Server 2005 y fue responsable del motor de almacenamiento del núcleo durante el desarrollo de SQL Server 2008. Es experto en recuperación de desastres, en alta disponibilidad y en el mantenimiento de bases de datos, además de moderador habitual en congresos. Además, es autor de un blog: SQLskills.com/blogs/paul.