Guía de arquitectura y administración de registros de transacciones de SQL ServerSQL Server Transaction Log Architecture and Management Guide

SE APLICA A: síSQL Server síAzure SQL Database síAzure SQL Data Warehouse síAlmacenamiento de datos paralelos APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

Todas las bases de datos de SQL ServerSQL Server tienen un registro de transacciones que graba todas las transacciones y las modificaciones que cada transacción realiza en la base de datos.Every SQL ServerSQL Server database has a transaction log that records all transactions and the database modifications that are made by each transaction. El registro de transacciones es un componente esencial de la base de datos y, si se produce un error del sistema, podría ser necesario para volver a poner la base de datos en un estado coherente.The transaction log is a critical component of the database and, if there is a system failure, the transaction log might be required to bring your database back to a consistent state. Esta guía proporciona información acerca de la arquitectura física y lógica del registro de transacciones.This guide provides information about the physical and logical architecture of the transaction log. Comprender la arquitectura puede mejorar la eficacia en la administración de registros de transacciones.Understanding the architecture can improve your effectiveness in managing transaction logs.

Arquitectura lógica del registro de transaccionesTransaction Log Logical Architecture

El registro de transacciones de SQL ServerSQL Server funciona desde el punto de vista lógico como si fuese una cadena de entradas de registro.The SQL ServerSQL Server transaction log operates logically as if the transaction log is a string of log records. Cada entrada del registro está identificada por un número de flujo de registro (LSN, Log Sequence Number).Each log record is identified by a log sequence number (LSN). Las nuevas entradas del registro se escriben al final lógico del registro con un LSN mayor que el de las entradas anteriores.Each new log record is written to the logical end of the log with an LSN that is higher than the LSN of the record before it. Las entradas del registro se almacenan en la secuencia en la que se crean.Log records are stored in a serial sequence as they are created. Cada entrada del registro contiene el Id. de la transacción a la que pertenece.Each log record contains the ID of the transaction that it belongs to. Por cada transacción, las entradas del registro asociadas a dicha transacción se vinculan individualmente en una cadena con punteros hacia atrás, para acelerar así la reversión de la transacción.For each transaction, all log records associated with the transaction are individually linked in a chain using backward pointers that speed the rollback of the transaction.

Los registros de modificaciones de datos registran la operación lógica llevada a cabo o las imágenes anterior y posterior de los datos modificados.Log records for data modifications record either the logical operation performed or they record the before and after images of the modified data. La imagen anterior es una copia de los datos antes de llevar a cabo la operación; la imagen posterior es una copia de los datos después de haber realizado la operación.The before image is a copy of the data before the operation is performed; the after image is a copy of the data after the operation has been performed.

Los pasos para recuperar una operación dependen del tipo de registro:The steps to recover an operation depend on the type of log record:

  • Registro de la operación lógicaLogical operation logged

    • Para poner al día la operación lógica, se vuelve a ejecutar la operación.To roll the logical operation forward, the operation is performed again.

    • Para revertir la operación lógica, se ejecuta la operación lógica inversa.To roll the logical operation back, the reverse logical operation is performed.

  • Registro de las imágenes anterior y posteriorBefore and after image logged

    • Para poner al día la operación, se aplica la imagen posterior.To roll the operation forward, the after image is applied.

    • Para revertir la operación, se aplica la imagen anterior.To roll the operation back, the before image is applied.

En el registro de transacciones se registran muchos tipos de operaciones.Many types of operations are recorded in the transaction log. Entre las operaciones se incluyen:These operations include:

  • El inicio y el final de cada transacción.The start and end of each transaction.

  • Todas las modificaciones de los datos (inserción, actualización y eliminación).Every data modification (insert, update, or delete). Esto incluye las modificaciones de las tablas, incluidas las tablas del sistema, hechas por procedimientos almacenados del sistema o por instrucciones del lenguaje de definición de datos (DDL).This includes changes by system stored procedures or data definition language (DDL) statements to any table, including system tables.

  • Las asignaciones o cancelaciones de asignación de páginas y extensiones.Every extent and page allocation or deallocation.

  • La creación o eliminación de una tabla o un índice.Creating or dropping a table or index.

También se registran las operaciones de reversión.Rollback operations are also logged. Cada transacción reserva espacio en el registro de transacciones para asegurarse de que existe suficiente espacio de registro para admitir una reversión provocada por una instrucción de reversión explícita o cuando se produce un error.Each transaction reserves space on the transaction log to make sure that enough log space exists to support a rollback that is caused by either an explicit rollback statement or if an error is encountered. La cantidad de espacio reservado depende de las operaciones realizadas en la transacción, pero normalmente equivale a la cantidad de espacio empleado para registrar cada operación.The amount of space reserved depends on the operations performed in the transaction, but generally it is equal to the amount of space used to log each operation. Este espacio reservado se libera cuando se completa la transacción.This reserved space is freed when the transaction is completed.

La sección del archivo de registro a partir de la primera entrada de registro que debe estar presente para una reversión correcta en toda la base de datos hasta la última entrada de registro escrita se denomina parte activa del registro o registro activo.The section of the log file from the first log record that must be present for a successful database-wide rollback to the last-written log record is called the active part of the log, or the active log. Esta es la sección del registro necesaria para una recuperación completa de la base de datos.This is the section of the log required to a full recovery of the database. No se puede truncar ninguna parte del registro activo.No part of the active log can ever be truncated. El número de secuencia de registro (LSN) de este primer registro se denomina el LSN de recuperación mínimo ( MinLSN).The log sequence number (LSN) of this first log record is known as the minimum recovery LSN (MinLSN).

Arquitectura física del registro de transaccionesTransaction Log Physical Architecture

