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

Aplica-se: 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 semelhante, 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 arquivo 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

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.	Geralmente, esse espaço utilizado aumenta (diminui) em inserções (exclusões). Em alguns casos, o espaço usado não é alterado nas 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, pois 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 do 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 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 atual do arquivo de log, seu tamanho máximo e a opção de crescimento automático do arquivo, você também pode usar as sizecolunas , max_sizee 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.

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. 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. 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 reduzir os arquivos de dados se uma carga de trabalho normal do aplicativo faz com que os arquivos voltem 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 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.

É 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, a redução do log só pode ser possível após o 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

Os índices podem ficar fragmentados depois que uma operação de redução é concluída em arquivos de dados. Isso 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. Lembre-se de que as recompilações de índice exigem espaço livre no banco de dados e, portanto, podem aumentar o espaço alocado, neutralizando o efeito de 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

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. Isso 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, 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 tempdb transações depois de iniciar ou reiniciar a instância do 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:

Use a opção 'SIZE' para alterar o tamanho atual do arquivo 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 as Recomendações.

Recomendações

A seguir estão algumas recomendações gerais ao trabalhar com arquivos de log 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 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 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.

• Ao definir o crescimento automático para arquivos de dados e log usando a FILEGROWTH opção, pode ser preferível 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 Arquivo, 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 pode gerar muitos 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 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:

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

• 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, isso não é recomendado e a propriedade auto_shrink database é 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 para o tamanho em que apenas 25% do arquivo é espaço não utilizado ou para o tamanho original do arquivo, o que for maior. - Para obter informações sobre como alterar a configuração da propriedade auto_shrink, consulte Exibir ou alterar as propriedades de um banco de dados e Opções ALTER DATABASE SET (Transact-SQL).

Conteúdo relacionado

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