Guida sull'architettura e gestione del log delle transazioni di SQL ServerSQL Server Transaction Log Architecture and Management Guide

In questo argomento si applica a: SìSQL ServerSìDatabase SQL di AzureSìAzure SQL Data Warehouse Sì Parallel Data WarehouseTHIS TOPIC APPLIES TO: yesSQL ServeryesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

In ogni database di SQL ServerSQL Server è incluso un log delle transazioni in cui vengono registrate tutte le transazioni e le modifiche apportate dalle transazioni stesse al database.Every SQL ServerSQL Server database has a transaction log that records all transactions and the database modifications that are made by each transaction. Il log delle transazioni è un componente fondamentale del database e, in caso di errore di sistema, può essere necessario per ripristinare la coerenza del database.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. In questa guida vengono fornite informazioni sull'architettura fisica e logica del log delle transazioni.This guide provides information about the physical and logical architecture of the transaction log. Le informazioni sull'architettura consentono di gestire più efficacemente i log delle transazioni.Understanding the architecture can improve your effectiveness in managing transaction logs.

Architettura logica del log delle transazioniTransaction Log Logical Architecture

Il log delle transazioni di SQL ServerSQL Server funziona in modo logico come se si trattasse di una stringa di record di log.The SQL ServerSQL Server transaction log operates logically as if the transaction log is a string of log records. Ogni record di log è identificato da un numero di sequenza del file di log (LSN).Each log record is identified by a log sequence number (LSN). Ogni nuovo record di log viene scritto nell'estremità finale logica del log con un LSN maggiore di quello del record precedente.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. I record di log vengono archiviati in sequenza man mano che vengono creati.Log records are stored in a serial sequence as they are created. Ogni record di log contiene l'ID della transazione a cui appartiene.Each log record contains the ID of the transaction that it belongs to. Tutti i record di log associati a ogni transazione sono collegati singolarmente in una catena tramite puntatori ai record precedenti che consentono di eseguire più rapidamente il rollback della transazione.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.

Nei record di log relativi alle modifiche dei dati vengono registrate le operazioni logiche eseguite oppure immagini dei dati precedenti e successive alla modifica.Log records for data modifications record either the logical operation performed or they record the before and after images of the modified data. Un'immagine precedente la modifica è una copia dei dati eseguita prima dell'operazione, mentre un'immagine successiva alla modifica è una copia dei dati eseguita dopo l'operazione.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.

La procedura necessaria per recuperare un'operazione varia a seconda del tipo di record di log:The steps to recover an operation depend on the type of log record:

  • Operazione logica registrataLogical operation logged

    • Per eseguire il rollforward dell'operazione logica, questa viene eseguita nuovamente.To roll the logical operation forward, the operation is performed again.

    • Per eseguire il rollback dell'operazione logica, questa viene eseguita al contrario.To roll the logical operation back, the reverse logical operation is performed.

  • Immagine precedente e successiva (alla modifica dei dati) registrataBefore and after image logged

    • Per eseguire il rollforward dell'operazione, viene applicata l'immagine successiva.To roll the operation forward, the after image is applied.

    • Per eseguire il rollback dell'operazione, viene applicata l'immagine precedente.To roll the operation back, the before image is applied.

    Nel log delle transazioni vengono registrati molti tipi di operazioni,Many types of operations are recorded in the transaction log. tra cui:These operations include:

  • L'inizio e la fine di ogni transazione.The start and end of each transaction.

  • Tutte le modifiche apportate ai dati, ovvero inserimento, aggiornamento o eliminazione,Every data modification (insert, update, or delete). comprese le modifiche apportate da stored procedure di sistema o istruzioni DDL (Data Definition Language) a qualsiasi tabella, incluse le tabelle di sistema.This includes changes by system stored procedures or data definition language (DDL) statements to any table, including system tables.

  • Tutte le allocazioni o deallocazioni di pagina e di extent.Every extent and page allocation or deallocation.

  • Operazioni di creazione o eliminazione di una tabella o di un indice.Creating or dropping a table or index.

    Nel log vengono registrate anche le operazioni di rollback.Rollback operations are also logged. Ogni transazione riserva una determinata quantità di spazio nel log delle transazioni per garantire che nel log sia disponibile spazio sufficiente per supportare un rollback causato da un'istruzione di rollback esplicita o dal verificarsi di un errore.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 quantità di spazio riservata varia in base alle operazioni eseguite nella transazione, ma in genere equivale alla quantità di spazio utilizzata per registrare nel log ogni operazione.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. Lo spazio riservato viene liberato al completamento della transazione.This reserved space is freed when the transaction is completed.

    La sezione del file di log dal primo record di log che deve essere presente per garantire la corretta esecuzione del rollback a livello di database all'ultimo record di log scritto è definita la parte attiva del log o log attivo.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. Questa sezione del log è necessaria per il recupero completo del database.This is the section of the log required to a full recovery of the database. Non è possibile troncare nessuna parte del log attivo.No part of the active log can ever be truncated. Il numero di sequenza del file di log (LSN) di questo primo record di log è noto come LSN minimo del recupero (MinLSN).The log sequence number (LSN) of this first log record is known as the minimum recovery LSN (MinLSN).