El registro de transacciones de una base de datos está asignado a uno o varios archivos físicos.The transaction log in a database maps over one or more physical files. Conceptualmente, el archivo de registro es una cadena de entradas de registro.Conceptually, the log file is a string of log records. Físicamente, la secuencia de entradas del registro se almacena de forma eficaz en el conjunto de archivos físicos que implementa el registro de transacciones.Physically, the sequence of log records is stored efficiently in the set of physical files that implement the transaction log. Cada base de datos debe tener al menos un archivo de registro.There must be at least one log file for each database.

Motor de base de datos de SQL ServerSQL Server Database Engine segmenta cada archivo de registro físico internamente en una serie de archivos de registro virtuales (VLF).The Motor de base de datos de SQL ServerSQL Server Database Engine divides each physical log file internally into a number of virtual log files (VLFs). Los archivos de registro virtuales no tienen un tamaño fijo y no hay un número fijo de archivos de registro virtuales para un archivo de registro físico.Virtual log files have no fixed size, and there is no fixed number of virtual log files for a physical log file. Motor de base de datosDatabase Engine elige dinámicamente el tamaño de los archivos de registro virtuales al crear o ampliar los archivos de registro.The Motor de base de datosDatabase Engine chooses the size of the virtual log files dynamically while it is creating or extending log files. Motor de base de datosDatabase Engine intenta mantener un número reducido de archivos virtuales.The Motor de base de datosDatabase Engine tries to maintain a small number of virtual files. El tamaño de los archivos virtuales después de ampliar un archivo de registro equivale a la suma del tamaño del registro existente y el tamaño del nuevo incremento del archivo.The size of the virtual files after a log file has been extended is the sum of the size of the existing log and the size of the new file increment. El tamaño o número de archivos de registro virtuales no lo pueden configurar ni establecer los administradores.The size or number of virtual log files cannot be configured or set by administrators.

Nota

La creación de archivos de registro virtual (VLF) sigue este método:Virtual log file (VLF) creation follows this method:

  • Si el siguiente crecimiento es inferior a 1/8 del tamaño físico actual del registro, cree 1 VLF que cubra el tamaño del crecimiento (a partir de SQL Server 2014 (12.x)SQL Server 2014 (12.x)).If the next growth is less than 1/8 of current log physical size, then create 1 VLF that covers the growth size (Starting with SQL Server 2014 (12.x)SQL Server 2014 (12.x))
  • Si el siguiente crecimiento es superior a 1/8 del tamaño actual del registro, use el método para versiones anteriores a 2014:If the next growth is more than 1/8 of the current log size, then use the pre-2014 method:
    • Si el crecimiento es inferior a 64 MB, cree 4 VLF que cubran el tamaño del crecimiento (p. ej., en el caso de un crecimiento de 1 MB, cree 4 VLF de 256 KB).If growth is less than 64MB, create 4 VLFs that cover the growth size (e.g. for 1 MB growth, create four 256KB VLFs)
    • Si el crecimiento oscila entre 64 MB y 1 GB, cree 8 VLF que cubran el tamaño del crecimiento (p. ej., en el caso de un crecimiento de 512 MB, cree 8 VLF de 64 MB).If growth is from 64MB up to 1GB, create 8 VLFs that cover the growth size (e.g. for 512MB growth, create eight 64MB VLFs)
    • Si el crecimiento es superior a 1 GB, cree 16 VLF que cubran el tamaño del crecimiento (p. ej., en el caso de un crecimiento de 8 GB, cree 16 VLF de 512 MB).If growth is larger than 1GB, create 16 VLFs that cover the growth size (e.g. for 8 GB growth, create sixteen 512MB VLFs)

Si los archivos de registro crecen hasta un tamaño grande en muchos incrementos pequeños, tendrán numerosos archivos de registro virtuales.If the log files grow to a large size in many small increments, they will have many virtual log files. Esto puede retrasar el inicio de la base de datos, así como las operaciones de copias de seguridad y restauración del registro.This can slow down database startup and also log backup and restore operations. Por el contrario, si los archivos de registro están establecidos en un tamaño grande con pocos o solo un incremento, tendrán muy pocos archivos de registro virtuales muy grandes.Conversely, if the log files are set to a large size with few or just one increment, they will have few very large virtual log files. Para obtener más información sobre la estimación correcta de la configuración de tamaño requerido y crecimiento automático de un registro de transacción, consulte la sección Recomendaciones de Administrar el tamaño del archivo de registro de transacciones.For more information on properly estimating the required size and autogrow setting of a transaction log, refer to the Recommendations section of Manage the size of the transaction log file.

Se recomienda que los archivos de registro se definan con un valor size cercano al tamaño final necesario, con los incrementos requeridos para conseguir la distribución de VLF óptima, y que tengan también un valor de growth_increment relativamente alto.We recommend that you assign log files a size value close to the final size required, using the required increments to achieve optimal VLF distribution, and also have a relatively large growth_increment value. Vea la siguiente sugerencia para determinar la distribución de VLF óptima para el tamaño del registro de transacciones actual.See the tip below to determine the optimal VLF distribution for the current transaction log size.

  • El valor size, establecido por el argumento SIZE de ALTER DATABASE, es el tamaño inicial del archivo de registro.The size value, as set by the SIZE argument of ALTER DATABASE is the initial size for the log file.
  • El valor growth_increment (también conocido como el valor de crecimiento automático), establecido por el argumento FILEGROWTH de ALTER DATABASE, es la cantidad de espacio que se agrega al archivo cada vez que se necesita más espacio.The growth_increment value (also referred as the autogrow value), as set by the FILEGROWTH argument of ALTER DATABASE, is the amount of space added to the file every time new space is required.

Para obtener más información sobre los argumentos FILEGROWTH y SIZE de ALTER DATABASE, vea Opciones File y Filegroup de ALTER DATABASE (Transact-SQL).For more information on FILEGROWTH and SIZE arguments of ALTER DATABASE, see ALTER DATABASE (Transact-SQL) File and Filegroup Options.

