Gerenciar espaços de arquivo para bancos de dados na Instância Gerenciada de SQL do Azure

Aplica-se a:Instância Gerenciada de SQL do Azure

Este artigo explica como monitorar e gerenciar arquivos em bancos de dados na Instância Gerenciada de SQL do Azure. Ele aborda como monitorar o tamanho do arquivo de banco de dados, reduzir o log de transações, ampliar um arquivo de log de transações e controlar o crescimento de um arquivo de log de transações.

Este artigo se aplica à Instância Gerenciada de SQL do Azure. Para obter informações sobre como gerenciar o tamanho dos arquivos de log de transações no SQL Server, consulte Gerenciar o tamanho do arquivo de log de transações.

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

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 consulta a seguir em sys.database_files para devolver o valor de espaço de arquivo de banco de dados alocado e o valor de espaço não utilizado alocado. 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, use o sizearquivo max_sizee growth as colunas para esse arquivo de log em sys.database_files.

As métricas de espaço de armazenamento exibidas nas APIs de métricas baseadas no Azure Resource Manager medem apenas o tamanho das páginas de dados usadas. Para obter exemplos, consulte Get-AZMetric do PowerShell.

Reduzir o tamanho do arquivo de log

Para reduzir o tamanho físico de um arquivo de log físico removendo espaço não utilizado, reduza o arquivo de log. 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.

Esteja ciente do potencial impacto negativo no desempenho da redução de arquivos de banco de dados. Para obter mais informações, consulte a manutenção do índice após a redução. Em casos raros, backups automatizados de banco de dados podem afetar operações de redução. Se necessário, repita a operação de 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 e, ao fazer isso, introduzirá a sobrecarga de desempenho durante as operações de crescimento de log. Para obter mais informações, consulte a seção de 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 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 da meta.

Para obter mais informações sobre operações de redução, examine a seguinte documentação:

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

• Classe de evento Log File Auto Shrink.

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. A fragmentação reduz a eficácia da otimização de desempenho de um índice para determinadas cargas de trabalho, como consultas usando 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.

Avaliar a densidade da página de índice

Se truncar arquivos de dados não resultar em uma redução suficiente no espaço alocado, você poderá decidir reduzir os arquivos de dados do banco de dados para recuperar espaço não utilizado desses arquivos. No entanto, como uma etapa opcional, mas recomendada, primeiro você deve determinar a densidade média da página para os índices no banco de dados. Para a mesma quantidade de dados, a redução será concluída mais rapidamente se a densidade de páginas for alta, pois ela move menos páginas. Se a densidade de página for baixa para alguns índices, considere executar a manutenção nesses índices para aumentar a densidade antes de reduzir os arquivos de dados. Essa etapa permite reduzir uma redução mais profunda no espaço de armazenamento alocado.

Para determinar a densidade de página para todos os índices no banco de dados, use a consulta a seguir. A densidade de página é relatada na coluna avg_page_space_used_in_percent.

SELECT OBJECT_SCHEMA_NAME(ips.object_id) AS schema_name,
       OBJECT_NAME(ips.object_id) AS object_name,
       i.name AS index_name,
       i.type_desc AS index_type,
       ips.avg_page_space_used_in_percent,
       ips.avg_fragmentation_in_percent,
       ips.page_count,
       ips.alloc_unit_type_desc,
       ips.ghost_record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), default, default, default, 'SAMPLED') AS ips
INNER JOIN sys.indexes AS i
ON ips.object_id = i.object_id
   AND
   ips.index_id = i.index_id
ORDER BY page_count DESC;

Se houver índices com alta contagem de páginas e com densidade de página inferior a 60 a 70%, considere recompilar ou reorganizar esses índices antes de reduzir arquivos de dados.