Architettura fisica del log delle transazioniTransaction Log Physical Architecture

Del log delle transazioni di un database viene eseguito il mapping su uno o più file fisici.The transaction log in a database maps over one or more physical files. Concettualmente, il file di log è una stringa di record di log.Conceptually, the log file is a string of log records. Fisicamente, la sequenza di record di log viene archiviata in modo efficiente nel set di file fisici che implementano il log delle transazioni.Physically, the sequence of log records is stored efficiently in the set of physical files that implement the transaction log. È necessario che sia disponibile almeno un file di log per ogni database.There must be at least one log file for each database.

In Motore di database di SQL ServerSQL Server Database Engine ogni file di log fisico viene diviso internamente in diversi file di log virtuali.The Motore di database di SQL ServerSQL Server Database Engine divides each physical log file internally into a number of virtual log files (VLFs). I file di log virtuali non hanno dimensioni fisse e non è previsto un numero fisso di file di log virtuali per un file di log fisico.Virtual log files have no fixed size, and there is no fixed number of virtual log files for a physical log file. Il Motore di databaseDatabase Engine definisce dinamicamente le dimensioni dei file di log virtuali durante la creazione o l'estensione.The Motore di databaseDatabase Engine chooses the size of the virtual log files dynamically while it is creating or extending log files. Il Motore di databaseDatabase Engine tende a mantenere ridotto il numero di file virtuali.The Motore di databaseDatabase Engine tries to maintain a small number of virtual files. Le dimensioni dei file virtuali dopo l'estensione di un file di log corrispondono alla somma delle dimensioni del log esistente e del nuovo incremento del file.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. Le dimensioni o il numero di file di log virtuali non possono essere configurati o impostati dagli amministratori.The size or number of virtual log files cannot be configured or set by administrators.

Nota

La creazione di file di log virtuali avviene nel modo seguente:VLF creation follows this method:

  • Se il valore growth successivo è inferiore a 1/8 del valore size del log fisico attuale, creare 1 file di log virtuale che copra l'aumento delle dimensioni (a partire da SQL Server 2014SQL Server 2014)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 2014SQL Server 2014)
  • Se il valore growth è inferiore a 64 MB, creare 4 file di log virtuali che coprano l'aumento delle dimensioni (ad esempio con growth di 1 MB, creare quattro file virtuali di log da 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)
  • Se il valore growth è compreso tra 64 MB e 1 GB, creare 8 file di log virtuali che coprano l'aumento delle dimensioni (ad esempio con growth di 512 MB, creare otto file virtuali di log da 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)
  • Se il valore growth è superiore a 1 GB, creare 16 file di log virtuali che coprano l'aumento delle dimensioni (ad esempio con growth di 8 GB, creare sedici file virtuali di log da 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)

I file di log virtuali influenzano le prestazioni del sistema solo se i file di log fisici sono definiti da valori bassi di size e growth_increment .The only time virtual log files affect system performance is if the physical log files are defined by small size and growth_increment values. Il valore size indica le dimensioni iniziali del file di log mentre il valore growth_increment indica la quantità di spazio aggiunta al file ogni volta che è necessario dello spazio nuovo.The size value is the initial size for the log file and the growth_increment value is the amount of space added to the file every time new space is required. Se le dimensioni dei file di log aumentano in modo considerevole in seguito a una serie di piccoli incrementi, in essi verrà incluso un numero elevato di file di log virtuali.If the log files grow to a large size because of many small increments, they will have many virtual log files. Questo potrebbe provocare un rallentamento delle operazioni di avvio del database e di backup e ripristino del log.This can slow down database startup and also log backup and restore operations. È consigliabile assegnare ai file di log un valore size simile a quello delle dimensioni finali necessarie e un valore growth_increment relativamente alto.We recommend that you assign log files a size value close to the final size required, and also have a relatively large growth_increment value. Per altre informazioni su questi parametri, vedere Opzioni per file e filegroup ALTER DATABASE (Transact-SQL).For more information about these parameters, see ALTER DATABASE File and Filegroup Options (Transact-SQL).