Sugerencia

Para determinar la distribución óptima de VLF para el tamaño de registro de transacciones actual de todas las bases de datos en una instancia determinada, así como los incrementos de tamaño necesarios para conseguir el tamaño requerido, consulte este script.To determine the optimal VLF distribution for the current transaction log size of all databases in a given instance, and the required growth increments to achieve the required size, see this script.

El registro de transacciones es un archivo de registro circular.The transaction log is a wrap-around file. Considere, por ejemplo, una base de datos con un archivo de registro físico dividido en cuatro VLF.For example, consider a database with one physical log file divided into four VLFs. Cuando se crea la base de datos, el archivo de registro lógico empieza en el principio del archivo de registro físico.When the database is created, the logical log file begins at the start of the physical log file. Las nuevas entradas del registro se agregan al final del registro lógico y se expanden hacia el final del archivo físico.New log records are added at the end of the logical log and expand toward the end of the physical log. El truncamiento del registro libera los registros virtuales cuyas entradas son anteriores al número de flujo de registro de recuperación mínimo (MinLSN).Log truncation frees any virtual logs whose records all appear in front of the minimum recovery log sequence number (MinLSN). MinLSN es el número de flujo de registro de la entrada del registro más antigua necesaria para una reversión correcta de toda la base de datos.The MinLSN is the log sequence number of the oldest log record that is required for a successful database-wide rollback. El registro de transacciones de ejemplo sería similar al de la siguiente ilustración.The transaction log in the example database would look similar to the one in the following illustration.

tranlog3

Cuando el final del registro lógico llega al final del archivo de registro físico, las nuevas entradas del registro se escriben al principio del archivo de registro físico.When the end of the logical log reaches the end of the physical log file, the new log records wrap around to the start of the physical log file.

tranlog4

El ciclo se repite indefinidamente, siempre que el final del registro lógico no alcance el inicio del registro lógico.This cycle repeats endlessly, as long as the end of the logical log never reaches the beginning of the logical log. Si las entradas antiguas se truncan con la frecuencia suficiente para disponer siempre de espacio para todas las nuevas entradas de registro que se van a crear hasta el próximo punto de comprobación, el registro no se llena nunca.If the old log records are truncated frequently enough to always leave sufficient room for all the new log records created through the next checkpoint, the log never fills. Sin embargo, si el final del registro lógico llega al principio del registro lógico, se produce una de estas dos situaciones:However, if the end of the logical log does reach the start of the logical log, one of two things occurs:

  • Si el registro tiene habilitada la opción FILEGROWTH y hay espacio disponible en el disco, el archivo se amplía en la cantidad especificada en el parámetro growth_increment y las nuevas entradas del registro se escriben en la extensión.If the FILEGROWTH setting is enabled for the log and space is available on the disk, the file is extended by the amount specified in the growth_increment parameter and the new log records are added to the extension. Para obtener más información sobre la opción FILEGROWTH, vea Opciones File y Filegroup de ALTER DATABASE (Transact-SQL).For more information about the FILEGROWTH setting, see ALTER DATABASE File and Filegroup Options (Transact-SQL).

  • Si la opción FILEGROWTH no está habilitada o el disco que almacena el archivo de registro tiene menos espacio disponible que la cantidad especificada en growth_increment, se genera el error 9002.If the FILEGROWTH setting is not enabled, or the disk that is holding the log file has less free space than the amount specified in growth_increment, an 9002 error is generated. Para obtener más información, consulte Solucionar problemas de un registro de transacciones lleno.Refer to Troubleshoot a Full Transaction Log for more information.

Si el registro contiene varios archivos de registro físicos, el registro lógico pasará por todos los archivos de registro físicos antes de volver a empezar por el principio del primer archivo de registro físico.If the log contains multiple physical log files, the logical log will move through all the physical log files before it wraps back to the start of the first physical log file.

Importante

Para obtener más información sobre la administración de tamaño del registro de transacciones, vea Administrar el tamaño del archivo de registro de transacciones.For more information about transaction log size management, see Manage the Size of the Transaction Log File.

Truncamiento del registroLog Truncation

El truncamiento del registro es esencial para evitar que se llene.Log truncation is essential to keep the log from filling. El truncamiento del registro elimina los archivos de registro virtuales inactivos del registro de transacciones lógico de una base de datos de SQL ServerSQL Server , liberando espacio en el registro lógico para que lo reutilice el registro de transacciones físico.Log truncation deletes inactive virtual log files from the logical transaction log of a SQL ServerSQL Server database, freeing space in the logical log for reuse by the physical transaction log. Si no se truncara nunca un registro de transacciones, acabaría ocupando todo el espacio de disco asignado a sus archivo de registro físicos.If a transaction log were never truncated, it would eventually fill all the disk space that is allocated to its physical log files. Sin embargo, para que se pueda truncar el registro, se debe realizar primero una operación de punto de comprobación.However, before the log can be truncated, a checkpoint operation must occur. Un punto de comprobación escribe en el disco las páginas modificadas en memoria actuales (denominadas páginas desfasadas) y la información del registro de transacciones de la memoria.A checkpoint writes the current in-memory modified pages (known as dirty pages) and transaction log information from memory to disk. Cuando se lleva a cabo el punto de comprobación, la parte inactiva del registro de transacciones se marca como reutilizable.When the checkpoint is performed, the inactive portion of the transaction log is marked as reusable. A partir de ese momento, se puede liberar la parte inactiva mediante el truncamiento del registro.Thereafter, the inactive portion can be freed by log truncation. Para obtener más información sobre los puntos de comprobación, consulte Puntos de comprobación de base de datos (SQL Server).For more information about checkpoints, see Database Checkpoints (SQL Server).

