Gestire le dimensioni del file di log delle transazioniManage the size of the transaction log file

QUESTO ARGOMENTO SI APPLICA A: SìSQL ServernonDatabase SQL di AzurenonAzure SQL Data Warehouse non Parallel Data WarehouseTHIS TOPIC APPLIES TO: yesSQL ServernoAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Questo argomento descrive come monitorare le dimensioni di un log delle transazioni di SQL ServerSQL 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.This topic covers how to monitor SQL ServerSQL Server transaction log size, shrink the transaction log, add to or enlarge a transaction log file, optimize the tempdb transaction log growth rate, and control the growth of a transaction log file.

Monitorare l'uso dello spazio del logMonitor log space use

Monitorare l'uso dello spazio del log tramite sys.dm_db_log_space_usage.Monitor log space use by using 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.This DMV returns information about the amount of log space currently used, and indicates when the transaction log needs truncation.

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.For information about the current log file size, its maximum size, and the autogrow option for the file, you can also use the size, max_size, and growth columns for that log file in sys.database_files.

Importante

Evitare l'overload del disco del log.Avoid overloading the log disk. 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.Ensure the log storage can withstand the IOPS and low latency requirements for your transactional load.

Compattare il file di logShrink log file size

Per ridurre la dimensione fisica di un file di log fisico, è necessario ridurre il file di log.To reduce the physical size of a physical log file, you must shrink the log file. Ciò può risultare utile quando si sa che un file di log delle transazioni contiene spazio inutilizzato.This is useful when you know that a transaction log file contains unused space. È possibile compattare un file di log solo mentre il database è online ed è disponibile almeno un file di log virtuale (VLF).You can shrink a log file only while the database is online, and at least one virtual log file (VLF) is free. In alcuni casi, la compattazione del log potrebbe non essere possibile finché il log non viene troncato.In some cases, shrinking the log may not be possible until after the next log truncation.

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.Factors such as a long-running transaction, that keep VLFs active for an extended period, can restrict log shrinkage or even prevent the log from shrinking at all. Per informazioni, vedere Fattori che possono posticipare il troncamento del log.For information, see Factors that can delay log truncation.

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.Shrinking a log file removes one or more VLFs that hold no part of the logical log (that is, inactive VLFs). 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.When a transaction log file is shrunk, inactive VLFs are removed from the end of the log file to reduce the log to approximately the target size.

Importante

Prima di ridurre le dimensioni del log delle transazioni, tenere presenti i fattori che possono posticipare il troncamento del log.Before shrinking the transaction log, keep in mind Factors that can delay log truncation. 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.If the storage space is required again after a log shrink, the transaction log will grow again and by doing that, introduce performance overhead during log growth operations. Per altre informazioni, vedere Indicazioni in questo argomento.For more information, see the Recommendations in this topic.

Compattare un file di log senza compattare i file di databaseShrink a log file (without shrinking database files)

Aggiungere o aumentare le dimensioni di un file di logAdd or enlarge a log file

È 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.You can gain space by enlarging the existing log file (if disk space permits) or by adding a log file to the database, typically on a different disk. 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.One transaction log file is sufficient unless log space is running out, and disk space is also running out on the volume that holds the log file.

  • Per aggiungere un file di log al database, usare la clausola ADD LOG FILE dell'istruzione ALTER DATABASE.To add a log file to the database, use the ADD LOG FILE clause of the ALTER DATABASE statement. L'aggiunta di un file di log consente l'aumento delle dimensioni del log.Adding a log file allows the log to grow.
  • Per espandere il file di log usare la clausola MODIFY FILE dell'istruzione ALTER DATABASE specificando la sintassi SIZE e MAXSIZE.To enlarge the log file, use the MODIFY FILE clause of the ALTER DATABASE statement, specifying the SIZE and MAXSIZE syntax. Per altre informazioni, vedere Opzioni per file e filegroup ALTER DATABASE (Transact-SQL).For more information, see ALTER DATABASE (Transact-SQL) File and Filegroup options.

Per altre informazioni, vedere Indicazioni in questo argomento.For more information, see the Recommendations in this topic.

Ottimizzare le dimensioni del log delle transazioni di tempdbOptimize tempdb transaction log size

Il riavvio di un'istanza del server riporta il log delle transazioni del database tempdb alle dimensioni originali, antecedenti all'aumento automatico delle dimensioni.Restarting a server instance resizes the transaction log of the tempdb database to its original, pre-autogrow size. Questo può comportare una riduzione delle prestazioni del log delle transazioni di tempdb .This can reduce the performance of the tempdb transaction log.

Per evitare tale overhead, è possibile incrementare le dimensioni del log delle transazioni di tempdb dopo l'avvio o il riavvio dell'istanza del server.You can avoid this overhead by increasing the size of the tempdb transaction log after starting or restarting the server instance. Per altre informazioni, vedere tempdb Database.For more information, see tempdb Database.