Il log delle transazioni è un file circolare.The transaction log is a wrap-around file. Si consideri, ad esempio, un database con un file di log fisico diviso in quattro file di log virtuali.For example, consider a database with one physical log file divided into four virtual log files. Quando viene creato il database, il file di log logico comincia all'inizio del file di log fisico.When the database is created, the logical log file begins at the start of the physical log file. Vengono aggiunti nuovi record di log alla fine del log logico, che si espandono verso la fine del log fisico.New log records are added at the end of the logical log and expand toward the end of the physical log. Il troncamento del log libera tutti i log virtuali i cui record vengono visualizzati tutti davanti al numero minimo di sequenza del file di log (MinLSN, Minimum Log Sequence Number) per il recupero.Log truncation frees any virtual logs whose records all appear in front of the minimum recovery log sequence number (MinLSN). MinLSN è il numero di sequenza del file di log del record di log meno recente necessario per un corretto rollback a livello di database.The MinLSN is the log sequence number of the oldest log record that is required for a successful database-wide rollback. Il log delle transazioni del database di esempio sarebbe simile a quello illustrato nella figura seguente.The transaction log in the example database would look similar to the one in the following illustration.

tranlog3

Quando la fine del log logico raggiunge la fine del file di log fisico, i nuovi record di log vengono nuovamente inseriti a partire dall'inizio del file di log fisico.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

Questo ciclo viene ripetuto all'infinito, a condizione che la fine del log logico non raggiunga mai l'inizio del log stesso.This cycle repeats endlessly, as long as the end of the logical log never reaches the beginning of the logical log. Se i vecchi record di log vengono troncati abbastanza frequentemente in modo da lasciare sempre spazio sufficiente per i nuovi record di log creati fino al checkpoint successivo, il log non viene mai riempito completamente.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. Se, tuttavia, la fine del log logico raggiunge l'inizio del log stesso, può verificarsi uno dei due eventi indicati di seguito:However, if the end of the logical log does reach the start of the logical log, one of two things occurs:

  • Se è abilitata l'impostazione FILEGROWTH per il log e sul disco vi è spazio disponibile, il file viene esteso in base al valore specificato nel parametro growth_increment e i nuovi record di log vengono aggiunti all'estensione.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. Per altre informazioni sull'impostazione FILEGROWTH, vedere Opzioni per file e filegroup ALTER DATABASE (Transact-SQL).For more information about the FILEGROWTH setting, see ALTER DATABASE File and Filegroup Options (Transact-SQL).

  • Se l'impostazione FILEGROWTH non è attiva oppure se lo spazio libero sul disco in cui risiede il file di log è inferiore a quello specificato in growth_increment, viene generato un errore 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. Per altre informazioni, fare riferimento a Risolvere i problemi relativi a un log completo.Refer to Troubleshoot a Full Transaction Log for more information.

    Se il log include più file di log fisici, il log logico utilizzerà tutti i file di log fisici prima di tornare all'inizio del primo file di log fisico.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.

Troncamento del logLog Truncation

Il troncamento del log è essenziale per evitare il riempimento del log.Log truncation is essential to keep the log from filling. Il troncamento del log comporta l'eliminazione dei file di log virtuali inattivi dal log delle transazioni logico di un database di SQL ServerSQL Server , liberando spazio nel log logico per il riutilizzo da parte del log delle transazioni fisico.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. Se un log delle transazioni non viene mai troncato, è possibile che le sue dimensioni aumentino fino a occupare tutto lo spazio su disco allocato ai file di log fisici.If a transaction log were never truncated, it would eventually fill all the disk space that is allocated to its physical log files. Tuttavia, prima che sia possibile troncare il log, è necessario eseguire un'operazione su checkpoint.However, before the log can be truncated, a checkpoint operation must occur. Tramite un checkpoint vengono scritte le pagine modificate in memoria correnti, note come pagine dirty, e le informazioni sul log delle transazioni dalla memoria sul disco.A checkpoint writes the current in-memory modified pages (known as dirty pages) and transaction log information from memory to disk. Quando viene eseguito il checkpoint, la parte inattiva del log delle transazioni viene contrassegnata come riutilizzabile.When the checkpoint is performed, the inactive portion of the transaction log is marked as reusable. Successivamente, tale parte inattiva potrà essere liberata mediante il troncamento del log.Thereafter, the inactive portion can be freed by log truncation. Per altre informazioni sui checkpoint, vedere Checkpoint di database (SQL Server).For more information about checkpoints, see Database Checkpoints (SQL Server).

