The Transaction Log (SQL Server) (Italiano)

• 10/23/2019
• 11 minutes to read
• • M
  • D
  • f
  • M
  • s
  • +8

Applies to: SQL Server (all supported versions)

Every SQL Server database has a transaction log that records all transactions and the database modifications made by each transaction.,

Il log delle transazioni è un componente critico del database. Se si verifica un errore di sistema, sarà necessario quel registro per riportare il database a uno stato coerente.

Per informazioni sull’architettura e gli interni del log delle transazioni, consultare la Guida all’architettura e alla gestione del log delle transazioni di SQL Server.

Operazioni supportate dal log delle transazioni

Il log delle transazioni supporta le seguenti operazioni:

• Recupero di singole transazioni.
• Ripristino di tutte le transazioni incomplete all’avvio di SQL Server.
• Eseguire l’inoltro di un database, un file, un gruppo di file o una pagina ripristinati fino al punto di errore.
• Supporto della replica transazionale.
• Supporto di soluzioni ad alta disponibilità e disaster recovery: gruppi di disponibilità sempre attivi, mirroring del database e spedizione dei log.,

Recupero di transazioni individuali

Se un’applicazione emette un’istruzioneROLLBACK o se il motore di database rileva un errore come la perdita di comunicazione con un client, i record di registro vengono utilizzati per ripristinare le modifiche apportate da una transazione incompleta.,

Ripristino di tutte le transazioni incomplete all’avvio di SQL Server

Se un server non riesce, i database possono essere lasciati in uno stato in cui alcune modifiche non sono mai state scritte dalla cache del buffer ai file di dati e potrebbero esserci alcune modifiche da transazioni incomplete nei file di dati. Quando viene avviata un’istanza di SQL Server, viene eseguito un ripristino di ciascun database. Ogni modifica registrata nel registro che potrebbe non essere stata scritta nei file di dati viene eseguita in avanti., Ogni transazione incompleta trovata nel log delle transazioni viene quindi ripristinata per assicurarsi che l’integrità del database sia preservata. Per ulteriori informazioni, vedere Panoramica ripristino e ripristino (SQL Server).

Eseguire l’inoltro di un database, un file, un gruppo di file o una pagina ripristinati al punto di errore

Dopo una perdita hardware o un errore del disco che interessano i file del database, è possibile ripristinare il database al punto di errore., Ripristinare prima l’ultimo backup completo del database e l’ultimo backup differenziale del database, quindi ripristinare la sequenza successiva dei backup del log delle transazioni fino al punto di errore.

Durante il ripristino di ogni backup del registro, il Motore di database riapplica tutte le modifiche registrate nel registro per eseguire il roll forward di tutte le transazioni. Quando viene ripristinato l’ultimo backup del registro, il Motore di database utilizza le informazioni del registro per ripristinare tutte le transazioni che non erano complete in quel momento. Per ulteriori informazioni, vedere Panoramica ripristino e ripristino (SQL Server).,

Supporto della replica transazionale

L’agente Log Reader monitora il log delle transazioni di ciascun database configurato per la replica transazionale e copia le transazioni contrassegnate per la replica dal log delle transazioni nel database di distribuzione. Per ulteriori informazioni, vedere Come funziona la replica transazionale.

Supporto di soluzioni ad alta disponibilità e disaster recovery

Le soluzioni standby-server, sempre su gruppi di disponibilità, mirroring del database e spedizione dei log, si basano molto sul log delle transazioni.,

In uno scenario di gruppi di disponibilità sempre attivi, ogni aggiornamento di un database, la replica primaria, viene immediatamente riprodotto in copie separate e complete del database, le repliche secondarie. La replica primaria invia ogni record di registro immediatamente alle repliche secondarie, che applica i record di registro in entrata ai database dei gruppi di disponibilità, facendolo continuamente avanzare. Per ulteriori informazioni, vedere Always on Failover Cluster Instances

