Un registro de transacciones crece inesperadamente o se llena en SQL Server

Resumen

Expansión de registro de transacción puede producirse por uno de los siguientes motivos o escenarios.


Nota: En SQL Server 2005 y versiones posteriores, puede revisar las columnas log_reuse_wait y log_reuse_wait_desc de la vista de catálogo sys.databases para determinar por qué no se reutiliza el espacio de registro de transacciones y por qué no se puede truncar el registro de transacciones.






Transacciones no confirmadas
Transacciones explícitas permanecen sin confirmar si no emite un comando COMMIT o ROLLBACK explícito. Esto ocurre más frecuentemente cuando una aplicación emite una cancelar o un comando KILL de Transact-SQL sin un comando ROLLBACK correspondiente. Se produce la cancelación de la transacción, pero no deshace. Por lo tanto, SQL Server no se puede truncar todas las transacciones que se produce después de esto porque la transacción anulada todavía está abierta. Puede utilizar la referencia de Transact-SQL de DBCC OPENTRAN para comprobar que hay una transacción activa en una base de datos en un momento determinado. Para obtener más información acerca de esta situación particular, haga clic en los números de artículo siguientes para verlos en Microsoft Knowledge Base:

295108 transacción incompleta puede contener un gran número de bloqueos y bloqueo de mayúsculas

171224 comprender cómo funciona el comando KILL de Transact-SQL

Además, vea el tema "DBCC OPENTRAN" en libros en pantalla de SQL Server.

Escenarios que pueden resultar en transacciones sin confirmar:
  • Diseño de una aplicación que se supone que todos los errores de causan deshechas.
  • Diseño de una aplicación completamente sin considerar el comportamiento de SQL Server cuando se deshace para transacciones con nombre o anidado especialmente con nombre. Si se intenta deshacer una transacción denominada interior, recibirá el siguiente mensaje de error:
    Servidor: Msg 6401, nivel 16, estado 1, línea 13 no deshacer InnerTran. Se ha encontrado ninguna transacción ni punto de almacenamiento de ese nombre.
    Después de que SQL Server genera el mensaje de error, sigue a la instrucción next. Esto es por diseño. Para obtener más información, vea el tema "Transacciones anidadas" o "Dentro de SQL Server" en los libros en pantalla de SQL Server.

    Al diseñar la aplicación, se recomienda lo siguiente:
    • unidad de transacción sólo un lápiz (considere la posibilidad de que otro proceso que llame a la suya).
    • Compruebe @@TRANCOUNT antes de emitir COMMIT, ROLLBACK, una devolución o un comando similar o instrucción.
    • Escribir su código con el supuesto de que otro @@TRANCOUNT puede "anidar" el suyo y planee para que el @@TRANCOUNT externo se revierta cuando se produzca un error.
    • Revise savepoint y marque las opciones para las transacciones. (Estos no liberan bloqueos!)
    • Realice una comprobación completa.
  • Una aplicación que permite la interacción con el usuario dentro de las transacciones. Esto hace que la transacción puede permanecer abierta durante mucho tiempo, y este bloqueo de causas y registro de transacciones crecimiento porque no se puede truncar la transacción abierta y se agregan nuevas transacciones en el registro después de la transacción abierta.
  • Una aplicación que no comprueba @@TRANCOUNT para comprobar que no existen transacciones abiertas.
  • Red u otros errores que cierran la conexión de la aplicación de cliente a SQL Server sin informar a él.
  • Agrupación de conexiones. Después de crean subprocesos de trabajo, SQL Server reutiliza si no están dando servicio a una conexión. Si una conexión de usuario inicia una transacción y se desconecta antes de confirmar o deshacer la transacción y una conexión después de reutiliza el mismo subproceso, la transacción anterior permanece abierta. Esta situación provoca bloqueos que permanezcan abiertos de la transacción anterior y evita el truncamiento de las transacciones confirmadas en el registro. Esto da como resultado el tamaño del archivo de registro de gran tamaño. Para obtener más información acerca de la agrupación de conexiones, haga clic en el número de artículo siguiente para verlo en Microsoft Knowledge Base:

    164221 cómo habilitar la agrupación de conexiones en una aplicación ODBC