En la siguiente ilustración se muestra un registro de transacciones antes y después del truncamiento.The following illustrations show a transaction log before and after truncation. En la primera ilustración se muestra un registro de transacciones que no se ha truncado nunca.The first illustration shows a transaction log that has never been truncated. El registro lógico tiene actualmente cuatro archivos de registro virtuales en uso.Currently, four virtual log files are in use by the logical log. El registro lógico comienza al principio del primer archivo de registro virtual y finaliza en el registro virtual 4.The logical log starts at the front of the first virtual log file and ends at virtual log 4. El registro de MinLSN está en el registro virtual 3.The MinLSN record is in virtual log 3. Los registros virtuales 1 y 2 solo contienen entradas de registro inactivas.Virtual log 1 and virtual log 2 contain only inactive log records. Estas entradas pueden truncarse.These records can be truncated. El registro virtual 5 no se utiliza aún y no forma parte del registro lógico actual.Virtual log 5 is still unused and is not part of the current logical log.

tranlog2

En la segunda ilustración se muestra el registro después del truncamiento.The second illustration shows how the log appears after being truncated. Se han liberado los registros virtuales 1 y 2 para su reutilización.Virtual log 1 and virtual log 2 have been freed for reuse. El registro lógico comienza ahora al principio del registro virtual 3.The logical log now starts at the beginning of virtual log 3. El registro virtual 5 no se utiliza aún y no forma parte del registro lógico actual.Virtual log 5 is still unused, and it is not part of the current logical log.

tranlog3

El truncamiento del registro se produce automáticamente después de los eventos siguientes, excepto cuando se retrasa por alguna razón:Log truncation occurs automatically after the following events, except when delayed for some reason:

  • En el modelo de recuperación simple, después de un punto de comprobación.Under the simple recovery model, after a checkpoint.
  • Bajo el modelo de recuperación completa o el modelo de recuperación optimizado para cargas masivas de registros, después de una copia de seguridad del registro, si un punto de comprobación ha producirse desde la copia de seguridad anterior.Under the full recovery model or bulk-logged recovery model, after a log backup, if a checkpoint has occurred since the previous backup.

El truncamiento del registro se puede retrasar por diferentes factores.Log truncation can be delayed by a variety of factors. En caso de un retraso largo en el truncamiento del registro, el registro de transacciones se puede llenar.In the event of a long delay in log truncation, the transaction log can fill up. Para obtener información, vea Factores que pueden ralentizar el truncamiento del registro y Solucionar problemas de un registro de transacciones lleno (Error 9002 de SQL Server).For information, see Factors that can delay log truncation and Troubleshoot a Full Transaction Log (SQL Server Error 9002).

Registro de transacciones de escritura anticipadaWrite-Ahead Transaction Log

En esta sección se describe el rol que desempeña el registro de transacciones de escritura anticipada en la grabación de modificaciones de datos en disco.This section describes the role of the write-ahead transaction log in recording data modifications to disk. SQL ServerSQL Server usa un algoritmo de registro de escritura previa (WAL), lo cual garantiza que no se escriba ninguna modificación de datos en el disco antes de escribir en él la entrada de registro asociada.uses a write-ahead logging (WAL) algorithm, which guarantees that no data modifications are written to disk before the associated log record is written to disk. Así se mantienen las propiedades ACID para una transacción.This maintains the ACID properties for a transaction.

Para entender cómo funciona el registro de escritura anticipada, es importante saber cómo se escriben los datos modificados en el disco.To understand how the write-ahead log works, it is important for you to know how modified data is written to disk. SQL ServerSQL Server mantiene una caché del búfer que lee las páginas de datos cuando estos deben recuperarse.maintains a buffer cache into which it reads data pages when data must be retrieved. Cuando se modifica una página en la caché del búfer, no se vuelve a escribir inmediatamente en el disco; en su lugar, la página se marca como desfasada.When a page is modified in the buffer cache, it is not immediately written back to disk; instead, the page is marked as dirty. Una página de datos puede tener más de una escritura lógica antes de que se escriba físicamente en el disco.A data page can have more than one logical write made before it is physically written to disk. Para cada escritura lógica, se inserta una entrada del registro de transacciones en la caché del registro que registra la modificación.For each logical write, a transaction log record is inserted in the log cache that records the modification. Las entradas del registro se tienen que escribir en el disco antes de que la página desfasada asociada se quite de la caché del búfer y se escriba en el disco.The log records must be written to disk before the associated dirty page is removed from the buffer cache and written to disk. El proceso de punto de comprobación examina periódicamente la caché del búfer en busca de búferes con páginas de una base de datos especificada y escribe todas las páginas desfasadas en el disco.The checkpoint process periodically scans the buffer cache for buffers with pages from a specified database and writes all dirty pages to disk. Los puntos de comprobación permiten ahorrar tiempo en una recuperación posterior al crear un punto en el que se garantiza que todas las páginas desfasadas se hayan escrito en el disco.Checkpoints save time during a later recovery by creating a point at which all dirty pages are guaranteed to have been written to disk.

A la escritura en el disco de una página de datos modificada desde la caché del búfer se le llama vaciar la página.Writing a modified data page from the buffer cache to disk is called flushing the page. SQL ServerSQL Server tiene una lógica que evita que una página desfasada se vacíe antes de que se escriba la entrada del registro asociada.has logic that prevents a dirty page from being flushed before the associated log record is written. Las entradas de registro se escriben en el disco cuando se vacían los búferes de registro.Log records are written to disk when the log buffers are flushed. Esto ocurre siempre que se confirma una transacción o se llenan los búferes de registro.This happens whenever a transaction commits or the log buffers become full.

Copias de seguridad de registros de transaccionesTransaction Log Backups

