Gestire le dimensioni del file di log delle transazioni

  • Articolo

Si applica a:SQL Server

Questo argomento descrive come monitorare le dimensioni di un log delle transazioni di SQL Server, compattare il log delle transazioni, aumentare le dimensioni di un file di log delle transazioni, ottimizzare la velocità di aumento del log delle transazioni tempdb e controllare l'aumento delle dimensioni di un file di log delle transazioni.

Monitoraggio dell'utilizzo dello spazio del log

Monitorare l'uso dello spazio del log tramite sys.dm_db_log_space_usage. Questo DMV restituisce informazioni sulla quantità di spazio del log attualmente usata e indica quando il log delle transazioni deve essere troncato.

Per informazioni sulle dimensioni correnti di un file di log, sulle relative dimensioni massime e sull'opzione di aumento automatico delle dimensioni per il file, è anche possibile usare le colonne size, max_size e growth per il file di log in sys.database_files.

Importante

Evitare l'overload del disco del log. Verificare che la risorsa di archiviazione log sia in grado di sostenere i requisiti IOPS e di bassa latenza per il carico di lavoro transazionale.

Compattare il file di log

Per ridurre la dimensione fisica di un file di log fisico, è necessario ridurre il file di log. Ciò può risultare utile quando si sa che un file di log delle transazioni contiene spazio inutilizzato. È possibile compattare un file di log solo mentre il database è online ed è disponibile almeno un file di log virtuale (VLF). In alcuni casi, la compattazione del log potrebbe non essere possibile finché il log non viene troncato.

Nota

Fattori come una transazione con esecuzione prolungata, che mantiene i VLF virtuali attivi per un lungo periodo di tempo, possono limitare in tutto o in parte la compattazione del log. Per informazioni, vedere Fattori che possono posticipare il troncamento del log.

Il processo di compattazione di un file di log comporta la rimozione di uno o più VLF che non contengono alcuna parte del log logico, ovvero dei VLF inattivi. Quando si compatta un file di log delle transazioni vengono rimossi dalla fine del file di log alcuni file VLF inattivi, per ridurre il log approssimativamente alle dimensioni della destinazione.

Importante

Prima di ridurre le dimensioni del log delle transazioni, tenere presenti i fattori che possono posticipare il troncamento del log. Se dopo una compattazione del log è di nuovo necessario lo spazio di archiviazione, il log delle transazioni torna a crescere e durante tale crescita origina un overhead delle prestazioni. Per altre informazioni, vedere Indicazioni in questo argomento.

Compattare un file di log senza compattare i file di database

Monitorare gli eventi di compattazione dei file di log

Monitorare lo spazio del log

Aggiungere o aumentare le dimensioni di un file di log

È possibile guadagnare spazio aumentando le dimensioni del file di log esistente, se lo spazio è sufficiente, o aggiungendo un file di log al database, in genere in un disco diverso. Un file di log delle transazioni è sufficiente, a meno che lo spazio del log sia in esaurimento e anche lo spazio su disco sia in esaurimento nel volume che contiene il file di log.

  • Per aggiungere un file di log al database, usare la clausola ADD LOG FILE dell'istruzione ALTER DATABASE. L'aggiunta di un file di log consente l'aumento delle dimensioni del log.
  • Per espandere il file di log usare la clausola MODIFY FILE dell'istruzione ALTER DATABASE specificando la sintassi SIZE e MAXSIZE. Per altre informazioni, vedere Opzioni per file e filegroup ALTER DATABASE (Transact-SQL).

Per altre informazioni, vedere Indicazioni in questo argomento.

Ottimizzare le dimensioni del log delle transazioni di tempdb

Il riavvio di un'istanza del server riporta il log delle transazioni del database tempdb alle dimensioni originali, antecedenti all'aumento automatico delle dimensioni. Questo può comportare una riduzione delle prestazioni del log delle transazioni di tempdb .

Per evitare tale overhead, è possibile incrementare le dimensioni del log delle transazioni di tempdb dopo l'avvio o il riavvio dell'istanza del server. Per altre informazioni, vedere tempdb Database.

Controllare l'aumento delle dimensioni di un file di log delle transazioni

Per gestire la crescita di un file di log delle transazioni, usare l'istruzione descritta in Opzioni per file e filegroup ALTER DATABASE (Transact-SQL). Tenere presente quanto segue:

  • Per modificare le dimensioni del file corrente in unità KB, MB, GB e TB usare l'opzione SIZE.
  • Per modificare l'incremento di crescita, usare l'opzione FILEGROWTH. Il valore 0 indica che l'aumento automatico delle dimensioni è disattivato e non è consentita l'allocazione di spazio aggiuntivo.
  • Per controllare le dimensioni massime di un file di log in unità KB, MB, GB e TB o per impostare la crescita su UNLIMITED, usare l'opzione MAXSIZE.

Per altre informazioni, vedere Indicazioni in questo argomento.

Consigli

Di seguito sono elencate alcune indicazioni di carattere generale relative all'uso dei file registro transazioni:

  • L'incremento automatico (autogrow) delle dimensioni del log delle transazioni, definito dall'opzione FILEGROWTH, deve essere sufficiente a soddisfare le esigenze delle transazioni del carico di lavoro. È consigliabile specificare un incremento di crescita per un file di log sufficientemente grande da consentire di evitare l'espansione frequente. Un buon indicatore per il dimensionamento corretto di un log delle transazioni è la quantità di spazio del log occupato durante:

    • Il tempo necessario per l'esecuzione di un backup completo, perché i backup del log non possono verificarsi finché non termina il backup completo.
    • Il tempo necessario per le operazioni di manutenzione dell'indice più grande.
    • Il tempo necessario per eseguire il batch più grande in un database.

  • Quando si imposta autogrow per i file di dati e di log usando l'opzione FILEGROWTH può essere preferibile impostare size (dimensioni) anziché percentage (percentuale), per consentire un controllo migliore del rapporto di crescita, dato che percentage corrisponde a un valore in continua crescita.

    • Nelle versioni precedenti a SQL Server 2022 (16.x), i log delle transazioni non possono sfruttare l'inizializzazione immediata dei file, quindi i tempi di crescita dei log estesi sono particolarmente critici.
    • A partire da SQL Server 2022 (16.x) (tutte le edizioni) e in Azure SQL Database, l'inizializzazione immediata dei file può trarre vantaggio da eventi di crescita dei log delle transazioni fino a 64 MB. L'incremento predefinito delle dimensioni di crescita automatica per i nuovi database è di 64 MB. L'inizializzazione automatica dei file di log delle transazioni è maggiore di 64 MB.
    • Come procedura consigliata, evitare di impostare un valore dell'opzione FILEGROWTH superiore a 1024 MB per i log delle transazioni. I valori predefiniti dell'opzione FILEGROWTH sono:
    Versione Valori predefiniti
    A partire da SQL Server 2016 (13.x) Dati 64 MB. File di log 64 MB.
    A partire da SQL Server 2005 (9.x) Dati 1 MB. File di log 10%.
    Prima di SQL Server 2005 (9.x) Dati 10%. File di log 10%.

  • Un incremento della crescita ridotto può generare molti file VLF piccoli e ridurre le prestazioni. Per determinare la distribuzione dei file di log virtuali ottimale per le dimensioni correnti del log delle transazioni di tutti i database in un'istanza specifica e gli incrementi della crescita necessari per ottenere le dimensioni richieste, vedere questo script.

  • Un incremento della crescita elevato può generare pochi file VLF di grandi dimensioni e ridurre a sua volta le prestazioni. Per determinare la distribuzione dei file di log virtuali ottimale per le dimensioni correnti del log delle transazioni di tutti i database in un'istanza specifica e gli incrementi della crescita necessari per ottenere le dimensioni richieste, vedere questo script.

  • Anche con l'aumento automatico attivato è possibile che si riceva un messaggio indicante che il log delle transazioni è pieno, se questo non può crescere a sufficienza per soddisfare le esigenze della query. Per altre informazioni su come modificare l'incremento della crescita, vedere Opzioni per file e filegroup ALTER DATABASE (Transact-SQL)

  • La presenza di più file di log in un database non migliora le prestazioni, perché i file di log delle transazioni non usano il riempimento proporzionale come i file di dati nello stesso gruppo di file.

  • È possibile impostare i file di log in modo che vengano compattati automaticamente. Tuttavia questa impostazione è sconsigliata e la proprietà del database auto_shrink è FALSE per impostazione predefinita. Se auto_shrink è impostata su TRUE, la compattazione automatica riduce le dimensioni di un file solo quando più del 25% dello spazio del file risulta inutilizzato.

Vedi anche

BACKUP (Transact-SQL)
Risolvere i problemi relativi a un log delle transazioni completo (Errore di SQL Server 9002)
Backup del log delle transazioni nella Guida sull'architettura e gestione del log delle transazioni in SQL Server
Backup di log delle transazioni (SQL Server)
Opzioni per file e filegroup ALTER DATABASE (Transact-SQL)