Si la opción autogrow está establecida en Microsoft SQL Server 2005 y versiones posteriores, SQL Server 2000 y SQL Server 7.0, pueden expandir automáticamente los archivos de registro de transacción para el tamaño de archivo máximo de 2 terabytes (TB) por cada archivo de registro.

Normalmente, el tamaño del archivo de registro de transacciones se estabiliza cuando puede contener el número máximo de transacciones que pueden producirse entre los truncamientos de registro de transacción que se activan mediante puntos de comprobación o copias de seguridad de registro de transacciones.

Sin embargo, en algunos casos que el registro de transacciones puede ser muy grande y quedarse sin espacio o lleno. Normalmente, recibirá el siguiente mensaje de error cuando un archivo de registro de transacción utiliza todo el espacio disponible en disco y no se puede expandir más largo:
Error: 9002, gravedad: 17, estado: 2
El archivo de registro de base de datos ' %. * ls' está lleno.
Si está utilizando SQL Server 2005, recibirá un mensaje de error similar al siguiente:
Error: 9002, gravedad: 17, estado: 2
El registro de transacciones para la base de datos ' %. * ls' está lleno. Para averiguar por qué no se puede reutilizar el espacio en el registro, vea la columna log_reuse_wait_desc de sys.databases
Además de este mensaje de error, SQL Server puede marcar las bases de datos como sospechosa debido a la falta de espacio para expansión de registro de transacciones. Para obtener más información acerca de cómo recuperarse de esta situación, vea el tema "Espacio insuficiente en disco" en los libros en pantalla de SQL Server.

Además, expansión de registro de transacción puede producirse por uno de los siguientes motivos o en uno de los siguientes escenarios:
  • Un archivo de registro de transacciones muy grandes.
  • Las transacciones pueden fallar y pueden empezar a revertir.
  • Las transacciones pueden tardar mucho tiempo en completarse.
  • Pueden producirse problemas de rendimiento.
  • Se pueden producir bloqueos.
  • La base de datos está participando en un grupo de disponibilidad AlwaysOn.


Transacciones muy grandes
Registros de registro en los archivos de registro se truncan en transacción por transacción. Si el ámbito de la transacción es grande, que después de iniciado transacción y las transacciones no se quitan del registro de transacciones a menos que se complete. Esto puede resultar en archivos de registro grandes. Si la transacción es suficientemente grande, el archivo de registro podría utilizar todo el espacio disponible en disco y hacer que el tipo de "registro de transacciones completo" del mensaje de error como Error 9002. Para obtener más información acerca de qué hacer cuando aparece este tipo de mensaje de error, consulte la sección "Más información" de este artículo. Además, se tarda mucho tiempo y la sobrecarga de SQL Server para deshacer las transacciones grandes.

Operaciones: Índice de DBCC DBREINDEX y crear
Debido a los cambios en el modelo de recuperación en SQL Server 2000, cuando se utiliza el modo de recuperación completa y ejecuta DBCC DBREINDEX, el registro de transacciones puede expandir significativamente más con respecto de SQL Server 7.0 en un modo de recuperación equivalente con el uso de SELECT INTO o copia masiva y con "Trunc. Cierre la sesión en chkpt.".

Aunque el tamaño del registro de transacciones después de la operación DBREINDEX puede ser un problema, este enfoque proporciona un mejor performance de restore log.


Al restaurar desde copias de seguridad de registro de transacciones
Esto se describe en el siguiente artículo de Microsoft Knowledge Base:
232196 espacio de registro utilizado parece crecer después de restaurar desde la copia de seguridad


Si configura SQL Server 2000 para utilizar el modo de registro masivo y emitir una instrucción de copia masiva o SELECT INTO, cada medida modificada es marcado y, a continuación, copia de seguridad cuando hace copia de seguridad del registro de transacciones. Aunque esto permite volver hasta los registros de transacciones y recuperación de errores, incluso después de realizar operaciones masivas, esto aumentaría el tamaño de los registros de transacciones. SQL Server 7.0 no incluye esta característica. SQL Server 7.0 sólo registra qué extensiones se cambian, pero no registra las extensiones reales. Por lo tanto, el registro utiliza mucho más espacio en SQL Server 2000 en SQL Server 7.0 en el modo de registro masivo, pero no tanto como lo hace en modo completo.

