KB2455009: CORRECCIÓN: Rendimiento lento al recuperar una base de datos si hay muchas VLFs en el registro de transacciones en SQL Server 2005, en SQL Server 2008 o en SQL Server 2008 R2

Síntomas

Imagine la siguiente situación:

Puede instalar Microsoft SQL Server 2005, Microsoft SQL Server 2008 o Microsoft SQL Server 2008 R2 en un equipo.
Una o varias de las bases de datos se encuentran en el modelo de recuperación completa o con registro masivo.
El tamaño inicial de un archivo de registro de transacciones para la base de datos es pequeño. Sin embargo, el archivo de transacciones aumenta de tamaño.
El incremento de crecimiento automático del archivo de registro de transacciones está configurado en un tamaño pequeño. Por ejemplo, el incremento de crecimiento de crecimiento automático está configurado en un megabyte o en un porcentaje. O bien, aumente el archivo de registro de transacciones con un pequeño crecimiento manual.
El archivo de registro de transacciones aumenta en incrementos reducidos. Después de un tiempo, el archivo de registro de transacciones es grande.

Por ejemplo, el archivo de registro de transacciones crece de cien megabytes a diez gigabytes. El archivo de registro de transacciones aumenta en incrementos de un megabyte en cada crecimiento.

En este escenario, puede experimentar los siguientes problemas:

La recuperación de base de datos es lenta cuando se inicia la base de datos si hay un gran volumen de trabajo para recuperar.

Por ejemplo, una transacción está pendiente de apertura durante mucho tiempo. O bien, muchas transacciones de tamaño reducido no se confirman cuando se cierra o reinicia el SQL Server. En este caso, es posible que reciba un error durante un largo tiempo estimado de recuperación en la fase de análisis, en la fase de deshacer o en la fase de rehacer. El error es similar al siguiente y se registra en el archivo de registro de errores de SQL Server 2005:

El análisis de la base de datos "mibase de datos" (7) es del 0% completado (quedan aproximadamente 1234 segundos). Solo se trata de un mensaje informativo. No es necesaria ninguna acción del usuario.
La recuperación de base de datos es lenta cuando se restaura un archivo de copia de seguridad de base de datos completo y una secuencia de registro de transacciones adicional si la base de datos se vuelve a conectar.

Nota Para poner la base de datos en línea durante la operación de restauración, use la sintaxis WITH RECOVERY o las opciones WITH STANDBY = <> sintaxis.
La recuperación de creación de reflejo de base de datos es lenta en un servidor asociado en el par de creación de reflejos.
El progreso del agente del lector de registro para la replicación transactional es lento en las siguientes operaciones:
- Se analiza el registro de transacciones de una base de datos de editor.
- Se agrega un comando a una base de datos de distribución.
- Las transacciones se replican.
El rendimiento lento se produce cuando se crea una instantánea de la base de datos si se registran muchas transacciones o si los registros de transacciones permanecen activos durante mucho tiempo. Este problema se produce porque la instantánea tiene que ejecutar la recuperación y los registros de transacciones tienen que revertirse.
El rendimiento lento se produce cuando se usa la sintaxis DBCC CHECKDB para crear una instantánea de base de datos oculta que tiene que recuperar completamente la instantánea de la base de datos para ejecutar las comprobaciones de coherencia.

Causa

Estos problemas se producen porque los archivos de registro virtual (VLF) en un archivo de registro de transacciones pueden provocar un rendimiento lento al SQL Server examina estos VLFs durante las siguientes operaciones:

Recuperación de base de datos
Creación de reflejo de la base de datos
Instantánea de la base de datos
Actividades del lector de registro de replicación de transacciones de base de datos

Cuando un archivo de registro de transacciones para una base de datos aumenta en pequeños incrementos durante condiciones de crecimiento extremas, hay de miles a cientos de miles de VLFs dentro de un único conjunto de archivos de registro de transacciones (.ldf). Sin embargo, el crecimiento de registro grande en incrementos grandes crea solo varias VLFs en comparación. Por ejemplo, varias pueden ser inferiores a 100 VLF.

Nota Los VLF son las divisiones o segmentos que se utilizan internamente por SQL Server en el archivo de registro de transacciones.