En esta sección se presentan conceptos acerca de cómo realizar copias de seguridad y restaurar (aplicar) registros de transacciones.This section presents concepts about how to back up and restore (apply) transaction logs. En los modelos de recuperación completa y de recuperación optimizada para cargas masivas de registros, es necesario realizar copias de seguridad periódicas de los registros de transacciones (copias de seguridad de registros) para recuperar datos.Under the full and bulk-logged recovery models, taking routine backups of transaction logs (log backups) is necessary for recovering data. Puede realizar una copia de seguridad del registro mientras se está ejecutando cualquier copia de seguridad completa.You can back up the log while any full backup is running. Para obtener más información sobre modelos de recuperación, consulte Realizar copias de seguridad y restaurar bases de datos de SQL Server.For more information about recovery models, see Back Up and Restore of SQL Server Databases.

Antes de crear la primera copia de seguridad de registros, debe crear una copia de seguridad completa, como una copia de seguridad de la base de datos o la primera de un conjunto completo de copias de seguridad de archivos.Before you can create the first log backup, you must create a full backup, such as a database backup or the first in a set of file backups. La restauración de una base de datos utilizando únicamente copias de seguridad de archivos puede llegar a ser un proceso complejo.Restoring a database by using only file backups can become complex. Por lo tanto, es recomendable que comience con una copia de seguridad de la base de datos completa si es posible.Therefore, we recommend that you start with a full database backup when you can. Posteriormente, será necesario realizar copias de seguridad del registro de transacciones con regularidad.Thereafter, backing up the transaction log regularly is necessary. De esta forma, no solo se minimiza el riesgo de pérdida de trabajo, sino que también se permite el truncamiento del registro de transacciones.This not only minimizes work-loss exposure but also enables truncation of the transaction log. Normalmente, el registro de transacciones se trunca tras cada copia de seguridad de registros convencional.Typically, the transaction log is truncated after every conventional log backup.

Importante

Es aconsejable realizar copias de seguridad de registros suficientemente regulares para ajustarse a los requisitos de su empresa, específicamente a la tolerancia a la pérdida de trabajo que un almacenamiento de registro dañada podría provocar.We recommend taking frequent enough log backups to support your business requirements, specifically your tolerance for work loss such as might be caused by a damaged log storage. La frecuencia adecuada para realizar copias de seguridad de registros varía en función de la tolerancia al riesgo de pérdida de trabajo y, por otra parte, de la cantidad de copias de seguridad de registros que puede almacenar, administrar y, potencialmente, restaurar.The appropriate frequency for taking log backups depends on your tolerance for work-loss exposure balanced by how many log backups you can store, manage, and, potentially, restore. Tenga en cuenta los RTO y RPO necesarios al implementar la estrategia de recuperación, específicamente el ritmo de realización de copias de seguridad de registros.Think about the required RTO and RPO when implementing your recovery strategy, and specifically the log backup cadence. Una copia de seguridad de registros cada 15 ó 30 minutos puede ser suficiente.Taking a log backup every 15 to 30 minutes might be enough. Si su empresa necesita minimizar el riesgo de pérdida de trabajo, piense en la posibilidad de realizar copias de seguridad de registros más frecuentemente.If your business requires that you minimize work-loss exposure, consider taking log backups more frequently. La existencia de copias de seguridad más frecuentes de los registros tiene la ventaja añadida de aumentar la frecuencia de truncamiento del registro, lo que genera archivos de registro menores.More frequent log backups have the added advantage of increasing the frequency of log truncation, resulting in smaller log files.

Importante

Para limitar el número de copias de seguridad del registro que necesita restaurar, es esencial que realice una copia de seguridad de sus datos periódicamente.To limit the number of log backups that you need to restore, it is essential to routinely back up your data. Por ejemplo, podría programar una copia de seguridad completa de la base de datos cada semana y copias de seguridad diferenciales de la base de datos a diario.For example, you might schedule a weekly full database backup and daily differential database backups.
Una vez más, tenga en cuenta los RTO y RPO necesarios al implementar la estrategia de recuperación, específicamente el ritmo de realización de copias de seguridad de base de datos completas y diferenciales.Again, think about the required RTO and RPO when implementing your recovery strategy, and specifically the full and differential database backup cadence.

Para obtener más información sobre las copias de seguridad del registro de transacciones, vea Copias de seguridad del registro de transacciones (SQL Server).For more information about transaction log backups, see Transaction Log Backups (SQL Server).

La cadena de registrosThe Log Chain

Una secuencia continua de copias de seguridad de registros se denomina cadena de registros.A continuous sequence of log backups is called a log chain. Una cadena de registros empieza con una copia de seguridad completa de la base de datos.A log chain starts with a full backup of the database. Normalmente, una cadena de registro nueva solo empieza cuando se realiza la primera copia de seguridad de la base de datos o después de que se cambie del modelo de recuperación simple al modelo de recuperación completa o al modelo de recuperación optimizado para cargas masivas de registros.Usually, a new log chain is only started when the database is backed up for the first time or after the recovery model is switched from simple recovery to full or bulk-logged recovery. A menos que se elija sobrescribir los conjuntos de copia de seguridad existentes al crear una copia de seguridad completa de la base de datos, la cadena de registros existente permanece intacta.Unless you choose to overwrite existing backup sets when creating a full database backup, the existing log chain remains intact. Con la cadena de registros intacta, se puede restaurar la base de datos a partir de cualquier copia de seguridad completa de la base de datos del conjunto de medios, seguida de todas las copias de seguridad de los registros subsiguientes hasta el punto de recuperación.With the log chain intact, you can restore your database from any full database backup in the media set, followed by all subsequent log backups up through your recovery point. El punto de recuperación puede ser el final de la última copia de seguridad de registros o un punto de recuperación concreto de cualquiera de las copias de seguridad de registros.The recovery point could be the end of the last log backup or a specific recovery point in any of the log backups. Para obtener más información, consulte Copias de seguridad de registros de transacciones (SQL Server).For more information, see Transaction Log Backups (SQL Server).