Nelle figure seguenti viene illustrato un log delle transazioni prima e dopo il troncamento.The following illustrations show a transaction log before and after truncation. Nella prima figura viene illustrato un log delle transazioni che non è mai stato troncato.The first illustration shows a transaction log that has never been truncated. Attualmente, il log logico utilizza quattro file di log virtuali.Currently, four virtual log files are in use by the logical log. Il log logico inizia prima del primo file di log virtuale e termina al log virtuale 4.The logical log starts at the front of the first virtual log file and ends at virtual log 4. Il record MinLSN si trova nel log virtuale 3.The MinLSN record is in virtual log 3. I log virtuali 1 e 2 contengono solo record di log inattivi.Virtual log 1 and virtual log 2 contain only inactive log records. Questi record possono essere troncati.These records can be truncated. Il log virtuale 5 è ancora inutilizzato e non fa parte del log logico corrente.Virtual log 5 is still unused and is not part of the current logical log.

tranlog2

Nella seconda figura è illustrata la struttura del log dopo il troncamento.The second illustration shows how the log appears after being truncated. I log virtuali 1 e 2 sono stati liberati per il riutilizzo.Virtual log 1 and virtual log 2 have been freed for reuse. Il log logico ora inizia all'inizio del log virtuale 3.The logical log now starts at the beginning of virtual log 3. Il log virtuale 5 è ancora inutilizzato e non fa parte del log logico corrente.Virtual log 5 is still unused, and it is not part of the current logical log.

tranlog3

A meno che non venga posticipato per qualche motivo, il troncamento del log viene effettuato automaticamente dopo gli eventi seguenti:Log truncation occurs automatically after the following events, except when delayed for some reason:

Log delle transazioni write-aheadWrite-Ahead Transaction Log

In questa sezione viene descritto il ruolo del log delle transazioni write-ahead nella registrazione delle modifiche dei dati sul disco.This section describes the role of the write-ahead transaction log in recording data modifications to disk. SQL ServerSQL Server usa un algoritmo di log write-ahead (WAL) che garantisce che le modifiche apportate ai dati non vengano scritte nel disco prima del record di log corrispondente. 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. In questo modo, è possibile mantenere le proprietà ACID per una transazione.This maintains the ACID properties for a transaction.

Per comprendere il funzionamento dei log write-ahead, è importante conoscere la modalità con cui i dati modificati vengono scritti sul 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 cache buffer in cui vengono lette le pagine di dati quando questi ultimi devono essere recuperati. maintains a buffer cache into which it reads data pages when data must be retrieved. Quando una pagina viene modificata nella cache buffer, non viene immediatamente riscritta nel disco, ma viene contrassegnata come dirty.When a page is modified in the buffer cache, it is not immediately written back to disk; instead, the page is marked as dirty. A una pagina di dati possono essere associate più scritture logiche prima di essere scritta fisicamente sul disco.A data page can have more than one logical write made before it is physically written to disk. Per ogni scrittura logica, viene inserito un record del log delle transazioni nella cache del log, per registrare la modifica.For each logical write, a transaction log record is inserted in the log cache that records the modification. I record di log devono essere scritti sul disco prima che la pagina dirty associata venga rimossa dalla cache buffer e scritta sul disco.The log records must be written to disk before the associated dirty page is removed from the buffer cache and written to disk. Tramite il processo di gestione dei checkpoint viene eseguita periodicamente l'analisi della cache buffer alla ricerca di buffer con pagine di un database specifico e tutte le pagine dirty vengono scritte nel disco.The checkpoint process periodically scans the buffer cache for buffers with pages from a specified database and writes all dirty pages to disk. I checkpoint consentono di risparmiare tempo durante un successivo recupero, grazie alla creazione di un punto in cui è certo che tutte le pagine dirty siano state scritte sul 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.

La scrittura di una pagina di dati modificata dalla cache buffer al disco viene definita scaricamento della pagina.Writing a modified data page from the buffer cache to disk is called flushing the page. SQL ServerSQL Server dispone della logica tramite cui viene impedito lo scaricamento di una pagina dirty prima della scrittura del record di log associato. has logic that prevents a dirty page from being flushed before the associated log record is written. I record di log vengono scritti su disco dopo che è stato eseguito il commit delle transazioni.Log records are written to disk when the transactions are committed.

Backup di log delle transazioniTransaction Log Backups

In questa sezione vengono introdotti concetti relativi al backup e al ripristino, vale a dire all'applicazione, di log delle transazioni.This section presents concepts about how to back up and restore (apply) transaction logs. In base ai modelli di recupero con registrazione completa e con registrazione minima delle operazioni bulk, per poter recuperare i dati è necessario eseguire backup di routine dei log delle transazioni (backup del log).Under the full and bulk-logged recovery models, taking routine backups of transaction logs (log backups) is necessary for recovering data. È possibile eseguire il backup del log mentre è in esecuzione un qualsiasi backup completo.You can back up the log while any full backup is running. Per altre informazioni sui modelli di recupero, vedere Backup e ripristino di database SQL Server.For more information about recovery models, see Back Up and Restore of SQL Server Databases.

Prima di creare il primo backup del log, è necessario creare un backup completo, ad esempio un backup del database oppure il primo di un set di backup di file.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. Il ripristino di un database solo tramite backup di file può essere un'operazione complessa.Restoring a database by using only file backups can become complex. Quando possibile, è pertanto consigliabile iniziare con un backup completo del database.Therefore, we recommend that you start with a full database backup when you can. Eseguire quindi regolarmente il backup del log delle transazioni.Thereafter, backing up the transaction log regularly is necessary. In questo modo, è possibile non solo limitare al minimo il rischio di perdita dei dati, ma anche abilitare il troncamento del log delle transazioni.This not only minimizes work-loss exposure but also enables truncation of the transaction log. In genere, il troncamento del log delle transazioni viene eseguito dopo ogni backup del log convenzionale,Typically, the transaction log is truncated after every conventional log backup.

È consigliabile eseguire backup del log sufficientemente frequenti da soddisfare i requisiti aziendali e, in particolare, il requisito inerente la tolleranza per eventuali perdite di dati, che potrebbero ad esempio verificarsi in seguito al danneggiamento dell'unità dei log.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 drive. La frequenza appropriata per l'esecuzione dei backup del log viene determinata in base al raggiungimento di un compromesso tra la tolleranza per il rischio di perdita dei dati e la quantità di backup del log che è possibile archiviare, gestire e potenzialmente ripristinare.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. Potrebbe essere sufficiente eseguire un backup del log ogni 15 - 30 minuti.Taking a log backup every 15 to 30 minutes might be enough. Se nella propria azienda è necessario limitare al minimo il rischio di perdita dei dati, valutare se eseguire i backup del log con una maggiore frequenza.If your business requires that you minimize work-loss exposure, consider taking log backups more frequently. L'esecuzione di backup del log più frequenti offre il vantaggio aggiuntivo di un aumento della frequenza del troncamento del log, con una conseguente riduzione delle dimensioni dei file di log.More frequent log backups have the added advantage of increasing the frequency of log truncation, resulting in smaller log files.

Per limitare il numero di backup dei log che è necessario ripristinare, è fondamentale eseguire regolarmente il backup dei dati.To limit the number of log backups that you need to restore, it is essential to routinely back up your data. Ad esempio, è possibile pianificare un backup completo del database una volta la settima e backup differenziali del database una volta al giorno.For example, you might schedule a weekly full database backup and daily differential database backups.

Catena di logThe Log Chain

Una sequenza continua di backup del log è denominata catena di log.A continuous sequence of log backups is called a log chain. Una catena di log ha inizio con un backup completo del database.A log chain starts with a full backup of the database. In genere, una nuova catena di log viene creata solo quando si esegue il backup del database per la prima volta oppure dopo il passaggio dal modello di recupero con registrazione minima al modello di recupero con registrazione completa o con registrazione minima delle operazioni bulk.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. Se si sceglie di non sovrascrivere i set di backup esistenti durante la creazione di un backup completo del database, la catena di log esistente rimane intatta.Unless you choose to overwrite existing backup sets when creating a full database backup, the existing log chain remains intact. Con la catena di log intatta, è possibile ripristinare il database da qualsiasi backup completo del database nel set di supporti, seguito da tutti i backup del log successivi tramite il punto di recupero specifico.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. Il punto di recupero può essere la fine dell'ultimo backup del log o un punto di recupero specifico in uno dei backup del log.The recovery point could be the end of the last log backup or a specific recovery point in any of the log backups. Per altre informazioni, vedere Backup di log delle transazioni (SQL Server).For more information, see Transaction Log Backups (SQL Server).

Per ripristinare un database al punto in cui si è verificato l'errore, è necessario che la catena di log sia intatta.To restore a database up to the point of failure, the log chain must be intact. In altre parole, è necessario che una sequenza non interrotta di backup del log delle transazioni si estenda fino al punto di errore.That is, an unbroken sequence of transaction log backups must extend up to the point of failure. Il punto in cui la sequenza del log deve iniziare dipende dal tipo di backup dei dati che si sta ripristinando, ovvero un backup del database, parziale o di file.Where this sequence of log must start depends on the type of data backups you are restoring: database, partial, or file. Nel caso di un backup del database o parziale, la sequenza di backup del log si deve estendere dalla fine di un backup del database o parziale.For a database or partial backup, the sequence of log backups must extend from the end of a database or partial backup. Nel caso di un set di backup di file, la sequenza di backup del log si deve estendere dall'inizio di un intero set di backup di file.For a set of file backups, the sequence of log backups must extend from the start of a full set of file backups. Per altre informazioni, vedere Applicare backup del log delle transazioni (SQL Server).For more information, see Apply Transaction Log Backups (SQL Server).

