Condividi tramite

Log delle transazioni (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 registro delle transazioni deve essere troncato regolarmente per impedirne il riempimento. Tuttavia, alcuni fattori possono ritardare il troncamento del log, quindi il monitoraggio delle dimensioni del log è importante. Ad alcune operazioni può essere applicata la registrazione minima per ridurre l'impatto sulle dimensioni del log delle transazioni.

Il log delle transazioni è un componente critico del database e, in caso di errore di sistema, il log delle transazioni potrebbe essere necessario per riportare il database a uno stato coerente. Il log delle transazioni non deve mai essere eliminato o spostato a meno che non si conoseguano completamente le ramificazioni di questa operazione.

Annotazioni

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).

Contenuto dell'argomento

Vantaggi: 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 di soluzioni di disponibilità elevata e ripristino di emergenza: gruppi di disponibilità AlwaysOn, mirroring del database e log shipping.

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. Il troncamento del log è essenziale per impedire il riempimento del log. Il troncamento del log elimina i file di log virtuali inattivi dal log delle transazioni logiche di un database di SQL Server, liberando spazio nel log logico per il riutilizzo dal log delle transazioni fisiche. Se un log delle transazioni non fosse mai stato troncato, alla fine riempirebbe tutto lo spazio su disco allocato ai file di log fisici.

Per evitare questo problema, a meno che il troncamento del log non venga ritardato per qualche motivo, il troncamento si verifica automaticamente dopo gli eventi seguenti:

  • 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 dopo il 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).

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

Annotazioni

Il troncamento del log non riduce le dimensioni del file di log fisico. Per ridurre le dimensioni fisiche di un file di log fisico, è necessario compattare il file di log. Per informazioni sulla compattazione delle dimensioni del file di log fisico, vedere Gestire le dimensioni del file di log delle transazioni.

Fattori che possono ritardare il troncamento del log

Quando i record di log rimangono attivi per un lungo periodo di tempo il troncamento del log delle transazioni viene ritardato e potenzialmente il log delle transazioni può riempirsi.

Importante

Per informazioni su come rispondere a un log delle transazioni completo, vedere Risolvere i problemi relativi a un log delle transazioni completo (errore di SQL Server 9002).

Il troncamento dei log può essere ritardato da vari fattori. È possibile individuare cosa, in caso affermativo, impedisce il troncamento del log eseguendo 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 NIENTE Attualmente sono presenti uno o più file di log virtuali riutilizzabili.
1 POSTO DI CONTROLLO Non si è verificato alcun checkpoint dopo l'ultimo troncamento del log o l'intestazione del log non è ancora stata spostata oltre un file di log virtuale. (Tutti i modelli di recupero)

Questa è una ragione comune per ritardare il troncamento del log. Per altre informazioni, vedere Checkpoint di database (SQL Server).
2 Backup del registro È 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 BACKUP_O_RIPRISTINO_ATTIVO È in corso un backup dei dati o un ripristino (tutti i modelli di ripristino).

Se il troncamento del log è impedito da un backup dei dati, l'annullamento del backup può risolvere il problema immediato.
4 TRANSAZIONE_ATTIVA 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. Si noti che le transazioni di lunga durata impediscono il troncamento del log per tutti i modelli di recupero, incluso il modello di recupero semplice, in cui il log delle transazioni viene in genere 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).

Le transazioni a esecuzione prolungata potrebbero anche riempire il log delle transazioni di tempdb. Tempdb viene utilizzato in modo implicito dalle transazioni degli utenti per oggetti interni come tabelle di lavoro per l'ordinamento, file di lavoro per l'hashing, tabelle di lavoro dei cursori e controllo delle versioni delle righe. Anche se la transazione utente include solo i dati di lettura (query SELECT), è possibile creare e usare oggetti interni nelle transazioni utente. È quindi possibile compilare il log delle transazioni tempdb.
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 REPLICA 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 CREAZIONE_ISTANTANEA_DATABASE 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 È in corso una scansione 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 REPLICA_DI_DISPONIBILITÀ Una replica secondaria di un gruppo di disponibilità applica i record del log delle transazioni del database a un database secondario corrispondente. (Modello di recupero completo)