Para restaurar una base de datos al momento del error, es preciso que la cadena de registros esté intacta.To restore a database up to the point of failure, the log chain must be intact. De esta forma, es necesario que una secuencia ininterrumpida de las copias de seguridad del registro de transacciones se extienda hasta el momento del error.That is, an unbroken sequence of transaction log backups must extend up to the point of failure. El lugar en el que esta secuencia de registros debe comenzar depende del tipo de copias de seguridad de datos que esté restaurando: de base de datos, parcial o de archivos.Where this sequence of log must start depends on the type of data backups you are restoring: database, partial, or file. En las copias de seguridad de base de datos o parciales, la secuencia de copias de seguridad de registros debe extenderse desde el final de la copia de seguridad de base de datos o parcial.For a database or partial backup, the sequence of log backups must extend from the end of a database or partial backup. En un conjunto de copia de seguridad de archivos, la secuencia de copias de seguridad de registros debe comenzar desde el principio del conjunto completo de copias de seguridad de archivos.For a set of file backups, the sequence of log backups must extend from the start of a full set of file backups. Para obtener más información, vea Aplicar copias de seguridad del registro de transacciones (SQL Server).For more information, see Apply Transaction Log Backups (SQL Server).

Restaurar copias de seguridad de registrosRestore Log Backups

Al restaurar una copia de seguridad de registros se ponen al día los cambios que se registraron en el registro de transacciones para volver a crear el estado exacto de la base de datos en el momento en que se inició la operación de copia de seguridad de registros.Restoring a log backup rolls forward the changes that were recorded in the transaction log to re-create the exact state of the database at the time the log backup operation started. Al restaurar una base de datos, será necesario restaurar las copias de seguridad de registros creadas tras la copia de seguridad de la base de datos completa que esté restaurando o al principio de la primera copia de seguridad de archivos que esté restaurando.When you restore a database, you will have to restore the log backups that were created after the full database backup that you restore, or from the start of the first file backup that you restore. Normalmente, se debe restaurar una serie de copias de seguridad de registros hasta llegar al punto de recuperación después de haber restaurado la copia de seguridad de los datos o la copia de seguridad diferencial más recientes.Typically, after you restore the most recent data or differential backup, you must restore a series of log backups until you reach your recovery point. A continuación, se realiza la recuperación de la base de datos.Then, you recover the database. De esta manera, todas las transacciones que estaban incompletas cuando comenzó la recuperación se revertirán y la base de datos se conectará.This rolls back all transactions that were incomplete when the recovery started and brings the database online. Una vez recuperada la base de datos, ya no es posible restaurar más copias de seguridad.After the database has been recovered, you cannot restore any more backups. Para obtener más información, consulte Aplicar copias de seguridad del registro de transacciones (SQL Server).For more information, see Apply Transaction Log Backups (SQL Server).

Puntos de comprobación y la parte activa del registroCheckpoints and the Active Portion of the Log

Los puntos de comprobación vacían las páginas de datos desfasadas en la memoria caché del búfer de la base de datos actual en el disco.Checkpoints flush dirty data pages from the buffer cache of the current database to disk. De este modo, se minimiza la parte activa del registro que se debe procesar durante una recuperación completa de una base de datos.This minimizes the active portion of the log that must be processed during a full recovery of a database. Durante una recuperación completa, se llevan a cabo los siguientes tipos de acciones:During a full recovery, the following types of actions are performed:

  • Se ponen al día los registros de modificaciones que no se vaciaron en el disco antes de detenerse el sistema.The log records of modifications not flushed to disk before the system stopped are rolled forward.
  • Se revierten todas las modificaciones asociadas a transacciones incompletas, como las transacciones para las que no hay entradas COMMIT o ROLLBACK en el registro.All modifications associated with incomplete transactions, such as transactions for which there is no COMMIT or ROLLBACK log record, are rolled back.

Funcionamiento de los puntos de comprobaciónCheckpoint Operation

Un punto de comprobación realiza los procesos siguientes en la base de datos:A checkpoint performs the following processes in the database:

  • Escribe en el archivo de registro una entrada que marca el inicio del punto de comprobación.Writes a record to the log file, marking the start of the checkpoint.

  • Guarda información registrada para el punto de comprobación en una cadena de entradas de registro de puntos de comprobación.Stores information recorded for the checkpoint in a chain of checkpoint log records.

    Una parte de la información registrada en el punto de comprobación es el número de flujo de registro (LSN) de la primera entrada del registro que debe estar presente para una reversión correcta de toda la base de datos.One piece of information recorded in the checkpoint is the log sequence number (LSN) of the first log record that must be present for a successful database-wide rollback. Este LSN se denomina LSN de recuperación mínimo (MinLSN).This LSN is called the Minimum Recovery LSN (MinLSN). El MinLSN es el mínimo de:The MinLSN is the minimum of the:

    • El LSN del inicio del punto de comprobaciónLSN of the start of the checkpoint.
    • El LSN del inicio de la transacción activa más antiguaLSN of the start of the oldest active transaction.
    • El LSN del inicio de la transacción de replicación más antigua que aún no se ha entregado a la base de datos de distribuciónLSN of the start of the oldest replication transaction that has not yet been delivered to the distribution database.

    Los registros del punto de comprobación también contienen una lista de las transacciones activas que han modificado la base de datos.The checkpoint records also contain a list of all the active transactions that have modified the database.

  • Si la base de datos utiliza el modelo de recuperación simple, marca para su reutilización el espacio que se encuentra antes del MinLSN.If the database uses the simple recovery model, marks for reuse the space that precedes the MinLSN.

  • Escribe en el disco todas las páginas de datos y de registro desfasadas.Writes all dirty log and data pages to disk.

  • Escribe en el archivo de registro un registro que marca el final del punto de comprobación.Writes a record marking the end of the checkpoint to the log file.

  • Escribe el LSN del inicio de esta cadena en la página de arranque de la base de datos.Writes the LSN of the start of this chain to the database boot page.