Las aplicaciones cliente no procesan todos los resultados
Si emite una consulta a SQL Server y no controlan los resultados inmediatamente, puede mantener bloqueos y reducir la simultaneidad en el servidor.

Por ejemplo, supongamos que se emite una consulta que necesita filas de dos páginas para llenar el conjunto de resultados. SQL Server analiza, compila y ejecuta la consulta. Esto significa que se agregan los bloqueos compartidos en las dos páginas que contienen las filas que debe tener para satisfacer su consulta. Además, suponga que no todas las filas caben en un paquete TDS de SQL Server (el método por el cual el servidor se comunica con el cliente). Paquetes TDS se rellena y envía al cliente. Si todas las filas de la primera página caben en el paquete TDS, SQL Server libera el bloqueo compartido en esa página, pero deja un bloqueo compartido en la segunda página. SQL Server, a continuación, espera que el cliente solicite más datos (puede hacerlo mediante DBNEXTROW/DBRESULTS, SQLNextRow/SQLResults o FetchLast/FetchFirst por ejemplo).

Esto significa que el bloqueo compartido se mantiene hasta que el cliente solicita el resto de los datos. Otros procesos que solicitan datos de la segunda página pueden estar bloqueados.


Consulta el tiempo de espera antes de que un registro de transacciones finaliza la expansión y recibe mensajes de error falsos de 'Registro completo'
En esta situación, aunque no hay suficiente espacio en disco, todavía recibe un mensaje de error "sin espacio".

Esta situación varía para SQL Server 7.0 y SQL Server 2000.

Una consulta puede provocar que el registro de transacciones se expanda automáticamente si el registro de transacciones está casi lleno. Esta operación puede tardar más tiempo, y una consulta puede ser detenida o puede exceder su tiempo de espera debido a esto. SQL Server 7.0, se devuelve el error 9002 en esta situación. Este problema no se aplica a SQL Server 2000.

En SQL Server 2000, si tiene la opción de reducción automática activada para una base de datos, hay un pequeño de tiempo durante el cual se intenta expandir automáticamente un registro de transacciones. Sin embargo, que no se expanda porque la función de reducción automática se está ejecutando al mismo tiempo. Esto también puede provocar falsas instancias de error 9002.

Normalmente, la expansión automática de archivos de registro de transacciones se produce rápidamente. Sin embargo, en las situaciones siguientes, pueden tardar más de lo habitual:
  • Incrementos de crecimiento son demasiado pequeños.
  • El servidor es lento por diversas razones.
  • Unidades de disco no son lo suficientemente rápidas.


Transacciones sin duplicar
Si está utilizando replicación, puede expandir el tamaño del registro de transacciones de la base de datos del publicador . Las transacciones que afectan a los objetos que se replican se marcan como "Para la replicación". Estas transacciones, como las transacciones sin confirmar, no se eliminan después de checkpoint o después de hacer copia de seguridad del registro de transacciones hasta que la tarea de lector del registro copia las transacciones en la base de datos de distribución y quita la marca de ellos. Si un problema con la tarea de lector del Registro impide leer estas transacciones en la base de datos de publisher , puede seguir expandir como el número de transacciones no replican aumenta el tamaño del registro de transacciones. Puede utilizar la referencia de Transact-SQL de DBCC OPENTRAN para identificar la transacción más antigua no replicada.

Para obtener más información acerca de cómo solucionar problemas de transacciones sin duplicar, consulte los temas "sp_replcounters" y "sp_repldone" en libros en pantalla de SQL Server.

Para obtener más información, haga clic en los números de artículo siguientes para verlos en Microsoft Knowledge Base:

CORREGIR 306769 : no se puede truncar el registro de transacciones de instantánea de base de datos publicada

CORREGIR 240039 : DBCC OPENTRAN no presenta información de replicación

CORREGIR 198514 : restauración al nuevo servidor, las transacciones permanecen en el registro



AlwaysOn aplicando registros de transacciones a una base de datos secundaria ' AVAILABILITY_REPLICA'

En SQL Server 2012 con grupos de disponibilidad AlwaysOn habilitado, puede que aparezca el siguiente mensaje en el registro de errores SQL:

Error: 9002, gravedad: 17, estado: 9.
El registro de transacciones para la base de datos ' %. * ls' está llena debido a 'AVAILABILITY_REPLICA'