Controllare l'aumento delle dimensioni di un file di log delle transazioniControl transaction log file growth

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).Use the ALTER DATABASE (Transact-SQL) File and Filegroup options statement to manage the growth of a transaction log file. Si noti quanto segue:Note the following:

  • Per modificare le dimensioni del file corrente in unità KB, MB, GB e TB usare l'opzione SIZE.To change the current file size in KB, MB, GB, and TB units, use the SIZE option.
  • Per modificare l'incremento di crescita, usare l'opzione FILEGROWTH.To change the growth increment, use the FILEGROWTH option. Il valore 0 indica che l'aumento automatico delle dimensioni è disattivato e non è consentita l'allocazione di spazio aggiuntivo.A value of 0 indicates that automatic growth is set to off and no additional space is permitted.
  • 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.To control the maximum the size of a log file in KB, MB, GB, and TB units or to set growth to UNLIMITED, use the MAXSIZE option.

Per altre informazioni, vedere Indicazioni in questo argomento.For more information, see the Recommendations in this topic.

IndicazioniRecommendations

Di seguito sono elencate alcune indicazioni di carattere generale relative all'uso dei file registro transazioni:Following are some general recommendations when you are working with transaction log files:

  • 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.The automatic growth (autogrow) increment of the transaction log, as set by the FILEGROWTH option, must be large enough to stay ahead of the needs of the workload transactions. È consigliabile specificare un incremento di crescita per un file di log sufficientemente grande da consentire di evitare l'espansione frequente.The file growth increment on a log file should be sufficiently large to avoid frequent expansion. Un buon indicatore per il dimensionamento corretto di un log delle transazioni è la quantità di spazio del log occupato durante:A good pointer to properly size a transaction log is monitoring the amount of log occupied during:

    • Il tempo necessario per l'esecuzione di un backup completo, perché i backup del log non possono verificarsi finché non termina il backup completo.The time required to execute a full backup, because log backups cannot occur until it finishes.
    • Il tempo necessario per le operazioni di manutenzione dell'indice più grande.The time required for the largest index maintenance operations.
    • Il tempo necessario per eseguire il batch più grande in un database.The time required to execute the largest batch in a 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.When setting autogrow for data and log files using the FILEGROWTH option, it might be preferred to set it in size instead of percentage, to allow better control on the growth ratio, as percentage is an ever-growing amount.

    • Tenere presente che i log delle transazioni non possono sfruttare Inizializzazione immediata dei file, quindi i tempi di crescita dei file di log estesi sono particolarmente importanti.Keep in mind that transaction logs cannot leverage Instant File Initialization, so extended log growth times are especially critical.
    • Come procedura consigliata, evitare di impostare un valore dell'opzione FILEGROWTH superiore a 1024 MB per i log delle transazioni.As a best practice, do not set the FILEGROWTH option value above 1,024 MB for transaction logs. I valori predefiniti dell'opzione FILEGROWTH sono:The default values for FILEGROWTH option are:

      VersioneVersion Valori predefinitiDefault values
      A partire da SQL Server 2016 (13.x)SQL Server 2016 (13.x)Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) Dati 64 MB.Data 64 MB. File di log 64 MB.Log files 64 MB.
      A partire da SQL Server 2005SQL Server 2005Starting with SQL Server 2005SQL Server 2005 Dati 1 MB.Data 1 MB. File di log 10%.Log files 10%.
      Prima di SQL Server 2005SQL Server 2005Prior to SQL Server 2005SQL Server 2005 Dati 10%.Data 10%. File di log 10%.Log files 10%.
  • Un incremento della crescita ridotto può generare molti file VLF piccoli e ridurre le prestazioni.A small growth increment can generate too many small VLFs and can reduce performance. 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.To determine the optimal VLF distribution for the current transaction log size of all databases in a given instance, and the required growth increments to achieve the required size, see this script.

  • Un incremento della crescita elevato può generare pochi file VLF di grandi dimensioni e ridurre a sua volta le prestazioni.A large growth increment can generate too few and large VLFs and can also affect performance. 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.To determine the optimal VLF distribution for the current transaction log size of all databases in a given instance, and the required growth increments to achieve the required size, see this 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.Even with autogrow enabled, you can receive a message that the transaction log is full, if it cannot grow fast enough to satisfy the needs of your query. Per altre informazioni su come modificare l'incremento della crescita, vedere Opzioni per file e filegroup ALTER DATABASE (Transact-SQL)For more information on changing the growth increment, see ALTER DATABASE (Transact-SQL) File and Filegroup options

  • 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.Having multiple log files in a database does not enhance performance in any way, because the transaction log files do not use proportional fill like data files in a same filegroup.

  • È possibile impostare i file di log in modo che vengano compattati automaticamente.Log files can be set to shrink automatically. Tuttavia questa impostazione è sconsigliata e la proprietà del database auto_shrink è FALSE per impostazione predefinita.However this is not recommended, and the auto_shrink database property is set to FALSE by default. 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.If auto_shrink is set to TRUE, automatic shrinking reduces the size of a file only when more than 25 percent of its space is unused.

Vedere ancheSee also

BACKUP (Transact-SQL) BACKUP (Transact-SQL)
Risolvere i problemi relativi a un log delle transazioni completo (Errore di SQL Server 9002) Troubleshoot a Full Transaction Log (SQL Server Error 9002)
Backup del log delle transazioni nella Guida sull'architettura e gestione del log delle transazioni in SQL Server Transaction Log Backups in the SQL Server Transaction Log Architecture and Management Guide
Backup di log delle transazioni (SQL Server) Transaction Log Backups (SQL Server)
Opzioni per file e filegroup ALTER DATABASE (Transact-SQL)ALTER DATABASE (Transact-SQL) File and Filegroup options