Ripristinare i backup di logRestore Log Backups

Il ripristino di un backup del log determina il rollforward delle modifiche registrate nel log delle transazioni in modo da ricreare l'esatto stato del database esistente all'inizio dell'operazione di backup del log.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. Quando si ripristina un database, è necessario ripristinare i backup del log creati dopo il backup completo del database ripristinato oppure dall'inizio del primo backup di file ripristinato.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. In genere, dopo il ripristino del backup dei dati o del backup differenziale più recente, è necessario ripristinare una serie di backup del log fino al punto di recupero desiderato.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. Recuperare quindi il database.Then, you recover the database. Verrà eseguito il rollback di tutte le transazioni incomplete nel momento in cui è iniziato il recupero e verrà attivata la modalità online per il database.This rolls back all transactions that were incomplete when the recovery started and brings the database online. Dopo il recupero del database, non è possibile ripristinare altri backup.After the database has been recovered, you cannot restore any more backups. Per altre informazioni, vedere Applicare backup del log delle transazioni (SQL Server).For more information, see Apply Transaction Log Backups (SQL Server).

Relazione tra i checkpoint e la parte attiva del logCheckpoints and the Active Portion of the Log

I checkpoint scaricano le pagine di dati dirty dalla cache buffer del database corrente al discoCheckpoints flush dirty data pages from the buffer cache of the current database to disk. riducendo al minimo la parte attiva del log da elaborare durante un recupero con registrazione completa del database,This minimizes the active portion of the log that must be processed during a full recovery of a database. durante il quale vengono eseguiti i tipi seguenti di azioni:During a full recovery, the following types of actions are performed:

  • Rollforward dei record di log relativi alle modifiche non scaricate su disco prima dell'arresto del sistema.The log records of modifications not flushed to disk before the system stopped are rolled forward.
  • Rollback di tutte le modifiche associate a transazioni incomplete, ad esempio transazioni per cui non esiste un record di log COMMIT o ROLLBACK.All modifications associated with incomplete transactions, such as transactions for which there is no COMMIT or ROLLBACK log record, are rolled back.

Funzionamento dei checkpointCheckpoint Operation

Un checkpoint esegue i processi seguenti nel database:A checkpoint performs the following processes in the database:

  • Scrive nel file di log un record che indica l'inizio del checkpoint.Writes a record to the log file, marking the start of the checkpoint.
  • Archivia le informazioni registrate per il checkpoint in una catena di record di log relativi al checkpoint.Stores information recorded for the checkpoint in a chain of checkpoint log records.

    Una delle informazioni registrate nel checkpoint è il numero di sequenza del file di log (LSN) del primo record che deve essere presente per poter eseguire correttamente il rollback a livello di database.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. Questo numero LSN è denominato LSN minimo del recupero (MinLSN).This LSN is called the Minimum Recovery LSN (MinLSN). Il numero MinLSN è il valore minimo tra:The MinLSN is the minimum of the:

    • Numero LSN dell'inizio del checkpoint.LSN of the start of the checkpoint.
    • Numero LSN dell'inizio della transazione attiva meno recente.LSN of the start of the oldest active transaction.
    • Numero LSN dell'inizio della transazione di replica meno recente non ancora recapitata al database di distribuzione.LSN of the start of the oldest replication transaction that has not yet been delivered to the distribution database.

      I record del checkpoint contengono inoltre un elenco di tutte le transazioni attive che hanno modificato il database.The checkpoint records also contain a list of all the active transactions that have modified the database.

  • Se il database utilizza il modello di recupero con registrazione minima, contrassegna per il riutilizzo lo spazio che precede il numero MinLSN.If the database uses the simple recovery model, marks for reuse the space that precedes the MinLSN.

  • Scrive sul disco tutte le pagine di log e di dati dirty.Writes all dirty log and data pages to disk.
  • Scrive nel file di log un record che indica la fine del checkpoint.Writes a record marking the end of the checkpoint to the log file.
  • Scrive il numero LSN corrispondente all'inizio della catena nella pagina di avvio del database.Writes the LSN of the start of this chain to the database boot page.

Attività che causano un checkpointActivities that cause a Checkpoint