Se houver vários índices com baixa densidade de página, talvez seja possível recompilá-los em paralelo em várias sessões de banco de dados para acelerar o processo. No entanto, verifique se você não está se aproximando dos limites de recursos do banco de dados fazendo isso e deixe espaço suficiente para cargas de trabalho do aplicativo. Monitore o consumo de recursos (CPU, E/S de Dados, E/S de Log) no portal do Azure ou usando a exibição sys.dm_db_resource_stats . Inicie novas recompilações paralelas somente se a utilização de recursos em cada uma dessas dimensões permanecer substancialmente inferior a 100%. Se a utilização de CPU, E/S de Dados ou E/S de Log estiver em 100%, você poderá escalar verticalmente o banco de dados para ter mais núcleos de CPU e aumentar a taxa de transferência de E/S, permitindo que mais recompilações paralelas concluam o processo mais rapidamente.

Comando de recompilação de índice de amostra

A seguir, temos um comando de amostra para recompilar um índice e aumentar sua densidade de página, usando a instrução ALTER INDEX:

ALTER INDEX [index_name] ON [schema_name].[table_name]
REBUILD WITH (FILLFACTOR = 100, MAXDOP = 8,
ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 5 MINUTES, ABORT_AFTER_WAIT = NONE)),
RESUMABLE = ON);

Esse comando inicia uma recompilação de índice online e retomável. Esse tipo de recompilação permite que cargas de trabalho simultâneas continuem usando a tabela enquanto a recompilação está em andamento e permite que você retome a recompilação se ela for interrompida por qualquer motivo. No entanto, esse tipo de recompilação é mais lento do que uma recompilação offline, que bloqueia o acesso à tabela. Se nenhuma outra carga de trabalho precisar acessar a tabela durante a recompilação, defina as opções ONLINE e RESUMABLE como OFF e remova a cláusula WAIT_AT_LOW_PRIORITY.

Para saber mais 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.

Reduzir vários arquivos de dados

Como já foi observado, a redução com movimentação de dados é um processo de execução longa. Se o banco de dados tiver vários arquivos de dados, você poderá acelerar o processo reduzindo vários arquivos de dados em paralelo. Você faz essa operação abrindo várias sessões de banco de dados e usando DBCC SHRINKFILE em cada sessão com um valor diferente file_id . Semelhante à recompilação de índices, verifique se você tem reserva dinâmica de recursos (CPU, E/S de dados, E/S de logs) suficiente antes de iniciar cada comando de redução paralelo.

O seguinte comando de amostra reduz o arquivo de dados com file_id 4, tentando reduzir o tamanho alocado para 52.000 MB movendo páginas no arquivo:

DBCC SHRINKFILE (4, 52000);

Se quiser reduzir o espaço alocado para o arquivo ao mínimo possível, execute a instrução sem especificar o tamanho de destino:

DBCC SHRINKFILE (4);

Se uma carga de trabalho estiver sendo executada simultaneamente com a redução, ela poderá começar a usar o espaço de armazenamento liberado pela redução antes que ela seja concluída e truncar o arquivo. Nesse caso, a redução não pode reduzir o espaço alocado para o destino especificado.

Você pode atenuar esse problema reduzindo cada arquivo em etapas menores. Isso significa que, no comando DBCC SHRINKFILE, você define a meta um pouco menor do que o espaço alocado atualmente para o arquivo. Por exemplo, se o espaço alocado para o arquivo com file_id 4 é de 200.000 MB e você quer reduzi-lo para 100.000 MB, você pode primeiro definir o destino como 170.000 MB:

DBCC SHRINKFILE (4, 170000);

Depois que esse comando é concluído, ele trunca o arquivo e reduz seu tamanho alocado para 170.000 MB. Em seguida, você pode repetir esse comando, definindo o destino primeiro para 140.000 MB, depois para 110.000 MB e assim por diante, até que o arquivo seja reduzido para o tamanho desejado. Se o comando for concluído, mas o arquivo não estiver truncado, use etapas menores, por exemplo, 15.000 MB em vez de 30.000 MB.

Para monitorar o progresso da redução para todas as sessões de redução em execução simultânea, você pode usar a seguinte consulta:

SELECT command,
       percent_complete,
       status,
       wait_resource,
       session_id,
       wait_type,
       blocking_session_id,
       cpu_time,
       reads,
       CAST(((DATEDIFF(s,start_time, GETDATE()))/3600) AS varchar) + ' hour(s), '
                     + CAST((DATEDIFF(s,start_time, GETDATE())%3600)/60 AS varchar) + 'min, '
                     + CAST((DATEDIFF(s,start_time, GETDATE())%60) AS varchar) + ' sec' AS running_time
FROM sys.dm_exec_requests AS r
LEFT JOIN sys.databases AS d
ON r.database_id = d.database_id
WHERE r.command IN ('DbccSpaceReclaim','DbccFilesCompact','DbccLOBCompact','DBCC');

Depois que a redução for concluída para todos os arquivos de dados, use a consulta de uso de espaço para determinar a redução resultante no tamanho do armazenamento alocado. Se ainda houver uma grande diferença entre espaço usado e espaço alocado, você poderá recompilar índices. A recompilação pode aumentar temporariamente ainda mais o espaço alocado, no entanto, a redução dos arquivos de dados novamente após a recriação de índices deve resultar em uma redução mais profunda no espaço alocado.

Ampliar um arquivo de log

Na Instância Gerenciada de SQL do Azure, você pode adicionar espaço a um arquivo de log ampliando o arquivo de log existente, se o espaço em disco permitir. Não há suporte para adicionar um arquivo de log ao banco de dados. 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 ampliar o arquivo de log, use a MODIFY FILE cláusula da ALTER DATABASE instrução e especifique a SIZE sintaxe.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 Recomendações.

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

Para gerenciar o crescimento de um arquivo de log de transações, use a instrução de opções de arquivo e grupo de arquivos ALTER DATABASE (Transact-SQL). Observe as seguintes opções:

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

Recomendações

Ao trabalhar com arquivos de log de transações, considere as seguintes recomendações:

• Defina o incremento de crescimento automático (crescimento automático) do log de transações, conforme configurado pela opção FILEGROWTH , para ser grande o suficiente para atender às necessidades de suas transações de carga de trabalho. Faça com que o incremento de crescimento do arquivo em um arquivo de log seja suficientemente grande para evitar expansão frequente. Você pode dimensionar corretamente um log de transações monitorando 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.

• Defina o crescimento automático para arquivos de dados e de log usando a opção FILEGROWTH em size vez de percentagepermitir um melhor controle sobre a taxa de crescimento, pois a porcentagem é uma quantidade cada vez maior.

  • Na Instância Gerenciada de SQL do Azure, a Inicialização Instantânea de Arquivo pode beneficiar eventos de aumento de 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 maiores que 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.

• Evite definir um pequeno incremento de crescimento automático porque ele pode gerar muitas VLFs pequenas e 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.

• Evite definir um incremento de crescimento automático grande porque pode causar dois problemas:

  • O banco de dados pode pausar 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 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 crescimento automático habilitado, você pode receber uma mensagem informando que o log de transações está cheio se ele 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 aumento, confira Opções de arquivo e grupos de arquivos de ALTER DATABASE (Transact-SQL).

• Você pode definir arquivos de log para reduzir automaticamente. No entanto, essa prática não é recomendada e a propriedade de banco de dados auto_shrink é definida como FALSE por padrão. Se você definir auto_shrink 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.

  • O arquivo é reduzido para o tamanho no qual apenas 25% do arquivo é espaço não utilizado ou reduzido para o tamanho original do arquivo, 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).

Conteúdo relacionado

• Backups automatizados na Instância Gerenciada de SQL do Azure
• Opções File e Filegroup de ALTER DATABASE (Transact-SQL)
• Visão geral dos limites de recursos da Instância Gerenciada de SQL do Azure
• Solução de problemas de erros de log de transações com a Instância Gerenciada de SQL do Azure