Il log delle transazioni

  • Articolo

Si applica a: SQL Server

Ogni database SQL Server include un log delle transazioni in cui vengono archiviate tutte le transazioni e le modifiche apportate dalle transazioni stesse al database.

Il log delle transazioni è un componente fondamentale del database. Se si verifica un errore di sistema, è necessario tale log per ripristinare il database a uno stato coerente.

Avviso

Evitare di eliminare o spostare questo file di registro se non si conoscono a fondo le implicazioni di questa operazione.

Per informazioni sull'architettura e sui meccanismi interni del log delle transazioni, vedere la Guida sull'architettura e gestione del log delle transazioni di SQL Server.

Suggerimento

I checkpoint rappresentano i punti ottimali noti da cui avviare l'applicazione dei log delle transazioni durante il ripristino del database. Per altre informazioni, vedere Checkpoint di database (SQL Server).

Operazioni supportate dal log delle transazioni

Il log delle transazioni supporta le operazioni seguenti:

  • Recupero di singole transazioni.
  • Recupero di tutte le transazioni incomplete all'avvio di SQL Server.
  • Rollforward di una pagina, file, filegroup o database ripristinato fino al punto in cui si è verificato l'errore.
  • Supporto della replica transazionale.
  • Supporto delle soluzioni di ripristino di emergenza e disponibilità elevata: gruppi di disponibilità Always On, mirroring del database e log shipping.

Recupero di singole transazioni

Se un'applicazione esegue un'istruzione ROLLBACK o se il motore di database rileva un errore come la perdita delle comunicazioni con un client, vengono usati i record del log per eseguire il rollback delle modifiche apportate da una transazione incompleta.

Recupero di tutte le transazioni incomplete all'avvio di SQL Server

Se si verifica un errore in un server, è possibile che alcune modifiche ai database non siano state scritte dalla cache del buffer ai file di dati e che transazioni incomplete abbiano apportato modifiche al file di dati. All'avvio di un'istanza di SQL Server, vengono recuperati i singoli database. Viene quindi eseguito il roll forward di tutte le modifiche registrate nel log che potrebbero non essere state scritte nei file di dati. A questo punto, per salvaguardare l'integrità del database, viene eseguito il rollback di tutte le transazioni incomplete rilevate nel log delle transazioni. Per altre informazioni, vedere Panoramica del ripristino e del recupero (SQL Server).

Rollforward di una pagina, un file, un filegroup o un database ripristinato fino al punto in cui si è verificato l'errore

Dopo un errore hardware o del disco che interessa i file del database, è possibile ripristinare il database fino al punto in cui si è verificato l'errore. Questo metodo prevede innanzitutto il ripristino dell'ultimo backup completo del database e dell'ultimo backup differenziale del database e quindi il ripristino della sequenza successiva dei backup del log delle transazioni fino al momento in cui si è verificato l'errore.

Durante il ripristino di ogni backup del log, il motore di database riapplica tutte le modifiche registrate nel log per eseguire il rollforward di tutte le transazioni. Dopo il ripristino dell'ultimo backup del log, il motore di database usa le informazioni disponibili nel log per eseguire il rollback di tutte le transazioni non ancora completate al momento dell'esecuzione di tale backup. Per altre informazioni, vedere Panoramica del ripristino e del recupero (SQL Server).

Supporto della replica transazionale

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. Per altre informazioni, vedere Funzionamento della replica transazionale.

Supportare le soluzioni di disponibilità elevata e ripristino di emergenza

Le soluzioni con server di standby, i gruppi di disponibilità Always On, il mirroring del database e il log shipping sono basati principalmente sul log delle transazioni.

In uno scenario con gruppo di disponibilità Always On, ogni aggiornamento apportato a un database (la replica primaria) viene immediatamente riprodotto in copie complete distinte del database (le repliche secondarie). La replica primaria invia immediatamente ogni record di log alle repliche secondarie. In questo modo, i record di log in ingresso vengono applicati ai database del gruppo di disponibilità, con una costante operazione di rollforward. Per altre informazioni, vedere Istanze del cluster di failover Always On (SQL Server).

In uno scenario di log shipping il server primario invia il log delle transazioni attivo del database primario a una o più destinazioni. I singoli server secondari ripristinano i backup dei log nei relativi database secondari locali. Per altre informazioni, vedere Informazioni sul log shipping (SQL Server).

In uno scenario di mirroring del database tutti gli aggiornamenti di un database (del database principale) vengono immediatamente riprodotti in una copia distinta e completa del database, il database mirror. L'istanza del server principale invia immediatamente i singoli record di log all'istanza del server mirror, che applica i record ricevuti nel database mirror, eseguendone continuamente il roll forward. Per altre informazioni, vedere Mirroring del database (SQL Server).

