Gerenciar o tamanho do arquivo de log de transaçõesManage the size of the transaction log file

APLICA-SE A: simSQL Server nãoBanco de Dados SQL do Azure nãoSQL Data Warehouse do Azure nãoParallel Data Warehouse APPLIES TO: yesSQL Server noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

Este tópico aborda como monitorar o tamanho de um log de transações do SQL ServerSQL Server, reduzir o log de transações, adicionar ou aumentar um arquivo de log de transações, otimizar a taxa de crescimento do log de transações de tempdb e controlar o crescimento de um arquivo de log de transações.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.

Monitorar o uso do espaço de logMonitor log space use

Monitore o uso do espaço de log usando sys.dm_db_log_space_usage.Monitor log space use by using sys.dm_db_log_space_usage. Essa DMV retorna informações sobre a quantidade de espaço de log usada atualmente e indica quando o log de transações precisa de truncamento.This DMV returns information about the amount of log space currently used, and indicates when the transaction log needs truncation.

Para obter informações sobre o tamanho do arquivo de log atual, seu tamanho máximo e a opção de crescimento automático para o arquivo, você também pode usar as colunas size, max_size e growth para esse arquivo de log em 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

Evite sobrecarregar o disco de log.Avoid overloading the log disk. Verifique se o armazenamento de log pode suportar os requisitos de IOPS e baixa latência da carga transacional.Ensure the log storage can withstand the IOPS and low latency requirements for your transactional load.

Reduzir o tamanho do arquivo de logShrink log file size

Para reduzir o tamanho físico de um arquivo de log físico, você deve reduzir o arquivo de log.To reduce the physical size of a physical log file, you must shrink the log file. Isso será útil se você souber que um arquivo de log de transações contém espaço não utilizado.This is useful when you know that a transaction log file contains unused space. É possível reduzir um arquivo de log somente enquanto o banco de dados estiver online e se, pelo menos, um VLF (arquivo de log virtual) estiver livre.You can shrink a log file only while the database is online, and at least one virtual log file (VLF) is free. Em alguns casos, talvez não seja possível reduzir o log antes do próximo truncamento de log.In some cases, shrinking the log may not be possible until after the next log truncation.

Observação

Fatores como uma transação de execução longa, que mantém VLFs ativos por um período extenso, podem restringir a redução de log ou até mesmo impedir que o log seja reduzido.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. Para obter informações, consulte Fatores que podem atrasar o truncamento de log.For information, see Factors that can delay log truncation.

A redução de um arquivo de log remove um ou mais VLFs que não mantêm nenhuma parte do log lógico (ou seja, VLFs inativos).Shrinking a log file removes one or more VLFs that hold no part of the logical log (that is, inactive VLFs). Quando um arquivo de log de transações é reduzido, os VLFs inativos são removidos do final do arquivo de log para reduzir o log para aproximadamente o tamanho do destino.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

Antes de reduzir o log de transações, tenha em mente os Fatores que podem atrasar o truncamento de log.Before shrinking the transaction log, keep in mind Factors that can delay log truncation. Se o espaço de armazenamento for necessário novamente após a redução de um log, o log de transações aumentará novamente e, fazendo isso, introduzirá uma sobrecarga de desempenho durante as operações de aumento do log.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. Para obter mais informações, consulte as Recomendações neste tópico.For more information, see the Recommendations in this topic.

Reduzir um arquivo de log (sem reduzir os arquivos do banco de dados)Shrink a log file (without shrinking database files)

Monitorar eventos de redução de arquivo de logMonitor log-file shrink events

Monitorar espaço de logMonitor log space

Adicionar ou aumentar um arquivo de logAdd or enlarge a log file