Solución

Información sobre la actualización acumulativa

SQL Server 2008 R2

La corrección para este problema se publicó por primera vez en la actualización acumulativa 6. Para obtener más información sobre cómo obtener este paquete de actualización acumulativa para SQL Server 2008 R2, haga clic en el número de artículo siguiente para verlo en Microsoft Knowledge Base:

2489376 Paquete de actualización acumulativa 6 para SQL Server 2008 R2 Nota Como las compilaciones son acumulativas, cada nueva versión de corrección contiene todas las revisiones y todas las correcciones de seguridad que se incluyeron con la versión de corrección de SQL Server 2008 R2 anterior. Le recomendamos que considere la posibilidad de aplicar la corrección más reciente que contiene esta revisión. Si desea obtener más información, haga clic en el número de artículo siguiente para verlo en Microsoft Knowledge Base:

981356 Compilaciones de SQL Server 2008 R2 que se publicaron después del lanzamiento de SQL Server 2008 R2

SQL Server 2008 Service Pack 1

La corrección para este problema se publicó por primera vez en la actualización acumulativa 12 para SQL Server 2008 Service Pack 1.

Nota Dado que las compilaciones son acumulativas, cada nueva versión de corrección contiene todas las revisiones y todas las correcciones de seguridad que se incluyeron con la versión de corrección de SQL Server 2008 anterior. Microsoft recomienda que considere la posibilidad de aplicar la corrección más reciente que contiene esta revisión. Si desea obtener más información, haga clic en el número de artículo siguiente para verlo en Microsoft Knowledge Base:

970365 Las compilaciones de SQL Server 2008 que se publicaron después de SQL Server 2008 Service Pack 1 se publicaron revisiones de Microsoft SQL Server 2008 se crean para service packs de SQL Server específicos. Debe aplicar una revisión de SQL Server 2008 Service Pack 1 a una instalación de SQL Server 2008 Service Pack 1. De forma predeterminada, todas las revisiones que se proporcionan en un SQL Server Service Pack se incluyen en el siguiente SQL Server Service Pack.

SQL Server 2008 Service Pack 2

La corrección para este problema se publicó por primera vez en la actualización acumulativa 2 para SQL Server 2008 Service Pack 2.

Nota Dado que las compilaciones son acumulativas, cada nueva versión de corrección contiene todas las revisiones y todas las correcciones de seguridad que se incluyeron con la versión de corrección de SQL Server 2008 anterior. Microsoft recomienda que considere la posibilidad de aplicar la corrección más reciente que contiene esta revisión. Si desea obtener más información, haga clic en el número de artículo siguiente para verlo en Microsoft Knowledge Base:

2402659 Las compilaciones de SQL Server 2008 que se publicaron después de SQL Server 2008 Service Pack 2 se publicaron revisiones de Microsoft SQL Server 2008 se crean para service packs de SQL Server específicos. Debe aplicar una revisión de SQL Server 2008 Service Pack 2 a una instalación de SQL Server 2008 Service Pack 2. De forma predeterminada, todas las revisiones que se proporcionan en un SQL Server Service Pack se incluyen en el siguiente SQL Server Service Pack.

SQL Server 2005 Service Pack 3

La corrección para este problema se publicó por primera vez en la actualización acumulativa 13 para SQL Server 2005 Service Pack 3.

Nota Dado que las compilaciones son acumulativas, cada nueva versión de corrección contiene todas las revisiones y todas las correcciones de seguridad que se incluyeron con la versión de corrección de SQL Server 2005 anterior. Microsoft recomienda que considere la posibilidad de aplicar la corrección más reciente que contiene esta revisión. Si desea obtener más información, haga clic en el número de artículo siguiente para verlo en Microsoft Knowledge Base:

960598 Las compilaciones de SQL Server 2005 que se publicaron después de SQL Server 2005 Service Pack 3 se publicaron con revisiones de Microsoft SQL Server 2005 se crean para service packs de SQL Server específicos. Debe aplicar una revisión de SQL Server 2005 Service Pack 3 a una instalación de SQL Server 2005 Service Pack 3. De forma predeterminada, todas las revisiones que se proporcionan en un SQL Server Service Pack se incluyen en el siguiente SQL Server Service Pack.