Caratteristiche del log delle transazioni

Caratteristiche del log delle transazioni del motore di database di SQL Server:

  • Il log delle transazioni viene implementato come file o set di file distinto nel database. La cache del log viene gestita separatamente dalla cache del buffer per le pagine di dati e, pertanto, genera codice semplice, rapido e affidabile nel motore di database di SQL Server. Per altre informazioni, vedere Architettura fisica del log delle transazioni.

  • Il formato dei record e delle pagine del log non deve essere necessariamente conforme al formato delle pagine di dati.

  • Il log delle transazioni può essere implementato in diversi file, definiti in modo da espandersi automaticamente tramite l'impostazione del valore FILEGROWTH per il log. In questo modo è possibile ridurre le probabilità che si esaurisca lo spazio nel log delle transazioni e, nel contempo, alleggerire l'overhead amministrativo. Per altre informazioni, vedere Opzioni per file e filegroup ALTER DATABASE (Transact-SQL).

  • Il meccanismo che permette di riutilizzare lo spazio nei file di log è rapido e produce effetti minimi sulla velocità effettiva delle transazioni.

Per informazioni sull'architettura e sui meccanismi interni del log delle transazioni, vedere la Guida sull'architettura e gestione del log delle transazioni di SQL Server.

Troncamento del log delle transazioni

Il troncamento del log libera spazio nel file di log per consentirne il riutilizzo da parte del log delle transazioni. È necessario troncare periodicamente il log delle transazioni per evitare il riempimento dello spazio allocato. Numerosi fattori possono posticipare il troncamento del log, pertanto è importante monitorare la dimensione del log. Ad alcune operazioni può essere applicata la registrazione minima per ridurre l'impatto sulle dimensioni del log delle transazioni.

Il troncamento del log elimina i file di log virtuali (VLF) inattivi dal log delle transazioni logico di un database di SQL Server, liberando spazio nel log logico riusabile dal log delle transazioni fisico. Se un log delle transazioni non viene mai troncato, le sue dimensioni aumenteranno fino a occupare tutto lo spazio su disco allocato ai file di log fisici.

Per evitare l'esaurimento dello spazio, il troncamento si verifica automaticamente dopo gli eventi riportati di seguito, a meno che l'operazione non sia stata posticipata per qualche motivo:

  • Nel modello di recupero con registrazione minima, dopo un checkpoint.

  • Nel modello di recupero con registrazione completa o nel modello di recupero con registrazione minima delle operazioni bulk, se si è verificato un checkpoint dal backup precedente, il troncamento si verifica dopo un backup del log (a meno che non si tratti di un backup del log di sola copia).

  • Quando si crea un database usando il modello di recupero con registrazione completa, il log delle transazioni verrà riutilizzato in base alle esigenze (in modo analogo a un database tramite il modello di recupero con registrazione minima), fino a quando non si crea un backup completo del database.

Per altre informazioni, vedere Fattori che possono posticipare il troncamento del log più avanti in questo articolo.

Il troncamento del log non riduce le dimensioni del file di log fisico. Per ridurre la dimensione fisica di un file di log fisico, è necessario ridurre il file di log. Per informazioni sulla compattazione del file di log fisico, vedere Gestire le dimensioni del file registro delle transazioni. Tenere tuttavia presenti i fattori che possono posticipare il troncamento del log. Se dopo una compattazione del log è di nuovo necessario lo spazio di archiviazione, il log delle transazioni torna a crescere e durante tale crescita origina un overhead delle prestazioni.

Fattori che possono posticipare il troncamento del log

Quando i record del log rimangono attivi per molto tempo il troncamento viene ritardato e il log delle transazioni potrebbe riempirsi, come già accennato in precedenza.

Importante

Per informazioni su come agire quando il log delle transazioni è completo, vedere Risolvere i problemi di un log delle transazioni completo (errore 9002 di SQL Server).

Il troncamento del log può essere posticipato da diversi fattori. Per individuare l'eventuale condizione che impedisce il troncamento del log, eseguire una query sulle colonne log_reuse_wait e log_reuse_wait_desc della vista del catalogo sys.databases. Nella tabella seguente vengono descritti i valori di queste colonne.

Valore di log_reuse_wait Valore di log_reuse_wait_desc Descrizione
0 NOTHING Attualmente sono disponibili uno o più file di log virtuali (VLF) riusabili.
1 CHECKPOINT Non si è verificato alcun checkpoint dall'ultimo troncamento del log oppure l'inizio del log non è stato ancora spostato oltre un file di log virtuale (VLF) (tutti i modelli di recupero).

