Log delle transazioni (SQL Server)

Articolo
10/01/2013

Ogni database di SQL Server include un log delle transazioni in cui vengono archiviate tutte le transazioni e le modifiche apportate dalle transazioni stesse al database. Per evitarne il riempimento, il log delle transazioni deve essere troncato regolarmente. Tuttavia, alcuni fattori possono posticipare il troncamento del log, pertanto è importante monitorare le dimensioni del log. 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 fondamentale del database e, in caso di errore di sistema, può essere necessario per ripristinare la consistenza del database. Il log delle transazioni non deve mai essere eliminato o spostato, a meno che non vi sia la piena consapevolezza delle conseguenze di tale operazione.

[!NOTA]

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

Contenuto dell'argomento:

Vantaggi: operazioni supportate dal log delle transazioni
Troncamento del log delle transazioni
Fattori che possono posticipare il troncamento del log
Operazioni per cui è possibile eseguire la registrazione minima
Attività correlate

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

Icona freccia utilizzata con il collegamento Torna all'inizio [[Torna all'inizio]]

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 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 un 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 log più avanti in questo argomento.

[!NOTA]

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 del file di log fisico, vedere Gestione delle dimensioni del file di log delle transazioni.

Icona freccia utilizzata con il collegamento Torna all'inizio [Inizio pagina]

Fattori che possono posticipare il troncamento del log

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

Importante
Per informazioni sulla gestione di un log delle transazioni pieno, vedere Risolvere i problemi relativi a un log delle transazioni completo (Errore di SQL Server 9002).

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.

log_reuse_wait value	valore log_reuse_wait_desc	Descrizione
0	NOTHING	Attualmente vi sono uno o più file di log virtuali riutilizzabili.
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. (Tutti i modelli di recupero) Si tratta di una motivazione comune per il posticipo del troncamento del log. Per ulteriori 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. [!NOTA] 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).
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 ulteriori 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 Replica di SQL Server.
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	È 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.
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. (Modello di recupero con registrazione completa) Per ulteriori informazioni, vedere Panoramica di Gruppi di disponibilità AlwaysOn (SQL Server).
10	—	Solo per uso interno
11	—	Solo per uso interno
12	—	Solo per uso interno
13	OLDEST_PAGE	Se 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).
14	OTHER_TRANSIENT	Questo valore non è attualmente utilizzato.

Icona freccia utilizzata con il collegamento Torna all'inizio [[Torna all'inizio]]

Operazioni per cui è possibile eseguire la registrazione minima

La registrazione minima implica la registrazione nel log delle transazioni delle sole informazioni necessarie per il recupero della transazione 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]

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 un'operazione 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 bulk (bcp, BULK INSERT e INSERT... SELECT). 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 bulk.

[!NOTA]

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.

[!NOTA]

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 ulteriori 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 con tipo di dati text, ntext e image. Si noti che la registrazione minima non viene utilizzata per l'aggiornamento di valori esistenti.

[!NOTA]

Poiché le istruzioni WRITETEXT e UPDATETEXT sono deprecate, è consigliabile evitare di utilizzarle 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).
- Operazioni ALTER INDEX REBUILD o DBCC DBREINDEX.
  
  [!NOTA]
  
  Poiché l'istruzione DBCC DBREINDEX è deprecata, è consigliabile evitare di utilizzarla nelle nuove applicazioni.
- Ricompilazione del nuovo heap DROP INDEX (se pertinente).
  
  [!NOTA]
  
  Durante un'operazione DROP INDEX per la deallocazione delle pagine di un indice viene eseguita sempre la registrazione completa.