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.

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 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 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 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 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 de espaço de dados alocada não pode crescer além do tamanho máximo dos dados.

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

Diagrama que demonstra os conceitos de diferença de tamanho do espaço do banco de dados na tabela de quantidade do 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;

Monitore 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 no momento 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, use as colunas size, max_size e growth 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.

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 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 e, se possível, devem ser executados durante períodos de baixo uso. Não é recomendável reduzir arquivos de dados se a carga de trabalho regular do aplicativo 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. 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 voltar a ser necessário após um encolhimento do log, o log de transações voltará a crescer e, com isso, gerará sobrecarga de desempenho durante as operações de crescimento do 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, a redução do log pode não ser possível até depois do próximo truncamento de log.

Fatores, como uma transação de longa duração, podem manter os VLFs ativos por um período prolongado, restringir o encolhimento do log ou até mesmo impedir completamente o encolhimento do log. Para obter informações, consulte Fatores que podem atrasar o truncamento do 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). Ao reduzir um arquivo de log de transações, os VLFs inativos são removidos do final do arquivo de log para reduzir o arquivo de log a aproximadamente o tamanho desejado.

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)

Monitorar eventos de redução de arquivo de log

Monitorar o espaço de log

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 do desempenho de um índice para determinadas cargas de trabalho, como consultas que usam varreduras extensas. 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 da página for alta, porque terá que mover 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 também permitirá que a redução seja 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.

Observação

Para bancos de dados maiores, a consulta para determinar a densidade de página pode levar muito tempo (horas) para ser concluída. Além disso, recompilar ou reorganizar índices grandes também requer tempo e uso de recursos substanciais. Há um equilíbrio entre, por um lado, investir mais tempo para aumentar a densidade das páginas e, por outro, reduzir a duração do processo de compactação e obter maior economia de espaço.

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 por meio da 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ê pode escalar verticalmente o banco de dados para obter mais núcleos de CPU e aumentar a taxa de transferência de E/S, permitindo mais reconstruções em paralelo para concluir 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 operação de redução não consegue diminuir 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');

Observação

O progresso da redução pode ser não linear e o valor na percent_complete coluna pode permanecer inalterado por longos períodos de tempo, mesmo que a redução ainda esteja em andamento.

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 recriação pode aumentar temporariamente ainda mais o espaço alocado; no entanto, reduzir novamente os arquivos de dados após recriar os índices deve resultar em uma redução maior do 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 aumentar o arquivo de log, use a cláusula MODIFY FILE da instrução ALTER DATABASE e especifique 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 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 ALTER DATABASE (Transact-SQL) Opções de arquivo e grupo de arquivos. 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 do log de transações, conforme configurado pela opção FILEGROWTH para um valor grande o suficiente para atender às necessidades das transações da sua carga de trabalho. Defina o incremento de crescimento do arquivo de log como suficientemente grande para evitar expansões frequentes. 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 autogrow para os arquivos de dados e de log usando a opção FILEGROWTH em size, em vez de percentage, para permitir um controle melhor sobre a taxa de crescimento, pois a porcentagem é um valor que aumenta continuamente.

    • Na Instância Gerenciada de 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 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 dos VLFs para o tamanho atual do log de transações de todos os bancos de dados de uma determinada instância e os incrementos de crescimento necessários para atingir o tamanho exigido, confira este script para analisar e corrigir VLFs, fornecido pela equipe SQL Tiger Team.

  • 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 VLFs 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 requerido, consulte este script para analisar e corrigir VLFs, fornecido pela equipe SQL Tiger.

  • 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, consulte Opções de arquivo e grupo de arquivos 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.

Conteúdo relacionado

  • Last updated on