Si tratta di una motivazione comune per il posticipo del troncamento del log. Per altre informazioni, vedere Checkpoint di database (SQL Server).
2 LOG_BACKUP È necessario eseguire un backup del log prima del troncamento del log delle transazioni. (Solo modelli di recupero con registrazione completa e con registrazione minima delle operazioni bulk)

Quando il backup del log successivo viene completato, parte dello spazio del log potrebbe divenire riutilizzabile.
3 ACTIVE_BACKUP_OR_RESTORE È in esecuzione un processo di backup o ripristino dei dati (tutti i modelli di recupero).

Se il troncamento del log è impedito da un backup dei dati, l'annullamento del backup può risolvere il problema immediato.
4 ACTIVE_TRANSACTION Una transazione è attiva (tutti i modelli di recupero):

Una transazione con esecuzione prolungata potrebbe esistere all'inizio del backup del log. In questo caso, per liberare lo spazio potrebbe essere necessario un altro backup del log. Le transazioni con esecuzione prolungata impediscono il troncamento del log in tutti i modelli di recupero, incluso il modello di recupero con registrazione minima in cui il log delle transazioni viene generalmente troncato a ogni checkpoint automatico.

Viene posticipata una transazione. Una transazione posticipata è una transazione attiva ed efficace il cui ritorno allo stato precedente è bloccato a causa di alcune risorse non disponibili. Per informazioni sulle cause delle transazioni posticipate e su come modificarne lo stato, vedere Transazioni posticipate (SQL Server).

Anche le transazioni con esecuzione prolungata potrebbero riempire il log delle transazioni di tempdb. tempdb viene usato in modo implicito dalle transazioni utente per gli oggetti interni, ad esempio tabelle di lavoro per l'ordinamento, file di lavoro per l'hashing, tabelle di lavoro di cursori e controllo delle versioni delle righe. Anche se la transazione utente include solo la lettura dei dati (query SELECT), durante le transazioni utente possono essere creati e usati oggetti interni. In questo modo, il log delle transazioni di tempdb potrebbe riempirsi.
5 DATABASE_MIRRORING Il mirroring del database è sospeso o in modalità a prestazioni elevate, il database mirror è notevolmente in ritardo rispetto al database principale. (Solo modello di recupero con registrazione completa)

Per altre informazioni, vedere Mirroring del database (SQL Server).
6 REPLICATION Durante le repliche transazionali, le transazioni significative per le pubblicazioni non sono ancora state recapitate al database di distribuzione. (Solo modello di recupero con registrazione completa)

Per informazioni sulla replica transazionale, vedere SQL Server Replication.
7 DATABASE_SNAPSHOT_CREATION Viene creato uno snapshot del database (tutti i modelli di recupero).

Si tratta di una motivazione comune, e generalmente di breve durata, per il posticipo del troncamento del log.
8 LOG_SCAN Si sta verificando un'analisi del log (tutti i modelli di recupero).

Si tratta di una motivazione comune, e generalmente di breve durata, per il posticipo del troncamento del log.
9 AVAILABILITY_REPLICA Una replica secondaria di un gruppo di disponibilità applica i record del log delle transazioni del database a un database secondario corrispondente. (Solo modello di recupero con registrazione completa)

Per altre informazioni, vedere Definizione del gruppo di disponibilità Always On.
10 - Solo per uso interno
11 - Solo per uso interno
12 - Solo per uso interno
13 OLDEST_PAGE Se un database è configurato per l'uso dei checkpoint indiretti, la pagina meno recente del database potrebbe essere meno recente del numero di sequenza del file di log (LSN) del checkpoint. In questo caso, la pagina meno recente può causare il posticipo del troncamento del log (tutti i modelli di recupero).

Per informazioni sui checkpoint indiretti, vedere Checkpoint di database (SQL Server).
14 OTHER_TRANSIENT Questo valore non è attualmente utilizzato.
16 XTP_CHECKPOINT È necessario eseguire un checkpoint OLTP in memoria. Per le tabelle ottimizzate per la memoria, viene effettuato un checkpoint automatico quando la dimensione del file di log delle transazioni supera 1,5 GB dopo l'ultimo checkpoint (include sia le tabelle basate su disco che quelle con ottimizzazione per la memoria)

Per altre informazioni, vedere Operazione su checkpoint per le tabelle con ottimizzazione per la memoria e [Processo di registrazione e checkpoint per le tabelle ottimizzate per la memoria] (https://blogs.msdn.microsoft.com/sqlcat/2016/05/20/logging-and-checkpoint-process-for-memory-optimized-tables-2/)

Operazioni per cui è possibile eseguire la registrazione minima

Laregistrazione minima implica la registrazione nel log delle transazioni delle sole informazioni necessarie per il recupero della transazione stesse senza il supporto del recupero temporizzato. In questo articolo vengono identificate le operazioni con registrazione minima nel modello di recupero con registrazione minima delle operazioni bulk nonché nel modello di recupero con registrazione minima, ad eccezione dei momenti in cui è in esecuzione un backup.

La registrazione minima non è supportata dalle tabelle ottimizzate per la memoria.

In base al modello di recuperocon registrazione completa tutte le operazioni bulk vengono registrate per intero. È tuttavia possibile ridurre al minimo la registrazione per un set di operazioni bulk passando temporaneamente il database al modello di recupero con registrazione minima delle operazioni bulk per le operazioni bulk. La registrazione minima è più efficiente della registrazione completa e riduce la possibilità che un'operazione bulk su larga scala esaurisca lo spazio disponibile per il log delle transazioni durante una transazione bulk. Se tuttavia il database viene danneggiato o perso durante la registrazione minima, non è possibile recuperarlo fino al punto di errore.

Per le operazioni seguenti, con registrazione completa nel modello di recupero con registrazione completa, è prevista la registrazione minima nel modello di recupero con registrazione minima e in quello con registrazione minima delle operazioni bulk:

  • Operazioni di importazione in blocco (bcp, BULK INSERT e INSERT). Per ulteriori informazioni sui casi in cui viene eseguita la registrazione minima di un'importazione bulk in una tabella, vedere Prerequisiti per la registrazione minima nell'importazione in blocco.

    Quando la replica transazionale è abilitata, le operazioni BULK INSERT vengono registrate completamente, anche nel modello di recupero con registrazione minima delle operazioni bulk.

  • Operazioni SELECT - INTO clausola.

    Quando la replica transazionale è abilitata, le operazioni SELECT INTO vengono registrate completamente, anche nel modello di recupero con registrazione minima delle operazioni bulk.

  • Aggiornamenti parziali a tipi di dati di valori di grandi dimensioni eseguiti mediante la clausola .WRITE nell'istruzione UPDATE quando si inseriscono o si aggiungono nuovi dati. La registrazione minima non viene utilizzata in caso di aggiornamento di valori esistenti. Per altre informazioni sui tipi di dati per valori di grandi dimensioni, vedere Tipi di dati.

  • IstruzioniWRITETEXT e UPDATETEXT durante l'inserimento o l'aggiunta di nuovi dati nelle colonne con tipo di dati text, ntext, e image . La registrazione minima non viene utilizzata in caso di aggiornamento di valori esistenti.

    Avviso

    Le istruzioni WRITETEXT e UPDATETEXT sono deprecate, evitare di usarle nelle nuove applicazioni.

  • Se il database viene impostato sul modello di recupero con registrazione minima o con registrazione delle operazioni bulk, verrà eseguita la registrazione minima di alcune operazioni DDL sugli indici indipendentemente dal fatto che l'operazione venga eseguita online o offline. Le operazioni sugli indici con registrazione minima sono le seguenti:

    • OperazioniCREATE INDEX (incluse le viste indicizzate).

    • ALTER INDEX REBUILD o operazione DBCC DBREINDEX.

      Le operazioni di compilazione degli indici usano la registrazione minima ma possono essere posticipate se contemporaneamente viene eseguito un backup. Questo ritardo è causato dai requisiti di sincronizzazione delle pagine del pool di buffer con registrazione minima quando si usa il modello di recupero semplice o con registrazione minima delle operazioni bulk.

      Avviso

      L'istruzione DBCC DBREINDEX è deprecata, evitare di usarla nelle nuove applicazioni.

    • Ricompilazione del nuovo heap DROP INDEX (se pertinente). Durante un'operazione DROP INDEX per la deallocazione delle pagine di un indice viene eseguita sempre la registrazione completa.

Attività Articolo
Gestire il log delle transazioni - Gestione delle dimensioni del file di log delle transazioni

- Risolvere i problemi relativi a un log delle transazioni completo (Errore di SQL Server 9002)
Backup del log delle transazioni (solo modello di recupero con registrazione completa) - Eseguire il backup di un log delle transazioni

- Eseguire il backup del log delle transazioni quando il database è danneggiato (SQL Server)
Ripristinare il log delle transazioni (solo modello di recupero con registrazione completa) - Ripristinare un backup del log delle transazioni (SQL Server)

Contenuto correlato