Condividi tramite

Gestione delle dimensioni del file di log delle transazioni

Si applica a:SQL Server

Questo articolo descrive come monitorare le dimensioni del log delle transazioni di SQL Server, compattare il log delle transazioni, aggiungere o ingrandire un file di log delle transazioni, ottimizzare il tempdb tasso di crescita del log delle transazioni e controllare l'aumento di un file di log delle transazioni.

Questo articolo è relativo a SQL Server. Anche se il processo è simile, per la gestione dello spazio file in Azure SQL, vedere:

Informazioni sui tipi di spazio di archiviazione per un database

Comprendere le quantità di spazio di archiviazione seguenti è importante per gestire lo spazio file di un database.

Quantità di database Definizione Commenti
Spazio dati usato Spazio utilizzato per archiviare i dati del database. In genere, lo spazio usato aumenta sugli inserimenti e diminuisce sulle eliminazioni. 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 in qualsiasi frammentazione. Ad esempio, se si elimina una riga da ogni pagina di dati, non si riduce necessariamente lo spazio usato.
Spazio dati allocato Spazio file formattato reso disponibile per l'archiviazione dei dati del database. La quantità di spazio allocato aumenta automaticamente, ma non diminuisce mai dopo le eliminazioni. Questo comportamento garantisce che gli inserimenti futuri siano più veloci perché non è necessario riformattare lo spazio.
Spazio dati allocato ma non usato Differenza tra la quantità allocata e lo spazio dati usato. Questa quantità rappresenta lo spazio massimo disponibile che consente di recuperare i file di dati del database.
Dimensioni massime dei dati Quantità massima di spazio 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 le relazioni tra i diversi tipi di spazio di archiviazione per un database.

Diagramma che illustra le relazioni tra i diversi tipi di spazio di archiviazione per un database.

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 del file di log, le dimensioni massime e l'opzione aumento automatico per il file, è anche possibile usare le sizecolonne , max_sizee 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 un file di log

Compattare il file di log per ridurre le dimensioni fisiche restituendo spazio libero al sistema operativo. 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 normali operazioni aziendali ricorrenti non richiedono operazioni di compattazione. I comandi di compattazione influiscono sulle prestazioni del database durante l'esecuzione. Devono essere eseguiti durante i periodi di basso utilizzo. Non è consigliabile compattare i file di dati se un normale carico di lavoro dell'applicazione causerà nuovamente l'aumento delle dimensioni allocate dei file.

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

Prima di compattare il log delle transazioni, tenere presente i fattori che possono ritardare il troncamento del log. Se lo spazio di archiviazione è necessario di nuovo dopo la compattazione di un log, il log delle transazioni crescerà di nuovo, introducendo un sovraccarico delle prestazioni durante le operazioni di crescita dei log. Per altre informazioni, vedere Raccomandazioni.

È possibile compattare un file di log solo mentre il database è online e almeno un file di log virtuale (VLF) è gratuito. In alcuni casi, la compattazione del log potrebbe essere possibile solo dopo il troncamento del log successivo.

Alcuni fattori, ad esempio una transazione a esecuzione prolungata, possono mantenere attivi i VVL per un periodo prolungato, possono limitare la compattazione del log o persino impedire la compattazione del log. Per altre 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 altre informazioni sulle operazioni di compattazione, vedere le risorse 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 un'operazione di compattazione

Gli indici potrebbero diventare frammentati dopo il completamento di un'operazione di compattazione rispetto ai file di dati. Questa frammentazione 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 ricompilazione dell'indice richiedono spazio libero nel database e quindi potrebbe aumentare lo spazio allocato, contrastando l'effetto dell'operazione di compattazione.

Per altre informazioni, vedere Ottimizzare la manutenzione degli indici per migliorare le prestazioni delle query e ridurre l'utilizzo delle risorse.

Aggiungere o aumentare le dimensioni di un file di log

È possibile ottenere spazio ampliando il file di log esistente (se lo spazio su disco è consentito) 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 non esaurisca e che lo spazio su disco si esaurisca anche 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. Questa azione consente all'aumento 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 di file e filegroup ALTER DATABASE (Transact-SQL).

Per altre informazioni, vedere Raccomandazioni.

Ottimizzare le dimensioni del log delle transazioni di tempdb

Il riavvio di un'istanza del server ridimensiona il log delle transazioni del tempdb database alle dimensioni originali di aumento automatico. Questo ridimensionamento può ridurre le prestazioni del tempdb log delle transazioni.

È possibile evitare questo sovraccarico aumentando le dimensioni del tempdb log delle transazioni dopo l'avvio o il riavvio dell'istanza del server. Per altre informazioni, vedere Database Tempdb.

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

Usare l'istruzione delle opzioni del file e del filegroup ALTER DATABASE (Transact-SQL) per gestire l'aumento di un file di log delle transazioni. Notare quanto segue:

  • Usare l'opzione SIZE per modificare le dimensioni correnti del file in UNITÀ KB, MB, GB e TB.
  • Per modificare l'incremento di crescita, usare l'opzione FILEGROWTH. Il valore 0 indica che la crescita automatica è disattivata e non è consentito spazio aggiuntivo. Usare l'opzione MAXSIZE per controllare le dimensioni massime di un file di log in UNITÀ KB, MB, GB e TB o per impostare la crescita su UNLIMITED.

Per altre informazioni, vedere Raccomandazioni.

Consigli

Di seguito sono riportati alcuni consigli generali da considerare quando si lavora con i file di log delle transazioni:

  • L'incremento automatico (aumento automatico) del log delle transazioni, impostato dall'opzione FILEGROWTH , deve essere sufficientemente grande per rimanere al passo con 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 suggerimento per ridimensionare correttamente un log delle transazioni consiste nel monitorare la quantità di log occupata durante:

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

  • Quando si imposta l'aumento automatico dei file di dati e di log usando l'opzione FILEGROWTH , potrebbe essere preferibile impostarlo in dimensioni anziché percentuale per consentire un controllo migliore del rapporto di crescita, perché una percentuale è una quantità 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 aumento automatico per i nuovi database è 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, non impostare il valore dell'opzione FILEGROWTH superiore a 1.024 MB per i log delle transazioni. I valori predefiniti per l'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 piccolo incremento dell'aumento automatico può generare troppi file VVL di piccole dimensioni e ridurre le prestazioni. Per determinare la distribuzione VLF ottimale per le dimensioni correnti del log delle transazioni di tutti i database in una determinata istanza e gli incrementi di crescita necessari per ottenere le dimensioni necessarie, vedere questo script per l'analisi e la correzione delle VVL, fornite dal team SQL Tiger.

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

    • Può causare la sospensione del database durante l'allocazione del nuovo spazio, causando potenzialmente timeout delle query.
    • Può generare VLF troppo pochi e grandi e può influire anche sulle prestazioni. Per determinare la distribuzione VLF ottimale per le dimensioni correnti del log delle transazioni di tutti i database in una determinata istanza e gli incrementi di crescita necessari per ottenere le dimensioni necessarie, vedere questo script per l'analisi e la correzione delle VVL, fornite dal team SQL Tiger.

  • Anche con l'aumento automatico abilitato, è possibile ricevere un messaggio che indica che il log delle transazioni è pieno se non può crescere abbastanza velocemente per soddisfare le esigenze della query. Per altre informazioni sulla modifica dell'incremento di crescita, vedere Opzioni di file e filegroup ALTER DATABASE (Transact-SQL).

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

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

Contenuto correlato

  • Last updated on