Actividades que provocan un punto de comprobaciónActivities that cause a Checkpoint

Los puntos de comprobación pueden darse en las situaciones siguientes:Checkpoints occur in the following situations:

  • Se ejecuta explícitamente una instrucción CHECKPOINT.A CHECKPOINT statement is explicitly executed. Se produce un punto de comprobación en la base de datos actual para la conexión.A checkpoint occurs in the current database for the connection.
  • Se realiza una operación registrada al mínimo en la base de datos; por ejemplo, se realiza una operación de copia masiva en una base de datos que utiliza el modelo de recuperación optimizado para cargas masivas de registros.A minimally logged operation is performed in the database; for example, a bulk-copy operation is performed on a database that is using the Bulk-Logged recovery model.
  • Se han agregado o eliminado archivos de base de datos mediante ALTER DATABASE.Database files have been added or removed by using ALTER DATABASE.
  • Se detiene una instancia de SQL Server mediante una instrucción SHUTDOWN o deteniendo el servicio SQL Server (MSSQLSERVER).An instance of SQL Server is stopped by a SHUTDOWN statement or by stopping the SQL Server (MSSQLSERVER) service. Las dos acciones insertan un punto de comprobación en cada base de datos de la instancia de SQL Server.Either action causes a checkpoint in each database in the instance of SQL Server.
  • Una instancia de SQL Server genera periódicamente puntos de comprobación de manera automática en cada base de datos para reducir el tiempo que tardará la instancia en recuperar la base de datos.An instance of SQL Server periodically generates automatic checkpoints in each database to reduce the time that the instance would take to recover the database.
  • Se realiza una copia de seguridad de la base de datos.A database backup is taken.
  • Se realiza una actividad que requiere cerrar la base de datos.An activity requiring a database shutdown is performed. Por ejemplo, el valor de AUTO_CLOSE es ON y se ha cerrado la última conexión de usuario a la base de datos, o bien se realiza una modificación de una opción de la base de datos que requiere reiniciarla.For example, AUTO_CLOSE is ON and the last user connection to the database is closed, or a database option change is made that requires a restart of the database.

Puntos de comprobación automáticosAutomatic Checkpoints

El motor de base de datos de SQL Server genera puntos de comprobación automáticos.The SQL Server Database Engine generates automatic checkpoints. El intervalo entre puntos de comprobación automáticos se basa en el espacio del registro utilizado y en el tiempo transcurrido desde el último punto de comprobación.The interval between automatic checkpoints is based on the amount of log space used and the time elapsed since the last checkpoint. El intervalo de tiempo entre los puntos de comprobación automáticos puede ser muy variable y largo si se realizan pocas modificaciones en la base de datos.The time interval between automatic checkpoints can be highly variable and long, if few modifications are made in the database. Los puntos de comprobación automáticos también se pueden producir con frecuencia si se modifican muchos datos.Automatic checkpoints can also occur frequently if lots of data is modified.

El intervalo entre puntos de comprobación automáticos para todas las bases de datos de una instancia de servidor se calcula a partir de la opción de configuración del servidor recovery interval .Use the recovery interval server configuration option to calculate the interval between automatic checkpoints for all the databases on a server instance. Esta opción especifica el máximo de tiempo que el motor de base de datos debe usar para recuperar una base de datos al reiniciar el sistema.This option specifies the maximum time the Database Engine should use to recover a database during a system restart. El motor de base de datos calcula cuántas entradas de registro puede procesar en el intervalo de recuperación durante una operación de recuperación.The Database Engine estimates how many log records it can process in the recovery interval during a recovery operation.

El intervalo entre los puntos de comprobación automáticos depende también del modelo de recuperación:The interval between automatic checkpoints also depends on the recovery model:

  • Si la base de datos usa el modelo de recuperación completa o el modelo de recuperación optimizado para cargas masivas de registros, se generará un punto de comprobación automático cuando el número de entradas del registro alcance el número que el motor de base de datos estima que puede procesar durante el tiempo especificado en la opción de intervalo de recuperación.If the database is using either the full or bulk-logged recovery model, an automatic checkpoint is generated whenever the number of log records reaches the number the Database Engine estimates it can process during the time specified in the recovery interval option.

  • Si la base de datos utiliza el modelo de recuperación simple, se generará un punto de comprobación automático cuando el número de registros alcance el menor de estos dos valores:If the database is using the simple recovery model, an automatic checkpoint is generated whenever the number of log records reaches the lesser of these two values:

    • El registro está ocupado en un 70 por ciento.The log becomes 70 percent full.
    • El número de entradas de registro alcanza el número que el motor de base de datos calcula que puede procesar en el periodo especificado en la opción de intervalo de recuperación.The number of log records reaches the number the Database Engine estimates it can process during the time specified in the recovery interval option.

Para más información sobre la configuración del intervalo de recuperación, consulte Establecer la opción de configuración del servidor Intervalo de recuperación.For information about setting the recovery interval, see Configure the recovery interval Server Configuration Option.

Sugerencia

La opción de configuración avanzada -k de SQL Server permite a un administrador de base de datos limitar el comportamiento de E/S de los puntos de comprobación según el rendimiento de E/S para algunos tipos de puntos de comprobación.The -k SQL Server advanced setup option enables a database administrator to throttle checkpoint I/O behavior based on the throughput of the I/O subsystem for some types of checkpoints. La opción de configuración -k es válida para los puntos de comprobación y para cualquier punto de comprobación sin limitar.The -k setup option applies to automatic checkpoints and any otherwise unthrottled checkpoints.

