Gestione delle 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 il tasso di crescita del log delle transazioni di tempdb e controllare l'aumento delle dimensioni di un file registro transazioni.

Questo articolo è relativo a SQL Server. Anche se molto simile, per informazioni sulla gestione delle dimensioni dei file di log delle transazioni in Istanza gestita di SQL di Azure, vedere Gestire lo spazio dei file per i database in Istanza gestita di SQL di Azure. Per altre informazioni sul database SQL di Azure, vedere Gestione di uno spazio file per i database nel database SQL di Azure.

Informazioni sui tipi di spazio di archiviazione per un database

La comprensione delle quantità di spazio di archiviazione seguenti è importante per la gestione dello spazio file di un database.

Quantità di database Definizione Commenti
Spazio dati usato La quantità di spazio usato per archiviare i dati del database. In genere, lo spazio usato aumenta quando vengono inseriti i dati e diminuisce quando vengono eliminati. In alcuni casi, lo spazio usato non cambia in caso di inserimenti o eliminazioni, a seconda della quantità e del modello di dati coinvolti nell'operazione e dell'eventuale frammentazione. Ad esempio, se si elimina una riga da ogni pagina di dati, non si riduce necessariamente lo spazio usato.
Spazio dati allocato Quantità di spazio file formattato messo a disposizione per l'archiviazione dei dati del database. La quantità di spazio allocato aumenta automaticamente, ma non diminuisce mai dopo le eliminazioni. Questo comportamento assicura che gli inserimenti futuri avvengano più velocemente, perché non è necessario riformattare lo spazio.
Spazio dati allocato ma non usato Differenza tra la quantità di spazio dati allocato e lo spazio dati usato. Questa quantità rappresenta la quantità massima di spazio libero che può essere recuperata compattando i file di dati del database.
Dimensioni massime dei dati Quantità massima di spazio che può essere usata per l'archiviazione dei dati del database. La quantità di spazio dati allocato non può superare le dimensioni massime dei dati.

Il diagramma seguente illustra la relazione tra i diversi tipi di spazio di archiviazione per un database.

Diagram that demonstrates the size of difference database space concepts in the database quantity table.

Eseguire una query su un database singolo per ottenere informazioni sullo spazio dei file

Usare la query seguente per restituire la quantità di spazio dati di database allocato e la quantità di spazio non usato allocato. L'unità di misura dei risultati di query è costituita da MB.

-- Connect to a user database
SELECT file_id, type_desc,
       CAST(FILEPROPERTY(name, 'SpaceUsed') AS decimal(19,4)) * 8 / 1024. AS space_used_mb,
       CAST(size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS decimal(19,4)) AS space_unused_mb,
       CAST(size AS decimal(19,4)) * 8 / 1024. AS space_allocated_mb,
       CAST(max_size AS decimal(19,4)) * 8 / 1024. AS max_size_mb
FROM sys.database_files;

Monitorare l'uso 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 i file di log

Per ridurre le dimensioni fisiche di un file di log fisico restituendo spazio disponibile nel file nel sistema operativo, compattare il file di log. Una compattazione fa la differenza solo quando un file registro transazioni contiene spazio inutilizzato.

Se il file di log è pieno, probabilmente a causa di transazioni aperte, esaminare cosa impedisce il troncamento del log delle transazioni.

Attenzione

Le operazioni di compattazione non devono essere considerate un'operazione di manutenzione regolare. I file di dati e di log che aumentano a causa di operazioni aziendali regolari e ricorrenti non richiedono operazioni di compattazione. I comandi di compattazione influiscono sulle prestazioni del database mentre è in esecuzione e, se possibile, dovrebbero essere eseguiti in periodi di utilizzo ridotto. Non è consigliabile compattare i file di dati se il normale carico di lavoro dell'applicazione causa l'aumento delle dimensioni dei file fino a raggiungere le stesse dimensioni allocate.

Tenere presente il potenziale impatto negativo sulle prestazioni della compattazione dei file di database, come descritto in Manutenzione dell'indice dopo la compattazione.

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 ulteriori informazioni, consultare Suggerimenti.

È 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.

Fattori come una transazione con esecuzione prolungata, che mantiene i VLF 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 registro transazioni vengono rimossi dalla fine del file di log alcuni file VLF inattivi, per ridurre il log approssimativamente alle dimensioni della destinazione.

Per ulteriori informazioni sulle operazioni di compattazione, esaminare i collegamenti seguenti:

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

Manutenzione dell'indice dopo la compattazione

Al termine di un'operazione di compattazione rispetto ai file di dati, gli indici possono essere frammentati. In questo modo si riduce l'efficacia dell'ottimizzazione delle prestazioni per determinati carichi di lavoro, ad esempio le query che usano analisi di grandi dimensioni. Se si verifica una riduzione del livello delle prestazioni dopo il completamento dell'operazione di compattazione, prendere in considerazione la possibilità di eseguire la manutenzione degli indici ricostruendoli. Tenere presente che le ricompilazioni dell'indice richiedono spazio disponibile nel database e pertanto lo spazio allocato può aumentare, contrastando l'effetto della compattazione.

Per altre informazioni sulla manutenzione degli indici, vedere Ottimizzare la manutenzione degli indici per migliorare le prestazioni delle query e ridurre il consumo di risorse.

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 ulteriori informazioni, consultare Suggerimenti.

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). Nota 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 ulteriori informazioni, consultare Suggerimenti.

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 usare 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 database SQL di Azure, l'inizializzazione immediata dei file può trarre vantaggio dagli eventi di crescita del log delle transazioni fino a 64 MB. L'incremento predefinito delle dimensioni di crescita automatica per i nuovi database è di 64 MB. Gli eventi di aumento automatico dei file registro transazioni di dimensioni superiori a 64 MB non possono trarre vantaggio dall'inizializzazione immediata dei file.

    • 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 per l'analisi e la correzione di VLF forniti dal team di SQL Tiger.

  • Un aumento automatico di grandi dimensioni può causare due problemi:

    • Un aumento automatico di grandi dimensioni può causare la sospensione del database mentre viene allocato il nuovo spazio, causando potenzialmente timeout delle query.
    • 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 per l'analisi e la correzione di VLF forniti dal team di SQL Tiger.

  • 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.

Passaggi successivi