SQL Server 2005 Service Pack 4

La corrección para este problema se publicó por primera vez en la actualización acumulativa 1 para SQL Server 2005 Service Pack 4.

Nota Dado que las compilaciones son acumulativas, cada nueva versión de corrección contiene todas las revisiones y todas las correcciones de seguridad que se incluyeron con la versión de corrección de SQL Server 2005 anterior. Microsoft recomienda que considere la posibilidad de aplicar la corrección más reciente que contiene esta revisión.

Debe aplicar una revisión de SQL Server 2005 Service Pack 4 a una instalación de SQL Server 2005 Service Pack 4. De forma predeterminada, todas las revisiones que se proporcionan en un SQL Server Service Pack se incluyen en el siguiente SQL Server Service Pack.

Estado

Microsoft ha confirmado que se trata de un problema de los productos de Microsoft enumerados en la sección "Se aplica a".

Solución alternativa

Instala esta revisión y reinicia SQL Server podría mejorar el rendimiento de la recuperación. Por ejemplo, el rendimiento podría mejorarse para las siguientes operaciones después de instalar esta revisión y reiniciar SQL Server:

Recuperación de base de datos
Creación de reflejo de la base de datos
Instantánea de la base de datos
Actividades del lector de registro de replicación de transacciones de base de datos

Si no puede instalar esta revisión, puede realizar las siguientes soluciones alternativas para mitigar un problema existente y evitar una aparición futura.

Mitigar un problema existente

Espera a que finalice

la operación de restauración o recuperación Si tiene una base de datos no recuperada que experimenta un bajo rendimiento al restaurar o recuperar la base de datos, es posible que tenga que esperar a que se complete la operación de restauración o recuperación. Por ejemplo, es posible que vea el estado sin conexión o el estado de recuperación en SQL Server Management Studio (SSMS) para una base de datos no recuperada. Detener SQL Server normalmente no ofrece alivio para una recuperación lenta y puede tardar más tiempo en repetir la misma fase de análisis de recuperación, en la fase de rehacer o en la de deshacer.
Evite restaurar la secuencia de registro de transacciones que contiene miles de VLFs

Si experimenta el bajo rendimiento al restaurar y recuperar una base de datos mediante un archivo de copia de seguridad, puede evitar restaurar las secuencias de registro de transacciones que contienen miles de VLFs. Para identificar el archivo de copia de seguridad que tiene la mayor parte de los archivos de registro virtuales registrados, use la siguiente instrucción para ver las columnas FirstLSN y LastLSN en los archivos de copia de seguridad de registro:
RESTORE HEADERONLY FROM DISK='C:\folder\file.trn'

Puede optar por evitar la restauración de los archivos de copia de seguridad del registro. O bien, puede usar la instrucción STOP AT en los comandos RESTORE para evitar las partes muy fragmentadas de los registros de transacciones. Si no restaura completamente las secuencias de registro hasta el punto más reciente en el tiempo durante un escenario de recuperación de errores, la pérdida de datos se produce en la base de datos SQL Server. Esta pérdida de datos se produce porque no se mantienen todas las transacciones. Por lo tanto, existe una decisión de compensación empresarial. Puede restaurar por completo un registro de transacciones muy fragmentado. Sin embargo, esta operación puede tardar varias horas. O bien, puede usar la instrucción STOP AT en la recuperación para detener la recuperación antes de la parte altamente fragmentada del registro. Sin embargo, se perderán todas las transacciones que falten que omita.

Nota Sin instalar esta revisión, normalmente no hay recurso seguro para una recuperación acelerada después de reiniciar SQL Server. SQL Server tiene que localizar la lista de VLF para analizar los archivos de registro, rehacer transacciones completadas y luego deshacer transacciones incompletas para finalizar la recuperación y poner la base de datos en línea de forma segura. No puede omitir transacciones de forma segura durante la recuperación.

Evitar que se produzca en el futuro

Establecer el incremento de crecimiento automático de la base de datos en un tamaño

