Compartilhar via

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

Aplica-se: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 taxa de crescimento do tempdb log de transações e controlar o crescimento de um arquivo de log de transações.

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

Entender 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 de banco de dados Definição Comentários
Espaço de dados usado O espaço usado para armazenar dados do banco de dados. Em geral, o espaço usado aumenta nas inserções e diminui nas exclusões. Em alguns casos, o espaço usado 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, a exclusão de uma linha de cada página de dados não necessariamente diminui o espaço usado.
Espaço alocado de dados O espaço de arquivo formatado disponibilizado 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 inserções futuras sejam mais rápidas porque o espaço não precisa ser reformatado.
Espaço de dados alocados, mas não utilizado A diferença entre a quantidade alocada e o espaço de dados usado. 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 de banco de dados. A quantidade de espaço de dados alocada não pode crescer além do tamanho máximo dos 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 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 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 colunas size, max_size e growth desse 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 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.

Cuidado

As operações de redução 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 comerciais 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 baixo uso. Não recomendamos que você reduza os arquivos de dados se uma carga de trabalho de aplicativo regular fizer com que os arquivos aumentem para o mesmo tamanho alocado novamente.

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

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

Você só pode reduzir um arquivo de log enquanto o banco de dados estiver online e pelo menos um arquivo de log virtual (VLF) estiver livre. Em alguns casos, a redução do 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 as VLFs ativas por um período prolongado, restringindo a contração do log ou até mesmo impedindo o log de diminuir. 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 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 recursos:

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

Monitorar eventos de redução de arquivo de log

Monitorar espaço de log

Manutenção de í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 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 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. Tenha em mente que as recompilações de índice exigem espaço livre no banco de dados e, portanto, podem aumentar o espaço alocado, contrariando 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 aumentar 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 cláusula ADD LOG FILE da instrução ALTER DATABASE. Essa ação permite que o log cresça.
  • 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, consulte as 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

Reiniciar uma instância de servidor redimensiona o log de transações do banco de dados tempdb para seu tamanho original antes do crescimento automático. Esse redimensionamento pode reduzir o desempenho do log de tempdb 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 o banco de dados Tempdb.

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

Use a instrução ALTER DATABASE (Transact-SQL) opções de arquivo e grupo de arquivos para gerenciar o crescimento de um arquivo de log de transações. Observe o seguinte:

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

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

Recomendações

A seguir estão algumas recomendações gerais a serem consideradas quando você estiver trabalhando com arquivos de log de transações:

  • O incremento de crescimento automático (crescimento automático) do log de transações, conforme definido pela opção FILEGROWTH , deve ser grande o suficiente para ficar à frente das necessidades das transações de carga de trabalho. O incremento de crescimento do arquivo em um arquivo de log deve ser suficientemente grande para evitar a 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 de índice.
    • O tempo necessário para executar o maior lote em um banco de dados.

  • Quando você define o crescimento automático de dados e arquivos de log usando a opção FILEGROWTH , talvez seja melhor defini-lo em tamanho em vez de porcentagem para permitir um melhor controle da taxa de crescimento, pois 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 de crescimento 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 novos bancos de dados é de 64 MB. Os eventos de aumento 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 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
      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 crescimento automático pode gerar muitas VLFs pequenas 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 crescimento necessários para atingir o tamanho necessário, consulte este script para analisar e corrigir VLFs, fornecido pela equipe do SQL Tiger.

  • Um grande incremento de aumento 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.
    • Ele pode gerar poucos e grandes VLFs, além de 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 crescimento necessários para atingir o tamanho necessário, consulte este script para analisar e corrigir VLFs, fornecido pela equipe do SQL Tiger.

  • Mesmo com o crescimento automático habilitado, você pode receber uma mensagem informando que o log de transações está cheio se não puder crescer rápido o suficiente para atender às necessidades da consulta. Para obter mais informações sobre como alterar o incremento de crescimento, consulte ALTER DATABASE (Transact-SQL) opções de arquivo e grupo de arquivos.

  • Ter vários arquivos de log em um banco de dados não melhora o desempenho de forma alguma, pois 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 definidos para serem reduzidos automaticamente. No entanto, não recomendamos essa configuração e a propriedade de AUTO_SHRINK banco de dados é definida como FALSE por padrão. Se AUTO_SHRINK for definido como TRUE, a redução automática reduzirá o tamanho de um arquivo somente quando mais de 25% de seu espaço não for usado.

Conteúdo relacionado

  • Last updated on