Compartilhar via

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

  • Artigo

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

Este artigo aborda como monitorar e gerenciar arquivos em bancos de dados na Instância Gerenciada de SQL do Azure. Analisamos 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 aumento de um arquivo de log de transações.

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

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

Noções básicas sobre as quantidades de espaço de armazenamento a seguir são importantes para gerenciar o espaço de arquivo de 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 utilizado 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, excluir uma linha de cada página de dados não diminui necessariamente 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 futuras inserções sejam mais rápidas, já que 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 do espaço de dados alocados não pode crescer além do tamanho máximo 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.

Diagram that demonstrates the size of difference database space concepts in the database quantity table.

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 do arquivo de log atual, seu tamanho máximo e a opção de aumento automático para o arquivo, você também pode usar 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, confira get-metrics 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. 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. Comandos de redução afetam o desempenho do banco de dados enquanto ele está em execução e, se possível, deve ser executado durante períodos de baixa utilização. Não é recomendável reduzir os arquivos de dados se a carga de trabalho regular do aplicativo faz com que os arquivos voltem para o mesmo tamanho alocado novamente.

Esteja ciente do potencial impacto negativo sobre o desempenho causado pela redução de arquivos de banco de dados. Confira Manutenção do índice após a redução. Em casos raros, as operações de redução podem ser afetadas por backups automatizados de banco de dados. Se necessário, repita a operação de 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 a redução de um log, o log de transações aumentará novamente e, fazendo isso, introduzirá uma sobrecarga de desempenho durante as operações de aumento do 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, 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 as operações de redução, revise estes 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 a redução

Após uma operação de redução ser concluída em arquivos de dados, os índices podem ficar fragmentados. Isso reduz a eficácia da otimização de desempenho para determinadas cargas de trabalho, como as 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 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 os arquivos de dados não resultar em uma redução suficiente do espaço alocado, você precisará 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, pois 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. Isso 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á uma compensação entre gastar mais tempo aumentando a densidade de página por um lado e diminuir a duração da redução e atingir mais economia de espaço por outro.

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 uma reserva dinâmica de recursos suficiente para as cargas de trabalho de aplicativos que podem estar em execução. Monitore o consumo de recursos (CPU, E/S de dados, E/S de logs) no portal do Azure ou usando a exibição sys.dm_db_resource_stats e inicie recompilações paralelas adicionais somente se a utilização de recursos em cada uma dessas dimensões permanecer substancialmente inferior a 100%. Se a utilização de CPU, a E/S de dados ou a E/S de logs 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. Isso pode permitir que recompilações paralelas adicionais 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. Isso 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. Faça isso abrindo várias sessões de banco de dados e usando DBCC SHRINKFILE em cada sessão com um valor de file_id diferente. 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 poderá reduzir o espaço alocado para o destino especificado.

Você pode atenuar isso 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);

Quando esse comando for concluído, ele terá truncado o arquivo e reduzido o tamanho alocado para 170.000 MB. Em seguida, você pode repetir o comando, definindo o destino primeiro como 140.000 MB, depois como 110.000 MB etc., 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 coluna percent_complete pode permanecer praticamente inalterado por longos períodos, mesmo que a redução ainda esteja em andamento.

Após a redução ser concluída para todos os arquivos de dados, use a consulta de uso do espaço para determinar a redução resultante no tamanho do armazenamento alocado. Se ainda houver uma grande diferença entre o espaço usado e o espaço alocado, você pode recompilar índices. Isso pode aumentar temporariamente o espaço alocado, mas reduzir os arquivos de dados novamente após a recompilação dos í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, adicione 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 a adição de 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, 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 Recomendações.

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:

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

Recomendações

Estas são algumas recomendações gerais ao trabalhar com arquivos de log de transações:

  • O incremento de aumento automático do log de transações, conforme definido pela opção FILEGROWTH, deve ser grande o suficiente para se manter à frente das necessidades das transações da 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, pois 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 configurar um aumento automático para arquivos de log e de dados usando a opção FILEGROWTH, talvez seja preferível configurá-lo em size em vez de percentage, a fim de permitir um melhor controle da taxa de aumento, pois o percentual é um valor que aumenta constantemente.

    • 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 os novos bancos de dados é de 64 MB. Os eventos de crescimento 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 uma melhor prática, não defina o valor da opção FILEGROWTH como superior a 1.024 MB para logs de transações.

  • Um pequeno incremento de aumento automático pode gerar um número excessivo de VLFs pequenos 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.

  • 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 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 aumento automático habilitado, você pode receber uma mensagem informando que o log de transações está cheio, caso ele não possa aumentar 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).

  • Os arquivos de log podem ser definidos para serem reduzidos automaticamente. No entanto, isso não é recomendável e a propriedade de banco de dados auto_shrink é 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.

Próximas etapas