Gerenciar o tamanho do arquivo de log de transações

Aplica-se a: SQL Server

Este artigo aborda como monitorar o tamanho de um log de transações do SQL 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.

Este artigo se aplica ao SQL Server. Embora muito semelhantes, para obter informações sobre como gerenciar o tamanho dos arquivos de log de transações na Instância Gerenciada de SQL do Azure, consulte Gerenciar espaço de arquivos para bancos de dados na Instância Gerenciada de SQL do Azure. Para obter informações sobre o Banco de Dados SQL do Azure, consulte Gerenciar espaço de arquivos para bancos de dados no Banco de Dados SQL do Azure.

Entender os tipos de espaço de armazenamento para um banco de dados

Noções básicas sobre as quantidades de espaço de armazenamento a seguir são importantes para gerenciar o espaço de arquivo de banco de dados.

Quantidade de banco de dados	Definição	Comentários
Espaço de dados usado	A quantidade de espaço usada para armazenar dados do banco de dados.	Geralmente, esse espaço utilizado aumenta (diminui) em inserções (exclusões). Em alguns casos, o espaço utilizado não é alterado em inserções ou exclusões, dependendo da quantidade e do padrão de dados envolvidos na operação e de qualquer fragmentação. Por exemplo, excluir uma linha de cada página de dados não diminui necessariamente o espaço usado.
Espaço alocado de dados	A quantidade de espaço no arquivo formatado disponibilizada para armazenar dados do banco de dados.	O quantidade de espaço alocado cresce automaticamente, mas nunca diminui após as exclusões. Esse comportamento garante que as futuras inserções sejam mais rápidas, já que o espaço não precisa ser reformatado.
Espaço de dados alocados, mas não utilizado	A diferença entre a quantidade de espaço de dados alocado e espaço de dados usado.	Essa quantidade representa a quantidade máxima de espaço livre que pode ser recuperado pela redução de arquivos de dados do banco de dados.
Tamanho máximo dos dados	A quantidade máxima de espaço para dados que pode ser usada para armazenar dados do banco de dados.	A quantidade do espaço de dados alocados não pode crescer além do tamanho máximo de dados.

O diagrama a seguir ilustra o relacionamento entre os tipos de espaço diferentes de espaço de armazenamento para um banco de dados.

Consultar um banco de dados individual para informações de espaço de arquivo

Use a seguinte consulta para retornar a quantidade de espaço de arquivos do banco de dados alocado e a quantidade de espaço não utilizado alocada. Unidades do resultado da consulta são em 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;

Monitorar o uso do espaço de log

Monitore o uso do espaço de log usando 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.

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.

Importante

Evite sobrecarregar o disco de log. Verifique se o armazenamento de log pode suportar os requisitos de IOPS e baixa latência da carga transacional.

Reduzir o arquivo de log.

Para reduzir o tamanho físico de um arquivo de log físico devolvendo o espaço livre desse arquivo ao sistema operacional, reduza o arquivo de log. A redução apenas faz diferença quando um arquivo de log de transações contém espaço não utilizado.

Se o arquivo de log estiver cheio, provavelmente devido a transações abertas, investigue o que está impedindo o truncamento do log de transações.

Cuidado

As operações de redução não devem ser consideradas uma operação de manutenção regular. Arquivos de dados e de log que crescem devido a operações de negócios regulares e recorrentes não exigem operações de redução. Comandos de redução afetam o desempenho do banco de dados enquanto ele está em execução e, se possível, deve ser executado durante períodos de baixa utilização. Não é recomendável reduzir os arquivos de dados se a carga de trabalho regular do aplicativo faz com que os arquivos voltem para o mesmo tamanho alocado novamente.

Esteja ciente do potencial impacto negativo sobre o desempenho causado pela redução de arquivos de banco de dados. Confira a seção Manutenção de índice após a redução.

Antes de reduzir o log de transações, tenha em mente os Fatores que podem atrasar o truncamento de log. 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. Para obter mais informações, consulte Recomendações.

É 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. Em alguns casos, talvez não seja possível reduzir o log antes do próximo truncamento de log.

Fatores como uma transação de execução prolongada podem manter VLFs ativos por um período extenso, restringir a redução de log ou, até mesmo, impedir que o log seja reduzido. Para obter informações, consulte Fatores que podem atrasar o truncamento de log.

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). Quando você reduz um arquivo de log de transações, os VLFs inativos são removidos do final do arquivo de log para reduzir o log para aproximadamente o tamanho do destino.

Para obter mais informações sobre operações de redução, examine os seguintes links:

Reduzir um arquivo de log (sem reduzir os arquivos do banco de dados)

• DBCC SHRINKFILE (Transact-SQL)

• Reduzir um arquivo

Monitorar eventos de redução de arquivo de log

• Log File Auto Shrink Event Class.

Monitorar espaço de log

• sys.dm_db_log_space_usage (Transact-SQL)

• sys.database_files (Transact-SQL) (Confira as colunas size, max_size, e growth para o arquivo de log ou arquivos.)

Manutenção de índice após a redução