AVAILABILITY_REPLICA log_reuse_wait indica que una segunda réplica de grupos de disponibilidad AlwaysOn está aplicando registros de transacciones de esta base de datos a una base de datos secundaria correspondiente.

Hay dos escenarios que pueden conducir a registrar crecimiento en una base de datos de disponibilidad y la AVAILABILITY_REPLICA' log_reuse_wait:

Escenario 1: Entrega de latencia registra cambios en secundaria

Cuando una transacción se realiza en el principal, los bloques registrados deben ser entregados y consolidados para el archivo de registro de la base de datos en el secundario. Cualquier retraso impedirá el truncamiento de los cambios registrados en la base de datos en la réplica principal.

Escenario 2: Latencia de rehacer

Una vez consolidado en el archivo de registro de la base de datos secundaria un subproceso dedicado Rehacer aplica a los registros.

Si la operación de rehacer no es capaz de mantener el registro de transacciones generado, puede conducir potencialmente a iniciar el crecimiento. El principal será no se puede truncar el registro de transacciones si la operación de rehacer segunda réplica detrás de aplicar esos cambios a una base de datos secundaria correspondiente. Si hay más de una secundaria, para identificar a qué base de datos secundaria que está retrasando el truncamiento de logs, compare la columna truncation_lsn de la vista de administración dinámica sys.dm_hadr_database_replica_states a través de los múltiples secundarios.

Puede utilizar el panel AlwaysOn y vistas de administración dinámica sys.dm_hadr_database_replica_states para ayudar a supervisar el registro de cola de envío y de cola. Algunos campos de clave son:

CampoDescripción
log_send_queue_sizeCantidad de registros que no han llegado a la réplica secundaria
log_send_rateFrecuencia en qué registro se envían registros a las bases de datos secundarias
redo_queue_sizeLa cantidad de registros en los archivos de registro de la segunda réplica que ha no ha sido rehacer, en kilobytes (KB)
redo_rateLa velocidad a la que se se rehacen los registros en una base de datos secundaria dada, en kilobytes (KB) por segundo
last_redone_lsnNúmero de secuencia de registro real del último registro que se rehizo en la base de datos secundaria. last_redone_lsn siempre es menor que last_hardened_lsn
last_received_lsnRegistrar el identificador de bloque identifica el punto hasta el que se han recibido todos los bloques de registro la réplica secundaria que aloja esta base de datos secundaria. Refleja un identificador de bloque de registro se rellena con ceros. No es un número de secuencia de registro real.

Nota: Para obtener más información acerca de la vista de sys.dm_hadr_database_replica_states, consulte el siguiente sitio Web de TechNet:

http://technet.microsoft.com/en-us/library/ff877972.aspx



Información avanzada

El registro de transacciones para cualquier base de datos se administra como un conjunto de archivos de registro virtuales (VLF). SQL Server determina el tamaño de los archivos VLF internamente basándose en el tamaño total del archivo de registro y el incremento de crecimiento que se utiliza cuando el registro se expande. Un registro siempre se expande en las unidades de VLF todo y sólo puede comprimir un límite de VLF. Puede existir un VLF en uno de tres estados: activo, recuperable y REUTILIZABLE.
  • Activo: la parte activa del registro comienza en el número de secuencia de registro mínimo (LSN) que representa una transacción activa (sin confirmar). La parte activa del registro finaliza en el LSN del último escrito. Cualquier VLF que contengan cualquier parte del registro activo se considera activos VLF. (espacio no utilizado en el registro físico no es parte de cualquier VLF).
  • Recuperable: la parte del registro que viene antes de la transacción activa más antigua sólo es necesaria mantener una secuencia de copias de seguridad del registro de recuperación.
  • REUTILIZABLE: si no se mantienen las copias de seguridad del registro de transacciones, o si ya hizo copia de seguridad del registro, SQL Server reutiliza VLF antes de la transacción activa más antigua.
Cuando SQL Server llega al final del archivo de registro físico, comienza a volver a utilizar ese espacio en el archivo físico emitiendo una operación dando VUELTAS atrás hasta el principio de los archivos. De hecho, SQL Server recicla el espacio en el archivo de registro que ya no es necesario para propósitos de recuperación o copia de seguridad. Si se mantiene una secuencia de copia de seguridad del registro, la parte del registro antes del mínimo LSN no puede ser sobrescrito hasta que la copia de seguridad o truncar los registros. Después de realizar la copia de seguridad de registro, SQL Server puede rodear con un círculo al principio del archivo. Después de los círculos atrás para empezar a escribir entradas del registro anterior en el archivo de registro de SQL Server, la parte reutilizable del registro es, a continuación, entre el final del registro lógico y parte activa del registro.