adecuado Si el tamaño del incremento de crecimiento automático es demasiado pequeño, habrá muchos archivos de registro virtual (VL) y puede experimentar un rendimiento lento en SQL Server. Si el tamaño del incremento de crecimiento automático es demasiado grande, las consultas que hacen que los registros de transacciones aumenten automáticamente pueden tener que esperar mucho tiempo para finalizar un crecimiento. Por lo tanto, puede producirse un error de tiempo de salida en SQL Server. Para solucionar estos problemas, puede establecer el tamaño del incremento de crecimiento automático de la base de datos en un tamaño adecuado.
Eliminar el gran número de VLF y usar un crecimiento

manual Si hay muchas VLF en el registro de transacciones, reduzca el tamaño del registro de transacciones y haga que aumente antes del pico de negocio para satisfacer la demanda mediante un crecimiento manual. Por ejemplo, el registro de transacciones vuelve a tener un tamaño medio razonable en incrementos grandes o en un único crecimiento manual. Por lo tanto, el tamaño del registro de transacciones alcanza una capacidad máxima, y los archivos de copia de seguridad de registro se programan de forma frecuente y periódica. Además, el registro de transacciones podría truncarse y las VLF del registro de transacciones se pueden reutilizar en un ciclo.
Cómo reducir y aumentar el registro de transacciones manualmente

Para corregir un registro que tiene demasiadas VLFs, siga estos pasos para reducir el registro y volver a aumentarlo manualmente:
1. Si la base de datos se encuentra en un modelo de recuperación completa o con registro masivo, tiene que realizar una copia de seguridad del registro de transacciones para permitir que los VLF activos se trunco y se vuelvan a usar. BACKUP LOG nombre_de_base de datos TO DISK='C:\folder\log_backupfile.trn'
  Para obtener más información sobre cómo hacer una copia de seguridad del archivo de registro de transacciones mediante SSMS, visite el siguiente sitio web de Microsoft Developer Network (MSDN):
  
  Cómo hacer una copia de seguridad del archivo de registro de transacciones mediante SSMSPara obtener más información sobre cómo hacer una copia de seguridad del archivo de registro de transacciones mediante instrucciones Transact-SQLTransact-SQL, visite el siguiente sitio web de MSDN:
  
  Cómo realizar una copia de seguridad del archivo de registro de transacciones mediante instrucciones Transact-SQLTransact-SQL
2. Para determinar el nombre lógico del archivo de registro de transacciones, ejecute una de las instrucciones siguientes.
  Declaración 1
  exec sp_helpfile Statement 2
  seleccionar * de sys.sysfiles Para reducir el tamaño del archivo de registro de transacciones al tamaño deseado, utilice el siguiente código:DBCC SHRINKFILE(transactionloglogicalfilename, TRUNCATEONLY)
3. Puede aumentar el tamaño del archivo de registro de transacciones a un tamaño adecuado. Le recomendamos que deje que el tamaño del archivo de registro de transacciones aumente al tamaño normal máximo. Por lo tanto, se evita el incremento de crecimiento automático. Para establecer el tamaño del registro de transacciones, use la página Propiedades de la base de datos en SSMS o use la siguiente sintaxis DE ALTER DATABASE:MODIFY FILE ( NAME = transactionloglogicalfilenae, SIZE=newtotalsize MB)
  Para obtener más información sobre cómo aumentar el tamaño de una base de datos en SSMS, visite el siguiente sitio web de MSDN:
  
  Cómo aumentar el tamaño de una base de datos en SSMSPara obtener más información sobre la sintaxis MODIFICAR ARCHIVO DE ALTER DATABASE, visite el siguiente sitio web de MSDN:
  
  Información general sobre la sintaxis MODIFICAR ARCHIVO DE ALTER DATABASE

Más información

Puede comprobar el número de segmentos VLF revisando el archivo de registro de errores SQL y, a continuación, buscando el número de secuencia de registro (LSN) en cada archivo de copia de seguridad del registro de transacciones. Los primeros dígitos antes del símbolo de dos puntos en los LSN corresponden al número del LSN.

