Log delle transazioni (SQL Server)

Articolo
01/04/2017

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. 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 coerenza 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 altre 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 ritardare il troncamento del log
Operazioni che possono essere registrate minimamente
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 di soluzioni di disponibilità elevata e ripristino di emergenza: Always On gruppi di disponibilità, 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 evitare 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 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 altre informazioni, vedere Fattori che possono ritardare 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 Gestire le dimensioni del file di log delle transazioni.

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 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 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 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. Si noti che le transazioni a esecuzione prolungata impediscono il troncamento del log in tutti i modelli di ripristino, incluso il modello di ripristino semplice, in cui il log delle transazioni viene in genere troncato in 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 dati di lettura (query SELECT), gli oggetti interni possono essere creati e usati nelle transazioni utente. 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	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	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 altre informazioni, vedere Panoramica dei gruppi di disponibilità AlwaysOn (SQL Server).For more information, see Overview of AlwaysOn Availability Groups (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.
16	XTP_CHECKPOINT	Quando un database ha 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 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 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 ottimizzate per la memoria.

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:

Operazioni di importazione in blocco (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 Prerequisites for Minimal Logging in Bulk Import.

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 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 textcolonne del tipo di dati , ntexte 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:
- OperazioniCREATE INDEX (incluse le viste indicizzate).
- OperazioniALTER 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.