Per altre informazioni, vedere Panoramica dei gruppi di disponibilità AlwaysOn (SQL Server).
10 - Solo per uso interno
11 - Solo per uso interno
12 - Solo per uso interno
13 PAGINA PIÙ VECCHIA Se un database è configurato per l'uso di checkpoint indiretti, la pagina meno recente del database potrebbe essere precedente a quella del checkpoint LSN. In questo caso, la pagina più vecchia può ritardare il troncamento del log. (Tutti i modelli di recupero)

Per informazioni sui checkpoint indiretti, vedere Checkpoint del database (SQL Server).
14 ALTRO_TRANSITORIO Questo valore non è attualmente utilizzato.
16 XTP_CHECKPOINT Quando un database dispone di un filegroup ottimizzato per la memoria, il log delle transazioni potrebbe non troncare fino a quando non viene attivato il checkpoint OLTP automatico In-Memory (che si verifica ogni 512 MB di crescita del log).

Nota: per troncare il log delle transazioni prima delle dimensioni di 512 MB, attivare manualmente il comando Checkpoint sul database in questione.

Operazioni che possono essere registrate con 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 argomento vengono identificate le operazioni che vengono registrate in modo minimale nel modello di recupero a registrazione minima delle operazioni bulk, così come nel modello di recupero semplice, tranne quando è in esecuzione un backup.

Annotazioni

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

Annotazioni

Nel modello di recupero con registrazione completa tutte le operazioni bulk vengono registrate completamente. È 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. Tuttavia, se il database è danneggiato o perso quando è attivo il logging minimo, non è possibile ripristinare il database al momento del guasto.

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 bulk (bcp, BULK INSERT e INSERT... SELECT). Per maggiori informazioni su quando l'importazione bulk in una tabella viene registrata in modo minimo, vedere Prerequisiti per la registrazione minima nell'importazione bulk.

    Annotazioni

    Quando la replica transazionale è abilitata, le operazioni BULK INSERT vengono registrate completamente anche con il modello di ripristino 'Bulk-Logged'.

  • Operazioni di SELECT INTO.

    Annotazioni

    Quando la replica transazionale è abilitata, le operazioni SELECT INTO vengono registrate completamente anche nel modello di recupero Bulk Logged.

  • Aggiornamenti parziali ai tipi di dati di valore di grandi dimensioni, usando . Clausola WRITE nell'istruzione UPDATE durante l'inserimento o l'aggiunta di nuovi dati. Si noti che la registrazione minima non viene usata quando vengono aggiornati i valori esistenti. Per altre informazioni sui tipi di dati per valori di grandi dimensioni, vedere Tipi di dati (Transact-SQL).

  • Istruzioni WRITETEXT e UPDATETEXT durante l'inserimento o l'aggiunta di nuovi dati nelle colonne del tipo di dati text, ntext e image. Si noti che la registrazione minima non viene usata quando vengono aggiornati i valori esistenti.

    Annotazioni

    Le istruzioni WRITETEXT e UPDATETEXT sono deprecate, pertanto è consigliabile 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 Operazioni REBUILD o DBCC DBREINDEX.

      Annotazioni

      L'istruzione DBCC DBREINDEX è obsoleta, quindi è consigliabile evitarne l'uso nelle nuove applicazioni.

    • DROP INDEX nuova ricompilazione heap (se applicabile).

      Annotazioni

      La deallocazione della pagina di indice durante un'operazione DROP INDEX viene sempre registrata completamente.

Attività correlate

Managing the transaction log

Backup del log delle transazioni (modello di recupero completo)

Ripristino del log delle transazioni (modello di recupero completo)

Vedere anche

Controllo della durabilità delle transazioni
Prerequisiti per il log minimo nell'importazione di massa
backup e ripristino dei database di SQL Server
Checkpoint del database (SQL Server)
Visualizzare o modificare le proprietà di un database
Modelli di recupero (SQL Server)