Também é possível obter mais espaço aumentando o arquivo de log existente (se houver espaço em disco) ou adicionando um arquivo de log ao banco de dados, geralmente em um disco diferente.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. Um arquivo de log de transações é suficiente, a menos que o espaço de log esteja se esgotando e o espaço em disco também esteja se esgotando no volume que contém o arquivo de 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.

  • Para adicionar um arquivo de log ao banco de dados, use a cláusula ADD LOG FILE da instrução ALTER DATABASE.To add a log file to the database, use the ADD LOG FILE clause of the ALTER DATABASE statement. Adicionar um arquivo de log permite o crescimento do log.Adding a log file allows the log to grow.
  • Para aumentar o arquivo de log, use a cláusula MODIFY FILE da instrução ALTER DATABASE, especificando a sintaxe SIZE e MAXSIZE.To enlarge the log file, use the MODIFY FILE clause of the ALTER DATABASE statement, specifying the SIZE and MAXSIZE syntax. Para obter mais informações, consulte Opções de arquivo e grupo de arquivos de ALTER DATABASE (Transact-SQL).For more information, see ALTER DATABASE (Transact-SQL) File and Filegroup options.

Para obter mais informações, consulte as Recomendações neste tópico.For more information, see the Recommendations in this topic.

Otimizar o tamanho do log de transações tempdbOptimize tempdb transaction log size

Reinicializar uma instância de servidor redimensiona o log de transações do banco de dados tempdb ao seu tamanho original, antes do crescimento automático.Restarting a server instance resizes the transaction log of the tempdb database to its original, pre-autogrow size. Isso pode reduzir o desempenho do log de transações do tempdb .This can reduce the performance of the tempdb transaction log.

Você pode evitar essa sobrecarga aumentando o tamanho do log de transações do tempdb depois de iniciar ou reinicializar a instância de servidor.You can avoid this overhead by increasing the size of the tempdb transaction log after starting or restarting the server instance. Para obter mais informações, confira tempdb Database.For more information, see tempdb Database.

Controlar o crescimento de um arquivo de log de transaçõesControl transaction log file growth

Use a instrução Opções de arquivo e grupo de arquivos de ALTER DATABASE (Transact-SQL) para gerenciar o aumento de um arquivo de log de transações.Use the ALTER DATABASE (Transact-SQL) File and Filegroup options statement to manage the growth of a transaction log file. Observe o seguinte:Note the following:

  • Para alterar o tamanho atual do arquivo em unidades de KB, MB, GB e TB, use a opção SIZE.To change the current file size in KB, MB, GB, and TB units, use the SIZE option.
  • Para alterar o incremento de aumento, use a opção FILEGROWTH.To change the growth increment, use the FILEGROWTH option. Um valor 0 indica que o crescimento automático está definido como off e nenhum espaço adicional é permitido.A value of 0 indicates that automatic growth is set to off and no additional space is permitted.
  • Para controlar o tamanho máximo de um arquivo de log em unidades de KB, MB, GB e TB ou para definir o aumento como UNLIMITED, use a opção 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.

Para obter mais informações, consulte as Recomendações neste tópico.For more information, see the Recommendations in this topic.

RecomendaçõesRecommendations

