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 simile, per informazioni sulla gestione delle dimensioni dei file di log delle transazioni in Istanza gestita di SQL di Azure, vedere Gestire lo spazio 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

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 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 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 archiviare i 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.

Diagramma che illustra le dimensioni dei concetti relativi allo spazio del database di differenza nella tabella della quantità di 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, sulle dimensioni massime e sull'opzione di 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 i 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 i file di resoconto che aumentano a causa di operazioni aziendali regolari e ricorrenti non richiedono operazioni di compattazione. I comandi di compattazione influiscono sulle prestazioni del database durante l'esecuzione; devono essere eseguiti durante periodi di basso utilizzo. 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; vedere 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 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 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 essere possibile solo dopo il troncamento del log successivo.

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

Gli indici potrebbero diventare frammentati dopo il completamento di un'operazione di compattazione rispetto ai file di dati. 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 ricompilazione dell'indice richiedono spazio libero nel database e quindi potrebbero aumentare lo spazio allocato, 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 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. In questo modo il log può crescere.

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.

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

Consigli

Di seguito sono riportati alcuni consigli generali quando si stiano usando 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 indicatore per il dimensionamento corretto di un log delle transazioni è la quantità di spazio del log occupato 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.
    • Il tempo necessario per eseguire il batch più grande in un database.

  • Quando si imposta l'aumento automatico per i file di dati e di log usando l'opzione FILEGROWTH , potrebbe essere preferibile impostarlo in dimensioni anziché percentuale per consentire un migliore controllo del rapporto di crescita, poiché 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 di aumento 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:

    • 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 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 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 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. Non è tuttavia consigliabile e la proprietà del database auto_shrink è impostata su 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. - Il file viene ridotto alla dimensione in cui solo il 25% del file è inutilizzato o alle dimensioni originali del file, a qualsiasi livello di dimensioni maggiori. - Per informazioni sulla modifica dell'impostazione della proprietà auto_shrink, vedere Visualizzare o modificare le proprietà di un database e opzioni ALTER DATABASE SET (Transact-SQL).

Contenuto correlato