Para obtener más información, vea el tema "Arquitectura física del registro de transacciones" en los libros en pantalla de SQL Server. Además, puede ver un diagrama y una explicación al respecto en "Inside SQL Server 7.0" en la página 190 (Soukup, Ron. Inside Microsoft SQL Server 7.0, Microsoft Press, 1999) y también en las páginas 182 a 186 de "Inside SQL Server 2000" (Delaney, Kalen. Inside Microsoft SQL Server 2000, Microsoft Press, 2000).
Las bases de datos de SQL Server 2000 y SQL Server 7.0 tienen las opciones de crecimiento automático y autorreducción. Puede utilizar estas opciones para comprimir o expandir el registro de transacciones.

Para obtener más información acerca de cómo estas opciones pueden afectar al servidor, haga clic en el número de artículo siguiente para verlo en Microsoft Knowledge Base:

Consideraciones de 315512 para la configuración de crecimiento automático y autorreducción en SQL Server

Truncamiento de archivo de registro de transacciones se diferencia de la compresión del archivo de registro de transacciones. Cuando SQL Server trunca un archivo de registro de transacciones, esto significa que se elimina el contenido de ese archivo (por ejemplo, las transacciones confirmadas). Sin embargo, al ver el tamaño del archivo desde una perspectiva de espacio de disco (por ejemplo, en el Explorador de Windows o mediante el comando dir ), el tamaño no cambia. Sin embargo, el espacio dentro del archivo .ldf ahora puede ser reutilizado por nuevas transacciones. Sólo cuando SQL Server se reduce el tamaño del archivo de registro de transacción ¿ves realmente un cambio en el tamaño físico del archivo de registro.

Para obtener más información acerca de cómo reducir los registros de transacciones, haga clic en los números de artículo siguientes para verlos en Microsoft Knowledge Base:

256650 cómo reducir el registro de transacciones de SQL Server 7.0

272318 reducir el registro de transacciones en SQL Server 2000 con DBCC SHRINKFILE

Para obtener más información sobre el uso del registro de transacciones de SQL Server 6.5, haga clic en el número de artículo siguiente para verlo en Microsoft Knowledge Base:

Registro de transacciones hace de SQL 110139 se llene

Cómo localizar las consultas que consumen una gran cantidad de espacio de registro en SQL Server 2005 y versiones posteriores

En SQL Server 2005 y versiones posteriores, puede utilizar la vista de administración dinámica (DMV) de sys.dm_tran_database_transactions para localizar las consultas que consumen grandes cantidades de espacio de registro. Las columnas siguientes en la sys.dm_tran_database_transactions DMV puede ser útil:
  • database_transaction_log_bytes_used
  • database_transaction_log_bytes_used_system
  • database_transaction_log_bytes_reserved
  • database_transaction_log_bytes_reserved_system
  • database_transaction_log_record_count
Puede consultar la columna sql_handle de la sys.dm_exec_requests DMV para obtener el texto de instrucción real que consume gran cantidad de espacio de registro. Para ello, uniendo la DMV sys.dm_tran_database_transactions y la DMV sys.dm_tran_session_transactions en la columna transaction_id y, a continuación, agregar una combinación adicional con sys.dm_exec_requests en la columna "session_ID".


Para obtener más información acerca de la DMV sys.dm_tran_database_transactions, vaya al sitio Web de Microsoft Developer Network (MSDN) sys.dm_tran_database_transactions (Transact-SQL) .


Para obtener más información acerca de la DMV sys.dm_tran_session_transactions, vaya al sitio Web de MSDN sys.dm_tran_session_transactions (Transact-SQL) .


Para obtener más información acerca de la sys.dm_exec_requests DMV, vaya al sitio Web de MSDN sys.dm_exec_requests (Transact-SQL) .
Propiedades

Id. de artículo: 317375 - Última revisión: 17 ene. 2017 - Revisión: 1

Comentarios