Estas são algumas recomendações gerais ao trabalhar com arquivos de log de transações:Following are some general recommendations when you are working with transaction log files:

  • O incremento de aumento automático do log de transações, conforme definido pela opção FILEGROWTH, deve ser grande o suficiente para se manter à frente das necessidades das transações da carga de trabalho.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. O incremento de crescimento do arquivo em um arquivo de log deve ser suficientemente grande para evitar a expansão frequente.The file growth increment on a log file should be sufficiently large to avoid frequent expansion. Um ponteiro válido para dimensionar corretamente um log de transações é monitorar a quantidade de log ocupado durante:A good pointer to properly size a transaction log is monitoring the amount of log occupied during:

    • O tempo necessário para executar um backup completo, pois os backups de log não podem ocorrer até que ele seja concluído.The time required to execute a full backup, because log backups cannot occur until it finishes.
    • O tempo necessário para as maiores operações de manutenção de índice.The time required for the largest index maintenance operations.
    • O tempo necessário para executar o maior lote em um banco de dados.The time required to execute the largest batch in a database.
  • Ao definir aumento automático para arquivos de dados e de log usando a opção FILEGROWTH, talvez seja preferível defini-lo em tamanho em vez de em percentual, a fim de permitir um melhor controle da taxa de aumento, pois o percentual é um valor que aumenta constantemente.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.

    • Tenha em mente de que os logs de transações não podem utilizar a Inicialização Instantânea de Arquivo e, portanto, tempos de aumento de log estendidos são especialmente críticos.Keep in mind that transaction logs cannot leverage Instant File Initialization, so extended log growth times are especially critical.
    • Como uma melhor prática, não defina o valor da opção FILEGROWTH como superior a 1.024 MB para logs de transações.As a best practice, do not set the FILEGROWTH option value above 1,024 MB for transaction logs. Os valores padrão para a opção FILEGROWTH são:The default values for FILEGROWTH option are:
    VersãoVersion Valores padrãoDefault values
    A partir do SQL Server 2016 (13.x)SQL Server 2016 (13.x)Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) Dados 64 MB.Data 64 MB. Arquivos de log 64 MB.Log files 64 MB.
    A partir do SQL Server 2005 (9.x)SQL Server 2005 (9.x)Starting with SQL Server 2005 (9.x)SQL Server 2005 (9.x) Dados 1 MB.Data 1 MB. Arquivos de log 10%.Log files 10%.
    Antes do SQL Server 2005 (9.x)SQL Server 2005 (9.x)Prior to SQL Server 2005 (9.x)SQL Server 2005 (9.x) Dados 10%.Data 10%. Arquivos de log 10%.Log files 10%.
  • Um pequeno incremento de aumento pode gerar um número excessivo de VLFs pequenos e pode reduzir o desempenho.A small growth increment can generate too many small VLFs and can reduce performance. Para determinar a distribuição ideal de VLF para o tamanho atual do log de transações de todos os bancos de dados em uma instância determinada e os incrementos de crescimento necessários para alcançar o tamanho necessário, consulte este 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.

  • Um grande incremento de aumento pode gerar um número pequeno de VLFs grandes e também pode afetar o desempenho.A large growth increment can generate too few and large VLFs and can also affect performance. Para determinar a distribuição ideal de VLF para o tamanho atual do log de transações de todos os bancos de dados em uma instância determinada e os incrementos de crescimento necessários para alcançar o tamanho necessário, consulte este 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.

  • Mesmo com o aumento automático habilitado, você pode receber uma mensagem informando que o log de transações está cheio, caso ele não possa aumentar rápido o suficiente para atender às necessidades da consulta.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. Para obter mais informações sobre como alterar o incremento de aumento, consulte Opções de arquivo e grupo de arquivos de ALTER DATABASE (Transact-SQL)For more information on changing the growth increment, see ALTER DATABASE (Transact-SQL) File and Filegroup options

  • Ter vários arquivos de log em um banco de dados não melhora o desempenho de forma alguma, porque os arquivos de log de transações não usam o preenchimento proporcional como arquivos de dados no mesmo grupo de arquivos.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.

  • Os arquivos de log podem ser definidos para serem reduzidos automaticamente.Log files can be set to shrink automatically. No entanto, isso não é recomendável e a propriedade de banco de dados auto_shrink é definida como FALSE por padrão.However this is not recommended, and the auto_shrink database property is set to FALSE by default. Se auto_shrink for definido como TRUE, a redução automática reduzirá o tamanho de um arquivo apenas quando mais de 25% de seu espaço estiver inutilizado.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.

Confira tambémSee also

BACKUP (Transact-SQL) BACKUP (Transact-SQL)
Solução de problemas de um log de transações cheio (Erro 9002 do SQL Server ) Troubleshoot a Full Transaction Log (SQL Server Error 9002)
Guia de gerenciamento e arquitetura de backups de log de transações no log de transações do SQL Server Transaction Log Backups in the SQL Server Transaction Log Architecture and Management Guide
Backups de log de transações (SQL Server) Transaction Log Backups (SQL Server)
Opções de arquivo e grupo de arquivos de ALTER DATABASE (Transact-SQL)ALTER DATABASE (Transact-SQL) File and Filegroup options