Los puntos de comprobación automáticos truncan la parte no utilizada del registro de transacciones si la base de datos utiliza el modelo de recuperación simple.Automatic checkpoints truncate the unused section of the transaction log if the database is using the simple recovery model. No obstante, el registro no se trunca mediante puntos de comprobación automáticos si la base de datos utiliza el modelo de recuperación completa o el modelo optimizado para cargas masivas de registros.However, if the database is using the full or bulk-logged recovery models, the log is not truncated by automatic checkpoints. Para obtener más información, consulte El registro de transacciones.For more information, see The Transaction Log.

Ahora la instrucción CHECKPOINT ofrece un argumento checkpoint_duration opcional que especifica en segundos el tiempo necesario para que finalicen los puntos de comprobación.The CHECKPOINT statement now provides an optional checkpoint_duration argument that specifies the requested period of time, in seconds, for checkpoints to finish. Para obtener más información, consulte CHECKPOINT.For more information, see CHECKPOINT.

registro activoActive Log

La parte del archivo de registro desde el MinLSN hasta el último registro escrito se denomina parte activa del registro o registro activo.The section of the log file from the MinLSN to the last-written log record is called the active portion of the log, or the active log. Se trata de la sección del registro necesaria para una recuperación completa de la base de datos.This is the section of the log required to do a full recovery of the database. No se puede truncar ninguna parte del registro activo.No part of the active log can ever be truncated. Los truncamientos del registro se deben realizar en las partes del registro anteriores al MinLSN.All log records must be truncated from the parts of the log before the MinLSN.

En la ilustración siguiente se muestra una versión simplificada del final de un registro de transacciones con dos transacciones activas.The following illustration shows a simplified version of the end-of-a-transaction log with two active transactions. Los registros de punto de comprobación se han compactado en un solo registro.Checkpoint records have been compacted to a single record.

active_log

LSN 148 es la última entrada del registro de transacciones.LSN 148 is the last record in the transaction log. En el momento en que se procesó el registro del punto de comprobación en LSN 147, Tran 1 se había confirmado y Tran 2 era la única transacción activa.At the time that the recorded checkpoint at LSN 147 was processed, Tran 1 had been committed and Tran 2 was the only active transaction. Esto hace que la primera entrada del registro para Tran 2 sea la entrada de transacción activa más antigua del registro en el momento del último punto de comprobación.That makes the first log record for Tran 2 the oldest log record for a transaction active at the time of the last checkpoint. Esto convierte al registro de inicio del registro de transacciones para Tran 2, LSN 142, en el MinLSN.This makes LSN 142, the Begin transaction record for Tran 2, the MinLSN.

Transacciones de larga ejecuciónLong-Running Transactions

El registro activo debe incluir cada una de las partes de todas las transacciones no confirmadas.The active log must include every part of all uncommitted transactions. Una aplicación que inicia una transacción y no la confirma o la revierte impide que el motor de base de datos avance hacia el valor de MinLSN.An application that starts a transaction and does not commit it or roll it back prevents the Database Engine from advancing the MinLSN. Esto puede causar dos tipos de problemas:This can cause two types of problems:

  • Si se cierra el sistema después de que la transacción haya realizado un gran número de modificaciones no confirmadas, la fase de recuperación del siguiente reinicio puede durar bastante más que el tiempo especificado en la opción recovery interval .If the system is shut down after the transaction has performed many uncommitted modifications, the recovery phase of the subsequent restart can take much longer than the time specified in the recovery interval option.
  • Puede que el tamaño del registro aumente de forma considerable, porque no se puede truncar pasado el MinLSN.The log might grow very large, because the log cannot be truncated past the MinLSN. Esto ocurre incluso si la base de datos utiliza el modelo de recuperación simple, donde el registro de transacciones se suele truncar en cada punto de comprobación automático.This occurs even if the database is using the simple recovery model, in which the transaction log is generally truncated on each automatic checkpoint.

Transacciones de replicaciónReplication Transactions

El Agente de registro del LOG supervisa el registro de transacciones de cada base de datos configurada para la replicación transaccional y copia las transacciones marcadas para la replicación desde el registro de transacciones a la base de datos de distribución.The Log Reader Agent monitors the transaction log of each database configured for transactional replication, and it copies the transactions marked for replication from the transaction log into the distribution database. El registro activo debe contener todas las transacciones marcadas para la replicación, pero que aún no se han entregado a la base de datos de distribución.The active log must contain all transactions that are marked for replication, but that have not yet been delivered to the distribution database. Si estas transacciones no se replican puntualmente, pueden evitar el truncamiento del registro.If these transactions are not replicated in a timely manner, they can prevent the truncation of the log. Para obtener más información, consulte Replicación transaccional.For more information, see Transactional Replication.

Vea tambiénSee also

Se recomiendan los artículos y libros siguientes para obtener información adicional sobre el registro de transacciones y las prácticas recomendadas de administración de registros.We recommend the following articles and books for additional information about the transaction log and log management best practices.

El registro de transacciones (SQL Server) The Transaction Log (SQL Server)
Administrar el tamaño del archivo de registro de transacciones Manage the size of the transaction log file
Copias de seguridad del registro de transacciones (SQL Server) Transaction Log Backups (SQL Server)
Puntos de comprobación de base de datos (SQL Server) Database Checkpoints (SQL Server)
Establecer la opción de configuración del servidor Intervalo de recuperación Configure the recovery interval Server Configuration Option
sys.dm_db_log_info (Transact-SQL) sys.dm_db_log_info (Transact-SQL)
sys.dm_db_log_space_usage (Transact-SQL) sys.dm_db_log_space_usage (Transact-SQL)
Descripción del registro y la recuperación en SQL Server por Paul Randal Understanding Logging and Recovery in SQL Server by Paul Randal
Administración de registros de transacciones de SQL Server por Tony Davis y Gail ShawSQL Server Transaction Log Management by Tony Davis and Gail Shaw