In uno scenario di spedizione del log, il server primario invia il log delle transazioni attivo del database primario a una o più destinazioni., Ogni server secondario ripristina il registro nel database secondario locale. Per ulteriori informazioni, vedere Informazioni sulla spedizione dei log.

In uno scenario di mirroring del database, ogni aggiornamento a un database, il database principale, viene immediatamente riprodotto in una copia completa separata del database, il database mirror. L’istanza del server principale invia immediatamente ogni record di registro all’istanza del server mirror, che applica i record di registro in entrata al database mirror, facendolo continuamente avanzare. Per ulteriori informazioni, vedere Mirroring del database.,

Caratteristiche del log delle transazioni

Caratteristiche del motore di database SQL Server log delle transazioni:

• Il log delle transazioni viene implementato come file separato o insieme di file nel database. La cache di log viene gestita separatamente dalla cache buffer per le pagine di dati, il che si traduce in codice semplice, veloce e robusto all’interno del motore di database di SQL Server. Per ulteriori informazioni, vedere Architettura fisica del registro delle transazioni.

• Il formato dei record di registro e delle pagine non è vincolato a seguire il formato delle pagine di dati.,

• Il log delle transazioni può essere implementato in diversi file. I file possono essere definiti per espandersi automaticamente impostando il valoreFILEGROWTH per il log. Ciò riduce il potenziale di esaurimento dello spazio nel log delle transazioni, riducendo allo stesso tempo il sovraccarico amministrativo. Per ulteriori informazioni, vedere ALTER DATABASE (Transact-SQL) File e Filegroup Opzioni.

• Il meccanismo per riutilizzare lo spazio all’interno dei file di registro è rapido e ha un effetto minimo sul throughput delle transazioni.,

Per informazioni sull’architettura e gli interni del log delle transazioni, consultare la Guida all’architettura e alla gestione del log delle transazioni di SQL Server.

Troncamento del log delle transazioni

Il troncamento del log consente di liberare spazio nel file di log per il riutilizzo da parte del log delle transazioni. È necessario troncare regolarmente il registro delle transazioni per evitare che riempia lo spazio assegnato. Diversi fattori possono ritardare il troncamento del registro, quindi il monitoraggio delle dimensioni del registro è importante. Alcune operazioni possono essere registrate minimamente per ridurre il loro impatto sulla dimensione del log delle transazioni.,

Log truncation elimina i file di log virtuali inattivi (VLFS) dal log delle transazioni logiche di un database SQL Server, liberando spazio nel log logico per il riutilizzo dal log delle transazioni fisiche. Se un log delle transazioni non viene mai troncato, alla fine riempirà tutto lo spazio su disco assegnato ai file di log fisici.

Per evitare di esaurire lo spazio, a meno che il troncamento del log non venga ritardato per qualche motivo, il troncamento si verifica automaticamente dopo i seguenti eventi:

• Sotto il modello di ripristino semplice, dopo un checkpoint.,
• Nel modello di ripristino completo o nel modello di ripristino registrato in blocco, se si è verificato un checkpoint dal backup precedente, il troncamento si verifica dopo un backup del registro (a meno che non si tratti di un backup del registro di sola copia).

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

Fattori che possono ritardare il troncamento del log

Quando i record di log rimangono attivi per un lungo periodo, il troncamento del log delle transazioni viene ritardato e il log delle transazioni può riempirsi, come accennato in precedenza in questo lungo argomento.,

In realtà, il troncamento del registro può essere ritardato da una serie di motivi. Scopri cosa, se non altro, impedisce il troncamento del log interrogando le colonne log_reuse_wait e log_reuse_wait_desc del sys.database vista catalogo. La tabella seguente descrive i valori di queste colonne.,

log_reuse_wait valore	log_reuse_wait_desc valore	Descrizione
0	NULLA	Attualmente ci sono uno o più file di log virtuali riutilizzabili (Vlf).
1	CHECKPOINT	Non si è verificato alcun checkpoint dall’ultimo troncamento del log, o la testa del log non si è ancora spostata oltre un file di log virtuale (VLF). (Tutti i modelli di recupero) Questo è un motivo di routine per ritardare il troncamento del registro., Per ulteriori informazioni, vedere Checkpoint del database (SQL Server).
2	LOG_BACKUP	È necessario un backup del log prima che il log delle transazioni possa essere troncato. (Solo modelli di ripristino completi o registrati in blocco) Quando viene completato il backup del registro successivo, alcuni spazi di registro potrebbero diventare riutilizzabili.
3	ACTIVE_BACKUP_OR_RESTORE	È in corso un backup dei dati o un ripristino (tutti i modelli di ripristino). Se un backup dei dati impedisce il troncamento del registro, l’annullamento dell’operazione di backup potrebbe aiutare il problema immediato.,
4	ACTIVE_TRANSACTION	Una transazione è attiva (tutti i modelli di ripristino): Una transazione di lunga durata potrebbe esistere all’inizio del backup del log. In questo caso, liberare lo spazio potrebbe richiedere un altro backup del registro. Si noti che le transazioni di lunga durata impediscono il troncamento del log in tutti i modelli di ripristino, incluso il modello di ripristino semplice, in base al quale il log delle transazioni viene generalmente troncato su ciascun checkpoint automatico. Una transazione è differita., Una transazione differita è effettivamente una transazione attiva il cui rollback è bloccato a causa di alcune risorse non disponibili. Per informazioni sulle cause delle transazioni differite e su come spostarle dallo stato differito, vedere Transazioni differite (SQL Server). Le transazioni di lunga durata potrebbero anche riempire il registro delle transazioni di tempdb. Tempdb viene utilizzato implicitamente dalle transazioni degli utenti per oggetti interni come tabelle di lavoro per l’ordinamento, file di lavoro per l’hashing, tabelle di lavoro del cursore e controllo delle versioni delle righe., Anche se la transazione utente include solo dati di lettura (SELECT query), gli oggetti interni possono essere creati e utilizzati in transazioni utente. Quindi il registro delle transazioni tempdb può essere riempito.
5	DATABASE_MIRRORING	Il mirroring del database è in pausa, o in modalità ad alte prestazioni, il database mirror è significativamente indietro rispetto al database principale. (Solo modello di ripristino completo) Per ulteriori informazioni, vedere Mirroring del database (SQL Server).,
6	REPLICA	Durante le repliche transazionali, le transazioni relative alle pubblicazioni non vengono ancora consegnate al database di distribuzione. (Solo modello di ripristino completo) Per informazioni sulla replica transazionale, vedere Replica di SQL Server.
7	DATABASE_SNAPSHOT_CREATION	Viene creata un’istantanea del database. (Tutti i modelli di ripristino) Questa è una causa di routine, e in genere breve, del troncamento del registro ritardato.
8	LOG_SCAN	Si sta verificando una scansione del log., (Tutti i modelli di ripristino) Questa è una causa di routine, e in genere breve, del troncamento del registro ritardato.
9	AVAILABILITY_REPLICA	Una replica secondaria di un gruppo di disponibilità sta applicando i record del log delle transazioni di questo database a un database secondario corrispondente. (Modello di ripristino completo) Per ulteriori informazioni, vedere Panoramica dei gruppi di disponibilità sempre attivi (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 di checkpoint indiretti, la pagina più recente del database potrebbe essere più vecchio del registro di checkpoint numero di sequenza (LSN). In questo caso, la pagina più vecchia può ritardare il troncamento del registro. (Tutti i modelli di ripristino) Per informazioni sui checkpoint indiretti, vedere Checkpoint 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 eseguito un checkpoint automatico quando il file di log delle transazioni diventa più grande di 1.,5 GB a partire dall’ultimo checkpoint (include sia basato su disco e di memoria ottimizzato tabelle) Per ulteriori informazioni, consultare Operazione di Checkpoint per la Memoria Ottimizzato le Tabelle e (https://blogs.msdn.microsoft.com/sqlcat/2016/05/20/logging-and-checkpoint-process-for-memory-optimized-tables-2/)

le Operazioni che possono essere minimamente connesso

la registrazione Minima implica la registrazione solo le informazioni che è necessario per recuperare la transazione senza il supporto del punto di ripristino in un momento., Questo argomento identifica le operazioni che vengono registrate in modo minimo nel modello di ripristino registrato in blocco (nonché nel modello di ripristino semplice, tranne quando è in esecuzione un backup).

Le seguenti operazioni, che sono completamente registrate sotto il modello di ripristino completo, sono minimamente registrate sotto il modello di ripristino semplice e registrato in blocco:

• Operazioni di importazione in blocco (bcp, BULK INSERT e INSERT… SELEZIONARE)., Per ulteriori informazioni su quando l’importazione di massa in una tabella viene registrata in modo minimo, vedere Prerequisiti per la registrazione minima nell’importazione di massa.

Quando la replica transazionale è abilitata,BULK INSERT le operazioni vengono registrate completamente anche con il modello di ripristino registrato in blocco.

• SELEZIONARE IN operazioni.

Quando la replica transazionale è abilitata,SELECT INTO le operazioni vengono registrate completamente anche con il modello di ripristino registrato in blocco.,

• Aggiornamenti parziali a tipi di dati di grande valore, utilizzando la clausola.WRITE nell’istruzione UPDATE quando si inseriscono o si aggiungono nuovi dati. Si noti che la registrazione minima non viene utilizzata quando vengono aggiornati i valori esistenti. Per ulteriori informazioni sui tipi di dati di grande valore, vedere Tipi di dati (Transact-SQL).

• Istruzioni WRITETEXT e UPDATETEXT quando si inseriscono o si aggiungono nuovi dati nelle colonne text, ntext e image data type. Si noti che la registrazione minima non viene utilizzata quando vengono aggiornati i valori esistenti.,

  Warning

  Le istruzioni WRITETEXTe UPDATETEXT sono deprecate; evitare di utilizzarle in nuove applicazioni.

• Se il database è impostato sul modello di ripristino semplice o bulk-logged, alcune operazioni DDL indice vengono registrati in minima parte se l’operazione viene eseguita offline o online. Le operazioni di indice registrate in modo minimo sono le seguenti:

  • CREA operazioni di INDICE (incluse le viste indicizzate).

  • ALTER INDEX REBUILD o operazioni DBCC DBREINDEX.,

    Warning

    L’istruzione DBCC DBREINDEX è deprecata; non usarla in nuove applicazioni.

    Nota

    Le operazioni di compilazione dell’indice utilizzano la registrazione minimale ma possono essere ritardate quando c’è un backup in esecuzione simultanea. Questo ritardo è causato dai requisiti di sincronizzazione delle pagine del pool di buffer registrate in modo minimo quando si utilizza il modello di ripristino registrato in blocco o semplice.

  • DROP INDEX nuovo heap rebuild (se applicabile). La deallocazione della pagina indice durante un’operazioneDROP INDEX è sempre completamente registrata.,n danneggiamento del Database (SQL Server)

  Ripristino del Log delle Transazioni (Modello di Recupero Completo)

  • il Ripristino di un Backup del Log delle Transazioni (SQL Server)

  Vedi anche

  SQL Server Log delle Transazioni Architettura e Gestione Guida
  Controllare la Durata delle Transazioni
  Prerequisiti per la Registrazione Minima nell’Importazione di Massa
  Backup e Ripristino di Database di SQL Server
  di Ripristino e di Recupero Panoramica (SQL Server)
  Checkpoint di Database (SQL Server)
  Visualizzare o Modificare le Proprietà di un Database
  Modelli di Recupero (SQL Server)
  Backup del Log delle Transazioni (SQL Server)
  sys.,per ulteriori informazioni, consultare il sito .