Log delle transazioni (SQL Server)

 

Si applica a: SQL Server 2016

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 registro per ripristinare uno stato coerente del database. Evitare di eliminare o spostare questo file di registro se non si conoscono a fondo le implicazioni di questa operazione.

Per evitarne il riempimento, il log delle transazioni deve essere troncato regolarmente. Alcuni 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.

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

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à AlwaysOn, mirroring del database e log shipping.

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 evitare il riempimento del log. Il troncamento del log elimina i file di log virtuali inattivi dal log delle transazioni logico di un database di SQL Server , liberando spazio nel log logico per il riutilizzo da parte del log delle transazioni fisico. 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.

Per evitare questo problema, il troncamento si verifica automaticamente dopo gli eventi riportati di seguito, a meno che tale 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).

Per ulteriori informazioni, vedere Fattori che possono posticipare il troncamento del logpiù avanti in questo argomento.

NOTA! 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 Manage the Size of the Transaction Log File.

Quando i record del log rimangono attivi per molto tempo il troncamento viene posticipato e il log delle transazioni potrebbe riempirsi.

System_CAPS_ICON_important.jpg Importante


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

Il troncamento del log può essere posticipato da diverse ragioni. 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_waitValore di log_reuse_wait_descDescrizione
0NOTHINGAttualmente vi sono uno o più file di log virtuali riutilizzabili.
1CHECKPOINTNon si è verificato alcun checkpoint dall'ultimo troncamento del log oppure l'inizio del log non è stato ancora spostato oltre un file di log virtuale. (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).
2LOG_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.
3ACTIVE_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.
4ACTIVE_TRANSACTIONUna 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 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).
5DATABASE_MIRRORINGIl 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).
6REPLICATIONDurante 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.
7DATABASE_SNAPSHOT_CREATIONViene 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.
8LOG_SCANÈ in corso 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.
9AVAILABILITY_REPLICAUna replica secondaria di un gruppo di disponibilità applica i record del log delle transazioni del database a un database secondario corrispondente. (Modello di recupero con registrazione completa)

Per altre informazioni, vedere Panoramica di Gruppi di disponibilità AlwaysOn (SQL Server).
10Solo per uso interno
11Solo per uso interno
12Solo per uso interno
13OLDEST_PAGESe un database è configurato per l'utilizzo dei checkpoint indiretti, la pagina meno recente del database potrebbe essere meno recente dell'LSN 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).
14OTHER_TRANSIENTQuesto valore non è attualmente utilizzato.

La registrazione 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 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.

NOTA!! La registrazione minima non è supportata dalle tabelle con ottimizzazione per la memoria.

ALTRA NOTA! In base al modello di recupero con 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:

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

  • Operazioni SELECT INTO .

Quando la replica transazionale è abilitata, le operazioni SELECT INTO vengono registrate completamente persino 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. Si noti che la registrazione minima non viene utilizzata per l'aggiornamento di valori esistenti. Per altre informazioni sui tipi di dati per valori di grandi dimensioni, vedere Tipi di dati (Transact-SQL).

  • IstruzioniWRITETEXT e UPDATETEXT durante l'inserimento o l'aggiunta di nuovi dati nelle colonne con tipo di dati text, ntext, e image . Si noti che la registrazione minima non viene utilizzata per l'aggiornamento di valori esistenti.

    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:

    • Operazioni CREATE INDEX (incluse le viste indicizzate).

    • OperazioniALTER INDEX REBUILD o DBCC DBREINDEX.

      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.

Gestione del log delle transazioni

Backup del log delle transazioni (modello di recupero con registrazione completa)

Ripristino del log delle transazioni (modello di recupero con registrazione completa)

Controllo della durabilità delle transazioni
Prerequisiti per la registrazione minima nell'importazione bulk
Backup e ripristino di database SQL Server
Checkpoint di database (SQL Server)
Visualizzare o modificare le proprietà di un database
Modelli di recupero (SQL Server)

Aggiunte alla community

AGGIUNGI
Mostra: