Partilhar via

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

Aplica-se a:SQL Server

Este artigo descreve como monitorar o tamanho do log de transações do SQL Server, reduzir o log de transações, adicionar ou ampliar um arquivo de log de transações, otimizar a tempdb taxa de crescimento do log de transações e controlar o crescimento de um arquivo de log de transações.

Este artigo aplica-se ao SQL Server. Embora o processo seja semelhante, para gerenciamento de espaço de arquivo no Azure SQL, consulte:

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

Compreender as seguintes quantidades de espaço de armazenamento é importante para gerenciar o espaço de arquivo de um banco de dados.

Quantidade do banco de dados Definition Comments
Espaço de dados utilizado O espaço usado para armazenar dados do banco de dados. Geralmente, o espaço utilizado aumenta nas inserções e diminui nas eliminações. Em alguns casos, o espaço utilizado não muda nas inserções ou exclusões, dependendo da quantidade e 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 de dados alocado O espaço de arquivo formatado disponibilizado para armazenar dados do banco de dados. A quantidade de espaço alocado cresce automaticamente, mas nunca diminui após as exclusões. Esse comportamento garante que as inserções futuras sejam mais rápidas porque o espaço não precisa ser reformatado.
Espaço de dados alocado mas não utilizado A diferença entre o montante atribuído e o espaço de dados utilizado. Essa quantidade representa o espaço livre máximo que os arquivos de dados de banco de dados reduzidos podem recuperar.
Tamanho máximo dos dados A quantidade máxima de espaço para armazenar dados do banco de dados. A quantidade de espaço de dados alocado não pode crescer além do tamanho máximo de dados.

O diagrama a seguir ilustra as relações entre os diferentes tipos de espaço de armazenamento para um banco de dados.

Diagrama que ilustra as relações entre os diferentes tipos de espaço de armazenamento para um banco de dados.

Consultar um único banco de dados para obter informações de espaço de arquivo

Use a consulta a seguir para retornar a quantidade de espaço de arquivo de banco de dados alocado e a quantidade de espaço não utilizado alocado. As unidades do resultado da consulta estã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. Esse Detran retorna informações sobre a quantidade de espaço de log usado atualmente e indica quando o log de transações precisa de truncamento.

Para obter informações sobre o tamanho atual do arquivo de log, seu tamanho máximo e a opção de crescimento automático para o arquivo, você também pode usar as sizecolunas , max_sizee growth para esse arquivo de log no sys.database_files.

Importante

Evite sobrecarregar o disco de log. Certifique-se de que o armazenamento de logs possa suportar os requisitos de IOPS e baixa latência para sua carga transacional.

Reduzir um arquivo de log

Reduza o arquivo de log para reduzir seu tamanho físico retornando espaço livre para o sistema operacional. Uma redução só 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.

Atenção

As operações de encolhimento não devem ser consideradas uma operação de manutenção regular. Os arquivos de dados e de log que crescem devido a operações de negócios recorrentes regulares não exigem operações de redução. Os comandos de redução afetam o desempenho do banco de dados durante a execução. Eles devem ser executados durante períodos de baixa utilização. Não recomendamos que você reduza os arquivos de dados se uma carga de trabalho de aplicativo regular fizer com que os arquivos cresçam para o mesmo tamanho alocado novamente.

Esteja ciente do potencial impacto negativo no desempenho da redução de arquivos de banco de dados. Consulte Manutenção do índice após a redução.

Antes de reduzir o log de transações, tenha em mente os fatores que podem atrasar o truncamento do log. Se o espaço de armazenamento for necessário novamente após uma redução de log, o log de transações crescerá novamente, introduzindo sobrecarga de desempenho durante as operações de crescimento de log. Para obter mais informações, consulte Recomendações.

Você pode reduzir um arquivo de log somente enquanto o banco de dados estiver online e pelo menos um arquivo de log virtual (VLF) estiver livre. Em alguns casos, reduzir o log só pode ser possível após o próximo truncamento de log.

Alguns fatores, como uma transação de longa duração, podem manter os VLFs ativos por um longo período, podem restringir a redução do log ou até mesmo impedir que o log diminua. Para obter mais 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 contêm nenhuma parte do log lógico (ou seja, VLFs inativos). Quando você reduz um arquivo de log de transações, VLFs inativos são removidos do final do arquivo de log para reduzir o log para aproximadamente o tamanho de destino.

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

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

Monitorar eventos de redução de arquivos de log

Monitorar o espaço de log

Manutenção do índice após uma operação de redução

Os índices podem ficar fragmentados depois que uma operação de redução é concluída em relação aos arquivos de dados. Essa fragmentação reduz sua eficácia para otimização de desempenho para determinadas cargas de trabalho, como consultas que usam verificações grandes. Se ocorrer degradação do desempenho após a conclusão da operação de redução, considere a manutenção do índice para reconstruir os índices. Tenha em mente que as reconstruções de índice exigem espaço livre no banco de dados e, portanto, podem aumentar o espaço alocado, neutralizando o efeito da operação de redução.

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

Adicionar ou ampliar um arquivo de log

Você pode ganhar espaço ampliando o arquivo de log existente (se o espaço em disco permitir) ou adicionando um arquivo de log ao banco de dados, normalmente 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 ADD LOG FILEALTER DATABASE cláusula da instrução. Esta ação permite que o log cresça.
  • Para ampliar o arquivo de log, use a MODIFY FILEALTER DATABASE cláusula da instrução, especificando a SIZE sintaxe e MAXSIZE . Para obter mais informações, consulte Opções de arquivo e grupo de arquivos ALTER DATABASE (Transact-SQL).

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

Otimizar o tamanho do log de transações tempdb

A reinicialização de uma instância do servidor redimensiona o log de transações do tempdb banco de dados para seu tamanho original pré-crescimento automático. Esse redimensionamento pode reduzir o desempenho do tempdb log de transações.

Você pode evitar essa sobrecarga aumentando o tamanho do log de tempdb transações depois de iniciar ou reiniciar a instância do servidor. Para obter mais informações, consulte Banco de dados Tempdb.

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

Use a instrução ALTER DATABASE (Transact-SQL) file and filegroup options para gerenciar o crescimento de um arquivo de log de transações. Tenha em atenção o seguinte:

  • Use a SIZE opção para alterar o tamanho atual do arquivo em unidades KB, MB, GB e TB.
  • Para alterar o incremento de crescimento, use a FILEGROWTH opção. Um valor 0 indica que o crescimento automático está desativado e não é permitido espaço extra. Use a opção para controlar o tamanho máximo de um arquivo de log em unidades KB, MB, GB e TB ou para definir o MAXSIZE crescimento como UNLIMITED.

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

Recommendations

Seguem-se algumas recomendações gerais a considerar quando estiver a trabalhar com ficheiros de registo de transações:

  • O incremento de crescimento automático (crescimento automático) do log de transações, conforme definido pela FILEGROWTH opção, deve ser grande o suficiente para ficar à frente das necessidades das transações de carga de trabalho. O incremento de crescimento de arquivo em um arquivo de log deve ser suficientemente grande para evitar expansão frequente. Uma boa dica para dimensionar corretamente um log de transações é monitorar a quantidade de log ocupado durante:

    • O tempo necessário para executar um backup completo, porque 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 do índice.
    • O tempo necessário para executar o maior lote em um banco de dados.

  • Quando você define o crescimento automático para dados e arquivos de log usando a FILEGROWTH opção, talvez seja melhor defini-lo em tamanho em vez de porcentagem para permitir um melhor controle da taxa de crescimento, porque uma porcentagem é uma quantidade cada vez maior.

    • Em versões anteriores ao SQL Server 2022 (16.x), os logs de transações não podem usar a inicialização instantânea de arquivos, portanto, os tempos estendidos de crescimento do log 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 eventos de crescimento do log de transações de até 64 MB. O incremento de tamanho de crescimento automático padrão para novos bancos de dados é de 64 MB. Os eventos de crescimento automático do arquivo de log de transações maiores que 64 MB não podem se beneficiar da inicialização instantânea do arquivo.

    • Como prática recomendada, não defina o valor da FILEGROWTH opção acima de 1.024 MB para logs de transações. Os valores padrão para a FILEGROWTH opção são:

      Versão Valores padrão
      Começando com o SQL Server 2016 (13.x) Dados: 64 MB. Ficheiros de registo: 64 MB.
      Começando com o SQL Server 2005 (9.x) Dados: 1 MB. Ficheiros de registo: 10%.
      Antes do SQL Server 2005 (9.x) Data: 10%. Ficheiros de registo: 10%.

  • Um pequeno incremento de crescimento automático pode gerar muitos VLFs pequenos e pode reduzir o desempenho. Para determinar a distribuição VLF ideal para o tamanho atual do log de transações de todos os bancos de dados em uma determinada instância e os incrementos de crescimento necessários para atingir o tamanho necessário, consulte este script para analisar e corrigir VLFs, fornecido pela Equipe SQL Tiger.

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

    • Isso pode fazer com que o banco de dados pause enquanto o novo espaço é alocado, potencialmente causando tempos limite de consulta.
    • Pode gerar muito poucos e grandes VLFs e também pode afetar o desempenho. Para determinar a distribuição VLF ideal para o tamanho atual do log de transações de todos os bancos de dados em uma determinada instância e os incrementos de crescimento necessários para atingir o tamanho necessário, consulte este script para analisar e corrigir VLFs, fornecido pela Equipe SQL Tiger.

  • Mesmo com o crescimento automático ativado, você pode receber uma mensagem informando que o log de transações está cheio se não puder crescer rápido o suficiente para satisfazer as necessidades da sua consulta. Para obter mais informações sobre como alterar o incremento de crescimento, 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 preenchimento proporcional como arquivos de dados em um mesmo grupo de arquivos.

Os arquivos de log podem ser configurados para diminuir automaticamente. No entanto, não recomendamos essa configuração e a AUTO_SHRINK propriedade database é definida como FALSE por padrão. Se AUTO_SHRINK estiver definido como TRUE, a redução automática reduz o tamanho de um arquivo somente quando mais de 25% de seu espaço não é utilizado.

Conteúdo relacionado

  • Last updated on