I checkpoint vengono eseguiti nelle situazioni seguenti:Checkpoints occur in the following situations:

  • Viene eseguita esplicitamente un'istruzione CHECKPOINT.A CHECKPOINT statement is explicitly executed. Viene eseguito un checkpoint nel database corrente per la connessione.A checkpoint occurs in the current database for the connection.
  • Nel database viene eseguita un'operazione con registrazione minima, ad esempio viene eseguita un'operazione di copia bulk in un database che utilizza il modello di recupero con registrazione minima delle operazioni bulk.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.
  • Vengono aggiunti o rimossi file di database utilizzando l'istruzione ALTER DATABASE.Database files have been added or removed by using ALTER DATABASE.
  • Un'istanza di SQL Server viene arrestata da un'istruzione SHUTDOWN o dall'arresto del servizio SQL Server (MSSQLSERVER).An instance of SQL Server is stopped by a SHUTDOWN statement or by stopping the SQL Server (MSSQLSERVER) service. Entrambe le azioni causano un checkpoint in ogni database dell'istanza di SQL Server.Either action causes a checkpoint in each database in the instance of SQL Server.
  • Un'istanza di SQL Server genera periodicamente checkpoint automatici in ogni database per ridurre il tempo necessario all'istanza per il recupero del database.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.
  • Viene eseguito un backup del database.A database backup is taken.
  • Viene eseguita un'attività che richiede la chiusura di un database.An activity requiring a database shutdown is performed. Ad esempio, AUTO_CLOSE è impostata su ON e la connessione al database dell'ultimo utente viene chiusa, oppure viene eseguita una modifica a un'opzione di database che richiede un riavvio del database.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.

Checkpoint automaticiAutomatic Checkpoints

Il motore di database di SQL Server genera checkpoint automatici.The SQL Server Database Engine generates automatic checkpoints. L'intervallo fra i checkpoint automatici dipende dalla quantità di spazio di log utilizzata e dal tempo trascorso dall'ultimo checkpoint.The interval between automatic checkpoints is based on the amount of log space used and the time elapsed since the last checkpoint. Questo intervallo di tempo è estremamente variabile e se al database vengono apportate poche modifiche può essere molto lungo.The time interval between automatic checkpoints can be highly variable and long, if few modifications are made in the database. I checkpoint automatici possono anche essere eseguiti di frequente, se si modificano grandi quantità di dati.Automatic checkpoints can also occur frequently if lots of data is modified.

Usare l'opzione di configurazione del server dell' intervallo di recupero per calcolare l'intervallo tra i checkpoint automatici per tutti i database in un'istanza del server.Use the recovery interval server configuration option to calculate the interval between automatic checkpoints for all the databases on a server instance. Questa opzione specifica il periodo di tempo massimo che può essere usato dal motore di database per recuperare un database durante un riavvio del sistema.This option specifies the maximum time the Database Engine should use to recover a database during a system restart. Il motore di database stima il numero di record di log che è possibile elaborare nell' intervallo di recupero durante un'operazione di recupero.The Database Engine estimates how many log records it can process in the recovery interval during a recovery operation.

L'intervallo fra i checkpoint automatici dipende anche dal modello di recupero:The interval between automatic checkpoints also depends on the recovery model:

  • Se il database usa il modello di recupero con registrazione completa o con registrazione minima delle operazioni bulk, viene generato un checkpoint automatico ogni volta che il numero di record di log raggiunge il valore che il motore di database stima sia possibile elaborare nel periodo di tempo specificato dall'opzione dell'intervallo di recupero.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.
  • Se il database utilizza il modello di recupero con registrazione minima, viene generato un checkpoint automatico ogni volta che il numero di record di log raggiunge il minore tra i valori seguenti: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:

    • Il log viene riempito al 70%.The log becomes 70 percent full.
    • Il numero di record di log raggiunge il valore che il motore di database stima sia possibile elaborare nel periodo di tempo specificato dall'opzione dell'intervallo di recupero.The number of log records reaches the number the Database Engine estimates it can process during the time specified in the recovery interval option.

Per altre informazioni sull'impostazione dell'intervallo di recupero, vedere Configurare l'opzione di configurazione del server dell'intervallo di recupero.For information about setting the recovery interval, see Configure the recovery interval Server Configuration Option.

Suggerimento

L'opzione di impostazione avanzata -k di SQL Server consente all'amministratore del database di limitare il comportamento di I/O del checkpoint in base alla velocità effettiva del sottosistema di I/O per alcuni tipi di checkpoint.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. L'opzione di impostazione -k si applica ai checkpoint automatici e ai checkpoint senza limitazione.The -k setup option applies to automatic checkpoints and any otherwise unthrottled checkpoints.

I checkpoint automatici troncano la parte non utilizzata del log delle transazioni se il database utilizza il modello di recupero con registrazione minima,Automatic checkpoints truncate the unused section of the transaction log if the database is using the simple recovery model. ma non se il database utilizza il modello di recupero con registrazione completa o con registrazione minima delle operazioni bulk.However, if the database is using the full or bulk-logged recovery models, the log is not truncated by automatic checkpoints. Per altre informazioni, vedere Log delle transazioni (SQL Server).For more information, see The Transaction Log.