Após uma operação de redução ser concluída em arquivos de dados, os índices podem ficar fragmentados. Isso reduz a eficácia da otimização de desempenho para determinadas cargas de trabalho, como as consultas que usam verificações grandes. Se a degradação do desempenho ocorrer após a conclusão da operação de redução, considere a manutenção do índice para recompilar índices. Lembre-se de que recompilações de índice exigem espaço livre no banco de dados e, portanto, podem fazer com que o espaço alocado aumente, anulando o efeito da redução.

Para obter mais informações sobre a manutenção de índice, confira Otimizar a manutenção do índice para aprimorar o desempenho da consulta e reduzir o consumo de recursos.

Adicionar ou aumentar um arquivo de log

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

• Para adicionar um arquivo de log ao banco de dados, use a cláusula ADD LOG FILE da instrução ALTER DATABASE. Adicionar um arquivo de log permite o crescimento do log.
• Para aumentar o arquivo de log, use a cláusula MODIFY FILE da instrução ALTER DATABASE, especificando a sintaxe SIZE e MAXSIZE. Para obter mais informações, confira Opções de arquivo e grupo de arquivos de ALTER DATABASE (Transact-SQL).

Para obter mais informações, consulte as Recomendações.

Otimizar o tamanho do log de transações tempdb

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. Isso pode reduzir o desempenho do log de transações de tempdb.

Você pode evitar essa sobrecarga aumentando o tamanho do log de transações de tempdb depois de iniciar ou reinicializar a instância de servidor. Para obter mais informações, confira tempdb Database.

Controlar o crescimento de um arquivo de log de transações

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. Observe o seguinte:

• Para alterar o tamanho atual do arquivo em unidades de KB, MB, GB e TB, use a opção SIZE.
• Para alterar o incremento de aumento, use a opção FILEGROWTH. Um valor 0 indica que o crescimento automático está definido como off e nenhum espaço adicional é permitido.
• 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.

Para obter mais informações, consulte as Recomendações.

Recomendações

Estas são algumas recomendações gerais ao trabalhar com arquivos de log de transações:

• 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. O incremento de crescimento do arquivo em um arquivo de log deve ser suficientemente grande para evitar a expansão frequente. Um ponteiro válido para dimensionar corretamente um log de transações é monitorar a quantidade de log ocupado durante:

  • O tempo necessário para executar um backup completo, pois os backups de log não podem ocorrer até que ele seja concluído.
  • O tempo necessário para as maiores operações de manutenção de índice.
  • O tempo necessário para executar o maior lote em um banco de dados.

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

  • Em versões anteriores ao SQL Server 2022 (16.x), 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.

  • A partir do SQL Server 2022 (16.x) (todas as edições) e no Banco de Dados SQL do Azure, a inicialização instantânea de arquivos pode beneficiar os eventos de crescimento do log de transações de até 64 MB. O incremento de tamanho de crescimento automático padrão para os novos bancos de dados é de 64 MB. Os eventos de crescimento automático do arquivo de log de transações com mais de 64 MB não podem se beneficiar da inicialização instantânea de arquivo.

  • 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. Os valores padrão para a opção FILEGROWTH são:

    Versão	Valores padrão
    A partir do SQL Server 2016 (13.x)	Dados 64 MB. Arquivos de log 64 MB.
    A partir do SQL Server 2005 (9.x)	Dados 1 MB. Arquivos de log 10%.
    Antes do SQL Server 2005 (9.x)	Dados 10%. Arquivos de log 10%.

• Um pequeno incremento de aumento automático pode gerar um número excessivo de VLFs pequenos e pode reduzir o desempenho. 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 determinada instância e os incrementos de aumento necessários para atingir o tamanho necessário, confira este script para analisar e consertar VLFs, fornecido pelo time Tiger do SQL.

• Um grande incremento de aumento automático pode causar dois problemas:

  • Um grande incremento de aumento automático pode fazer com que o banco de dados pause enquanto o novo espaço é alocado, potencialmente causando tempos limite de consulta.
  • Um grande incremento de aumento automático pode gerar um número pequeno de VLFs grandes e também afetar o desempenho. 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 determinada instância e os incrementos de aumento necessários para atingir o tamanho necessário, confira este script para analisar e consertar VLFs, fornecido pelo time Tiger do SQL.

• 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. Para obter mais informações sobre como alterar o incremento de aumento, consulte Opções de arquivo e grupo de arquivos ALTER DATABASE (Transact-SQL).

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

• Os arquivos de log podem ser definidos para serem reduzidos automaticamente. No entanto, isso não é recomendável e a propriedade de banco de dados auto_shrink é definida como FALSE por padrão. 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.

  • O arquivo é reduzido de forma que 25% de seu tamanho seja de espaço não utilizado ou ele tenha o tamanho original, o que for maior.
  • Para obter informações sobre como alterar a configuração da propriedade auto_shrink, confira Exibir ou alterar as propriedades de um banco de dados e Opções de ALTER DATABASE SET (Transact-SQL).

Próximas etapas

• BACKUP (Transact-SQL)
• Solução de problemas em um log de transação completa (SQL Server Erro 9002)
• Guia de gerenciamento e arquitetura de backups de log de transações no log de transações do SQL Server
• Backups de log de transações (SQL Server)
• Opções File e Filegroup de ALTER DATABASE (Transact-SQL)