Por ejemplo, el primer número del primer mensaje informativo para el LSN es 1. Sin embargo, el primer número del segundo mensaje informativo para el LSN es 100001. En este escenario, hay 100 000 VLFs que se utilizan entre la hora del primer mensaje informativo y el segundo mensaje informativo. Por lo tanto, el registro de transacciones fragmentadas registrado que tiene muchos archivos de registro virtuales (VLFs) se asemeja a lo siguiente:

Se ha realizado una copia de seguridad de {Log. Base de datos: nombre_de_archivo, fecha(hora) de creación: 07/2010/08(12:36:46), primer LSN: 1:5068:70, último LSN: 1:5108:1, número de dispositivos de volcado: 1, información del dispositivo: (FILE=1, TYPE=DISK: {'C:\folder\logbackup1.trn'}). Solo se trata de un mensaje informativo. No es necesaria ninguna acción del usuario.

Se realizó una copia de seguridad del registro. Base de datos: nombre_de_archivo, fecha(hora) de creación: 07/2010/08(15:36:46), primer LSN: 100001:5108:1, último LSN: 100002:5108:1, número de dispositivos de volcado: 1, información del dispositivo: (FILE=2, TYPE=DISK: {'C:\folder\logbackup2.trn'}). Solo se trata de un mensaje informativo. No es necesaria ninguna acción del usuario.}

La tabla siguiente proporciona más información sobre los productos o herramientas que comprueban automáticamente la condición que se describe en la sección "Síntomas" en su caso de SQL Server y en las versiones de SQL Server en las que se evalúa la regla.

Software de regla	Título de regla	Descripción de regla	Versiones de producto para las que se ha evaluado la regla
System Center Advisor	SQL Server con la replicación transactional, el rendimiento del agente del lector de registro puede verse afectado debido al tamaño del registro de transacciones o al número de VLF	En este SQL Server asesor de instancia ha detectado la replicación de transacciones de presencia con un número considerablemente mayor de VLFs o tamaño de TLOG. El rendimiento del agente del lector de registro se ve afectado negativamente por el tamaño del registro de transacciones o el número de VLF. Reduzca el tamaño del registro de transacciones y el número de VLF para mejorar el rendimiento del Agente de registro del lector de registro.	SQL Server 2008 SQL Server 2008 R2 SQL Server 2012

Referencias

Para obtener más información sobre la arquitectura física del registro de transacciones, visite el siguiente sitio web de MSDN:

Información general sobre la arquitectura física del registro

de transacciones Para obtener más información sobre los números de secuencia de registro (LSN), visite el siguiente sitio web de MSDN:

Información general sobre los números de secuencia de

registro Para obtener más información sobre el error 1413 al iniciarse la creación de reflejo de la base de datos, visita el siguiente sitio web de MSDN:

Información general sobre el error 1413 al iniciar

la creación de reflejo de la base de datos Para obtener más información sobre cómo una estructura de archivos de registro puede afectar al tiempo de recuperación de la base de datos, visite el siguiente sitio web de MSDN:

Cómo puede afectar la estructura de un archivo de registro al tiempo

de recuperación de la base de datos Para obtener más información sobre las VL del registro de transacciones, visite el siguiente sitio web de MSDN:

Información general sobre el archivo

de registro de transacciones Para obtener más información sobre cómo crear una instantánea de la base de datos, visite el siguiente sitio web de MSDN:

Cómo crear una instantánea de base de datosPara obtener más información sobre el modelo de mantenimiento incremental para SQL Server, haga clic en el número de artículo siguiente para verlo en Microsoft Knowledge Base:

935897 El equipo de SQL Server dispone de un modelo de mantenimiento incremental para ofrecer revisiones para los problemas notificadosPara obtener más información sobre el esquema de nomenclatura para las actualizaciones de SQL Server, haga clic en el número de artículo siguiente para verlo en Microsoft Knowledge Base:

822499Nuevo esquema de nomenclatura para los paquetes de actualización de software de Microsoft SQL Server Para obtener más información sobre terminología de actualización de software, haga clic en el número de artículo siguiente para verlo en Microsoft Knowledge Base:

824684 Description of the standard terminology that is used to describe Microsoft software updates (Descripción de la terminología estándar usada para describir las actualizaciones de software de Microsoft)