L'istruzione CHECKPOINT offre ora l'argomento facoltativo checkpoint_duration che specifica il tempo necessario in secondi per il completamento dei checkpoint.The CHECKPOINT statement now provides an optional checkpoint_duration argument that specifies the requested period of time, in seconds, for checkpoints to finish. Per altre informazioni, vedere CHECKPOINT (Transact-SQL).For more information, see CHECKPOINT.

log attivoActive Log

La parte del file di log compresa tra il numero MinLSN e l'ultimo record di log scritto viene definita parte attiva del log o log attivo.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. ed è necessaria per eseguire il recupero con registrazione completa del database.This is the section of the log required to do a full recovery of the database. Non è possibile troncare nessuna parte del log attivo.No part of the active log can ever be truncated. Tutti i record del log devono essere troncati dalle parti del log che precedono il numero MinLSN.All log records must be truncated from the parts of the log before the MinLSN.

Nella figura seguente viene illustrata una versione semplificata della parte finale di un log delle transazioni con due transazioni attive.The following illustration shows a simplified version of the end-of-a-transaction log with two active transactions. I record di checkpoint sono stati compattati in un unico record.Checkpoint records have been compacted to a single record.

active_log

LSN 148 è l'ultimo record del log delle transazioni.LSN 148 is the last record in the transaction log. Quando è stato elaborato il checkpoint registrato in corrispondenza del numero LSN 147, era stato eseguito il commit di Tran 1 e Tran 2 era l'unica transazione attiva.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. Pertanto, il primo record di log di Tran 2 è il meno recente di una transazione attiva al momento dell'ultimo checkpointThat makes the first log record for Tran 2 the oldest log record for a transaction active at the time of the last checkpoint. e, di conseguenza, il numero MinLSN corrisponde a LSN 142, ovvero al record di inizio della transazione Tran 2.This makes LSN 142, the Begin transaction record for Tran 2, the MinLSN.

Transazioni con esecuzione prolungataLong-Running Transactions

Il log attivo deve includere tutte le parti di tutte le transazioni di cui non è stato eseguito il commit.The active log must include every part of all uncommitted transactions. Se un'applicazione avvia una transazione e non ne esegue il commit o il rollback, il motore di database non fa aumentare il numero MinLSN.An application that starts a transaction and does not commit it or roll it back prevents the Database Engine from advancing the MinLSN. Ciò può causare due tipi di problemi:This can cause two types of problems:

  • Se il sistema viene arrestato dopo che la transazione ha eseguito numerose modifiche di cui non è stato eseguito il commit, la fase di recupero del riavvio successivo può richiedere tempi notevolmente più lunghi rispetto al valore specificato dall'opzione dell' intervallo di recupero .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.
  • È possibile che il log raggiunga dimensioni considerevoli in quanto non può essere troncato dopo il numero MinLSN.The log might grow very large, because the log cannot be truncated past the MinLSN. Ciò si verifica anche se il database utilizza il modello di recupero con registrazione minima, in base al quale il log delle transazioni viene in genere troncato in corrispondenza di ogni checkpoint automatico.This occurs even if the database is using the simple recovery model, in which the transaction log is generally truncated on each automatic checkpoint.

Transazioni di replicaReplication Transactions

L'agente di lettura log esegue il monitoraggio del log delle transazioni di tutti i database configurati per la replica transazionale e copia le transazioni contrassegnate per la replica dal log delle transazioni al database di distribuzione.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. Il log attivo deve contenere tutte le transazioni contrassegnate per la replica, ma non ancora recapitate al database di distribuzione.The active log must contain all transactions that are marked for replication, but that have not yet been delivered to the distribution database. Se tali transazioni non vengono replicate tempestivamente, potrebbero impedire il troncamento del log.If these transactions are not replicated in a timely manner, they can prevent the truncation of the log. Per altre informazioni, vedere Replica transazionale.For more information, see Transactional Replication.

Ulteriori informazioniAdditional Reading

Per ulteriori informazioni sul log delle transazioni, vedere gli articoli e i documenti riportati di seguito.We recommend the following articles and books for additional information about the transaction log.

Gestione delle dimensioni del file di log delle transazioni Manage the Size of the Transaction Log File
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)
Log delle transazioni (SQL Server) The Transaction Log (SQL Server)
Informazioni sulla registrazione e il recupero in SQL Server di Paul Randall Understanding Logging and Recovery in SQL Server by Paul Randal
Gestione del log delle transazioni di SQL Server di Tony Davis e Gail ShawSQL Server Transaction Log Management by Tony Davis and Gail Shaw