Gerir o espaço de ficheiros para bases de dados na Base de Dados SQL do Azure

  • Artigo

Aplica-se a:Banco de Dados SQL do Azure

Este artigo descreve diferentes tipos de espaço de armazenamento para bancos de dados no Banco de Dados SQL do Azure e as etapas que podem ser executadas quando o espaço de arquivo alocado precisa ser explicitamente gerenciado.

Descrição geral

Com o Banco de Dados SQL do Azure, há padrões de carga de trabalho em que a alocação de arquivos de dados subjacentes para bancos de dados pode se tornar maior do que o número de páginas de dados usadas. Essa condição pode ocorrer quando o espaço usado aumenta e os dados são excluídos posteriormente. O motivo deve-se ao facto de o espaço de ficheiros alocado não ser recuperado automaticamente quando os dados são eliminados.

Nos cenários abaixo, monitorizar a utilização do espaço de ficheiros e encolher os ficheiros de dados poderá ser necessário:

  • Permita o crescimento dos dados num conjunto elástico quando o espaço de ficheiros alocado às respetivas bases de dados atingir o tamanho máximo do conjunto.
  • Permita a diminuição do tamanho máximo de uma base de dados individual ou de um conjunto elástico.
  • Permita a alteração de uma base de dados individual ou de um conjunto elástico para outro escalão de serviço ou escalão de desempenho com um tamanho máximo mais baixo.

Nota

As operações de encolhimento 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.

Monitorar o uso do espaço de arquivo

A maioria das métricas de espaço de armazenamento exibidas nas APIs a seguir medem apenas o tamanho das páginas de dados usadas:

  • APIs de métricas baseadas no Azure Resource Manager, incluindo get-metrics do PowerShell

No entanto, as APIs seguintes também medem o tamanho do espaço alocado para bases de dados e conjuntos elásticos:

Compreender 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 bases de dados Definição Comentários
Espaço de dados utilizado A quantidade de espaço usada para armazenar dados do banco de dados. Geralmente, o espaço utilizado aumenta (diminui) nas inserções (elimina). Em alguns casos, o espaço utilizado não muda nas inserções ou exclusões dependendo da quantidade e padrão de dados envolvidos na operação e de qualquer fragmentação. Por exemplo, eliminar uma linha de cada página de dados não diminui necessariamente o espaço utilizado.
Espaço de dados alocado A quantidade de espaço de ficheiros formatados disponibilizada para armazenar dados de bases de dados. A quantidade de espaço alocado aumenta automaticamente, mas nunca diminui após eliminações. Esse comportamento garante que as inserções futuras sejam mais rápidas, já que o espaço não precisa ser reformatado.
Espaço de dados atribuído, mas não utilizado A diferença entre a quantidade de espaço de dados alocado e o espaço de dados utilizado. Essa quantidade representa a quantidade máxima de espaço livre que pode ser recuperada reduzindo os arquivos de dados do banco de dados.
Tamanho máximo dos dados A quantidade máxima de espaço que pode ser usada para armazenar dados do banco de dados. A quantidade de espaço de dados alocado não pode ultrapassar o tamanho máximo dos dados.

O diagrama a seguir ilustra a relação entre os diferentes tipos 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 único banco de dados para obter informações de espaço de arquivo

Use a consulta a seguir no sys.database_files para retornar a quantidade de espaço de arquivo de banco de dados alocado e a quantidade de espaço não utilizado alocado. As unidades do resultado da consulta estã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;

Compreender os tipos de espaço de armazenamento para um pool elástico

Compreender as seguintes quantidades de espaço de armazenamento é importante para gerenciar o espaço de arquivo de um pool elástico.

Quantidade de conjuntos elásticos Definição Comentários
Espaço de dados utilizado A soma do espaço de dados utilizado por todas as bases de dados no conjunto elástico.
Espaço de dados alocado A soma do espaço de dados alocado por todas as bases de dados no conjunto elástico.
Espaço de dados atribuído, mas não utilizado A diferença entre a quantidade de espaço de dados alocado e o espaço de dados utilizado por todas as bases de dados no conjunto elástico. Essa quantidade representa a quantidade máxima de espaço alocado para o pool elástico que pode ser recuperado reduzindo os arquivos de dados do banco de dados.
Tamanho máximo dos dados A quantidade máxima de espaço de dados que pode ser usada pelo pool elástico para todos os seus bancos de dados. O espaço alocado ao conjunto elástico não deve exceder o tamanho máximo do conjunto elástico. Se essa condição ocorrer, o espaço alocado que não é utilizado pode ser recuperado reduzindo os arquivos de dados do banco de dados.

Nota

A mensagem de erro "O pool elástico atingiu seu limite de armazenamento" indica que os objetos de banco de dados foram alocados espaço suficiente para atender ao limite de armazenamento do pool elástico, mas pode haver espaço não utilizado na alocação de espaço de dados. Considere aumentar o limite de armazenamento do pool elástico ou, como uma solução de curto prazo, liberar espaço de dados usando as amostras em Recuperar espaço alocado não utilizado. Você também deve estar 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.

Consultar um pool elástico para obter informações de espaço de armazenamento

Pode utilizar as seguintes consultas para determinar as quantidades de espaço de armazenamento de um conjunto elástico.

Espaço de dados do conjunto elástico utilizado

Modifique a consulta a seguir para retornar a quantidade de espaço de dados do pool elástico usado. As unidades do resultado da consulta estão em MB.

-- Connect to master
-- Elastic pool data space used in MB  
SELECT TOP 1 avg_storage_percent / 100.0 * elastic_pool_storage_limit_mb AS ElasticPoolDataSpaceUsedInMB
FROM sys.elastic_pool_resource_stats
WHERE elastic_pool_name = 'ep1'
ORDER BY end_time DESC;

Espaço de dados do pool elástico alocado e espaço alocado não utilizado

Modifique os exemplos a seguir para retornar uma tabela listando o espaço alocado e o espaço alocado não utilizado para cada banco de dados em um pool elástico. A tabela ordena os bancos de dados dos bancos de dados com a maior quantidade de espaço alocado não utilizado para a menor quantidade de espaço alocado não utilizado. As unidades do resultado da consulta estão em MB.

Os resultados da consulta para determinar o espaço alocado para cada banco de dados no pool podem ser adicionados para determinar o espaço total alocado para o pool elástico. O espaço de piscina elástica alocado não deve exceder o tamanho máximo da piscina elástica.

Importante

O módulo PowerShell Azure Resource Manager ainda é suportado pelo Banco de Dados SQL do Azure, mas todo o desenvolvimento futuro é para o módulo Az.Sql. O módulo AzureRM continuará a receber correções de bugs até, pelo menos, dezembro de 2020. Os argumentos para os comandos no módulo Az e nos módulos AzureRm são substancialmente idênticos. Para obter mais informações sobre sua compatibilidade, consulte Apresentando o novo módulo Az do Azure PowerShell.

O script do PowerShell requer o módulo do SQL Server PowerShell – consulte Baixar o módulo do PowerShell para instalar.

$resourceGroupName = "<resourceGroupName>"
$serverName = "<serverName>"
$poolName = "<poolName>"
$userName = "<userName>"
$password = "<password>"

# get list of databases in elastic pool
$databasesInPool = Get-AzSqlElasticPoolDatabase -ResourceGroupName $resourceGroupName `
    -ServerName $serverName -ElasticPoolName $poolName
$databaseStorageMetrics = @()

# for each database in the elastic pool, get space allocated in MB and space allocated unused in MB
foreach ($database in $databasesInPool) {
    $sqlCommand = "SELECT DB_NAME() as DatabaseName, `
    SUM(size/128.0) AS DatabaseDataSpaceAllocatedInMB, `
    SUM(size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0) AS DatabaseDataSpaceAllocatedUnusedInMB `
    FROM sys.database_files `
    GROUP BY type_desc `
    HAVING type_desc = 'ROWS'"
    $serverFqdn = "tcp:" + $serverName + ".database.windows.net,1433"
    $databaseStorageMetrics = $databaseStorageMetrics + 
        (Invoke-Sqlcmd -ServerInstance $serverFqdn -Database $database.DatabaseName `
            -Username $userName -Password $password -Query $sqlCommand)
}

# display databases in descending order of space allocated unused
Write-Output "`n" "ElasticPoolName: $poolName"
Write-Output $databaseStorageMetrics | Sort -Property DatabaseDataSpaceAllocatedUnusedInMB -Descending | Format-Table

A captura de tela a seguir é um exemplo da saída do script:

A screenshot of the output of the related PowerShell cmdlet, showing elastic pool allocated space and unused allocated space.

Tamanho máximo dos dados do conjunto elástico

Modifique a seguinte consulta T-SQL para retornar o tamanho máximo dos últimos dados do pool elástico registrados. As unidades do resultado da consulta estão em MB.

-- Connect to master
-- Elastic pools max size in MB
SELECT TOP 1 elastic_pool_storage_limit_mb AS ElasticPoolMaxSizeInMB
FROM sys.elastic_pool_resource_stats
WHERE elastic_pool_name = 'ep1'
ORDER BY end_time DESC;

Recuperar o espaço alocado não utilizado

Importante

O comando de redução afeta o desempenho da base de dados durante a execução e, se possível, deve ser executado durante períodos de baixa utilização.

Reduzir arquivos de dados

Devido a um impacto potencial no desempenho do banco de dados, o Banco de Dados SQL do Azure não reduz automaticamente os arquivos de dados. No entanto, os clientes podem reduzir os arquivos de dados via autoatendimento no momento de sua escolha. Esta não deve ser uma operação programada regularmente, mas sim um evento único em resposta a uma grande redução no consumo de espaço utilizado pelo arquivo de dados.

Gorjeta

Não é aconselhável encolher ficheiros de dados se a carga de trabalho regular das aplicações fizer com que os ficheiros voltem a aumentar para o mesmo tamanho alocado.

No Banco de Dados SQL do Azure, para reduzir arquivos, você pode usar um ou DBCC SHRINKDATABASEDBCC SHRINKFILE comandos:

  • DBCC SHRINKDATABASE Reduz todos os dados e arquivos de log em um banco de dados usando um único comando. O comando reduz um arquivo de dados de cada vez, o que pode levar muito tempo para bancos de dados maiores. Ele também reduz o arquivo de log, o que geralmente é desnecessário porque o Banco de Dados SQL do Azure reduz os arquivos de log automaticamente conforme necessário.
  • DBCC SHRINKFILE O comando suporta cenários mais avançados:
    • Ele pode direcionar arquivos individuais conforme necessário, em vez de reduzir todos os arquivos no banco de dados.
    • Cada DBCC SHRINKFILE comando pode ser executado em paralelo com outros DBCC SHRINKFILE comandos para reduzir vários arquivos ao mesmo tempo e reduzir o tempo total de encolhimento, às custas de um maior uso de recursos e uma maior chance de bloquear consultas do usuário, se estiverem sendo executadas durante a redução.
    • Se a cauda do arquivo não contiver dados, ele pode reduzir o tamanho do arquivo alocado muito mais rápido, especificando o TRUNCATEONLY argumento. Isso não requer movimentação de dados dentro do arquivo.
  • Para obter mais informações sobre esses comandos shrink, consulte DBCC SHRINKDATABASE e DBCC SHRINKFILE.

Os exemplos a seguir devem ser executados enquanto conectados ao banco de dados do usuário de destino, não ao master banco de dados.

Para usar DBCC SHRINKDATABASE para reduzir todos os dados e arquivos de log em um determinado banco de dados:

-- Shrink database data space allocated.
DBCC SHRINKDATABASE (N'database_name');

No Banco de Dados SQL do Azure, um banco de dados pode ter um ou mais arquivos de dados, criados automaticamente à medida que os dados crescem. Para determinar o layout de arquivo do banco de dados, incluindo o tamanho usado e alocado de cada arquivo, consulte a sys.database_files exibição de catálogo usando o seguinte script de exemplo:

-- Review file properties, including file_id and name values to reference in shrink commands
SELECT file_id,
       name,
       CAST(FILEPROPERTY(name, 'SpaceUsed') AS bigint) * 8 / 1024. AS space_used_mb,
       CAST(size AS bigint) * 8 / 1024. AS space_allocated_mb,
       CAST(max_size AS bigint) * 8 / 1024. AS max_file_size_mb
FROM sys.database_files
WHERE type_desc IN ('ROWS','LOG');

Você pode executar uma redução em relação a um arquivo somente por meio do DBCC SHRINKFILE comando, por exemplo:

-- Shrink database data file named 'data_0` by removing all unused at the end of the file, if any.
DBCC SHRINKFILE ('data_0', TRUNCATEONLY);
GO

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.

Reduzir arquivo de log de transações

Ao contrário dos ficheiros de dados, a Base de Dados SQL do Azure encolhe automaticamente o ficheiro de registo de transações para evitar uma utilização excessiva do espaço, o que pode originar erros de espaço insuficiente. Normalmente, não é necessário que os clientes encolham o ficheiro de registo de transações.

Nos níveis de serviço Premium e Business Critical, se o log de transações se tornar grande, ele poderá contribuir significativamente para o consumo de armazenamento local em direção ao limite máximo de armazenamento local. Se o consumo de armazenamento local estiver próximo do limite, os clientes poderão optar por reduzir o log de transações usando o comando DBCC SHRINKFILE , conforme mostrado no exemplo a seguir. Isso libera o armazenamento local assim que o comando é concluído, sem esperar pela operação de redução automática periódica.

O exemplo a seguir deve ser executado enquanto conectado ao banco de dados de usuário de destino, não ao master banco de dados.

-- Shrink the database log file (always file_id 2), by removing all unused space at the end of the file, if any.
DBCC SHRINKFILE (2, TRUNCATEONLY);

Encolhimento automático

Como alternativa à redução manual de arquivos de dados, a redução automática pode ser habilitada para um banco de dados. No entanto, a redução automática pode ser menos eficaz na recuperação de espaço de arquivo do que DBCC SHRINKDATABASE e DBCC SHRINKFILE.

Por padrão, a redução automática está desabilitada, o que é recomendado para a maioria dos bancos de dados. Se for necessário ativar a redução automática, recomenda-se desativá-la assim que as metas de gerenciamento de espaço forem alcançadas, em vez de mantê-la habilitada permanentemente. Para obter mais informações, veja Considerações da AUTO_SHRINK.

Por exemplo, a redução automática pode ser útil no cenário específico em que um pool elástico contém muitos bancos de dados que experimentam crescimento e redução significativos no espaço de arquivo de dados usado, fazendo com que o pool se aproxime de seu limite de tamanho máximo. Este não é um cenário comum.

Para habilitar a redução automática, execute o seguinte comando enquanto estiver conectado ao seu banco de dados (não ao master banco de dados).

-- Enable auto-shrink for the current database.
ALTER DATABASE CURRENT SET AUTO_SHRINK ON;

Para obter mais informações sobre esse comando, consulte Opções de conjunto de banco de dados.

Manutenção do índice após a redução

Depois que uma operação de redução é concluída em relação aos arquivos de dados, os índices podem ficar fragmentados. Isso reduz a eficácia da otimização de desempenho para determinadas cargas de trabalho, como consultas usando verificações grandes. Se ocorrer degradação do desempenho após a conclusão da operação de redução, considere a manutenção do índice para reconstruir os índices. Tenha em mente que as reconstruções de índice exigem espaço livre no banco de dados e, portanto, podem fazer com que o espaço alocado aumente, neutralizando o efeito de encolhimento.

Para obter mais informações sobre a manutenção do índice, consulte Otimizar a manutenção do índice para melhorar o desempenho da consulta e reduzir o consumo de recursos.

Reduzir bancos de dados grandes

Quando o espaço alocado do banco de dados está em centenas de gigabytes ou mais, a redução pode exigir um tempo significativo para ser concluído, geralmente medido em horas ou dias para bancos de dados de vários terabytes. Há otimizações de processo e práticas recomendadas que você pode usar para tornar esse processo mais eficiente e menos impactante para as cargas de trabalho do aplicativo.

Linha de base de uso de espaço de captura

Antes de iniciar a redução, capture o espaço atual usado e alocado em cada arquivo de banco de dados executando a seguinte consulta de uso de espaço:

SELECT file_id,
       CAST(FILEPROPERTY(name, 'SpaceUsed') AS bigint) * 8 / 1024. AS space_used_mb,
       CAST(size AS bigint) * 8 / 1024. AS space_allocated_mb,
       CAST(max_size AS bigint) * 8 / 1024. AS max_size_mb
FROM sys.database_files
WHERE type_desc = 'ROWS';

Quando a redução for concluída, você poderá executar essa consulta novamente e comparar o resultado com a linha de base inicial.

Truncar arquivos de dados

Recomenda-se primeiro executar shrink para cada arquivo de dados com o TRUNCATEONLY parâmetro. Desta forma, se houver algum espaço alocado mas não utilizado no final do arquivo, ele é removido rapidamente e sem qualquer movimento de dados. O comando de exemplo a seguir trunca o arquivo de dados com o file_id 4:

DBCC SHRINKFILE (4, TRUNCATEONLY);

Depois que esse comando for executado para cada arquivo de dados, você poderá executar novamente a consulta de uso de espaço para ver a redução no espaço alocado, se houver. Você também pode exibir o espaço alocado para o banco de dados no portal do Azure.

Avaliar a densidade da página de índice

Se truncar arquivos de dados não resultou em uma redução suficiente no espaço alocado, você precisará reduzir os arquivos de dados. No entanto, como uma etapa opcional, mas recomendada, você deve primeiro determinar a densidade média de páginas para í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 de página antes de reduzir os arquivos de dados. Isso também permitirá que o shrink obtenha 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 da página é relatada avg_page_space_used_in_percent na coluna.

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 com densidade de página inferior a 60-70%, considere reconstruir ou reorganizar esses índices antes de reduzir os arquivos de dados.

Nota

Para bancos de dados maiores, a consulta para determinar a densidade da página pode levar muito tempo (horas) para ser concluída. Além disso, reconstruir ou reorganizar grandes índices também requer tempo substancial e uso de recursos. Há uma compensação entre gastar tempo extra no aumento da densidade de páginas, por um lado, e reduzir a duração da redução e obter maiores economias de espaço, por outro.

Se houver vários índices com baixa densidade de página, você poderá reconstruí-los em paralelo em várias sessões de banco de dados para acelerar o processo. No entanto, certifique-se de que 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 de aplicativos que possam estar em execução. Monitore o consumo de recursos (CPU, E/S de Dados, E/S de Log) no portal do Azure ou usando o modo de exibição sys.dm_db_resource_stats e inicie reconstruçõ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, E/S de dados ou E/S de log estiver em 100%, você poderá dimensionar o banco de dados para ter mais núcleos de CPU e aumentar a taxa de transferência de E/S. Isso pode permitir reconstruções paralelas adicionais para concluir o processo mais rapidamente.

Exemplo de comando de reconstrução de índice

A seguir está um comando de exemplo para reconstruir 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);

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

Para saber mais sobre a manutenção do índice, consulte Otimizar a manutenção do índice para melhorar o desempenho da consulta e reduzir o consumo de recursos.

Reduzir vários arquivos de dados

Como observado anteriormente, reduzir com a movimentação de dados é um processo de longa duração. Se o banco de dados tiver vários arquivos de dados, você pode acelerar o processo reduzindo vários arquivos de dados em paralelo. Para fazer isso, abra várias sessões de banco de dados e use DBCC SHRINKFILE em cada sessão com um valor diferente file_id . Semelhante à reconstrução de índices anteriormente, certifique-se de ter espaço suficiente para recursos (CPU, E/S de dados, E/S de log) antes de iniciar cada novo comando de redução paralela.

O comando de exemplo a seguir reduz o arquivo de dados com o file_id 4, tentando reduzir seu tamanho alocado para 52.000 MB movendo páginas dentro do arquivo:

DBCC SHRINKFILE (4, 52000);

Se você 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 a redução seja concluída e trunce o arquivo. Nesse caso, a redução não será capaz de reduzir o espaço alocado para o destino especificado.

Você pode atenuar isso reduzindo cada arquivo em etapas menores. Isso significa que, no DBCC SHRINKFILE comando, você define o destino que é um pouco menor do que o espaço alocado atual para o arquivo, como visto nos resultados da consulta de uso do espaço da linha de base. Por exemplo, se o espaço alocado para o arquivo com file_id 4 for de 200.000 MB e você quiser reduzi-lo para 100.000 MB, você pode primeiro definir o destino para 170.000 MB:

DBCC SHRINKFILE (4, 170000);

Quando esse comando for concluído, ele terá truncado o arquivo e reduzido 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, 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');

Nota

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

Quando a redução for concluída para todos os arquivos de dados, execute novamente a consulta de uso de espaço (ou verifique no portal do Azure) para determinar a redução resultante no tamanho de armazenamento alocado. Se ainda houver uma grande diferença entre o espaço usado e o espaço alocado, você poderá reconstruir os índices conforme descrito anteriormente. Isso pode aumentar temporariamente o espaço alocado ainda mais, no entanto, reduzir os arquivos de dados novamente após a reconstrução dos índices deve resultar em uma redução mais profunda no espaço alocado.

Erros transitórios durante a redução

Ocasionalmente, um comando shrink pode falhar com vários erros, como tempos limite e deadlocks. Em geral, esses erros são transitórios e não ocorrem novamente se o mesmo comando for repetido. Se shrink falhar com um erro, o progresso feito até agora na movimentação de páginas de dados será mantido, e o mesmo comando shrink poderá ser executado novamente para continuar reduzindo o arquivo.

O script de exemplo a seguir mostra como você pode executar shrink em um loop de repetição para tentar novamente automaticamente até um número configurável de vezes quando ocorre um erro de tempo limite ou um erro de deadlock. Essa abordagem de repetição é aplicável a muitos outros erros que podem ocorrer durante a redução.

DECLARE @RetryCount int = 3; -- adjust to configure desired number of retries
DECLARE @Delay char(12);

-- Retry loop
WHILE @RetryCount >= 0
BEGIN

BEGIN TRY

DBCC SHRINKFILE (1); -- adjust file_id and other shrink parameters

-- Exit retry loop on successful execution
SELECT @RetryCount = -1;

END TRY
BEGIN CATCH
    -- Retry for the declared number of times without raising an error if deadlocked or timed out waiting for a lock
    IF ERROR_NUMBER() IN (1205, 49516) AND @RetryCount > 0
    BEGIN
        SELECT @RetryCount -= 1;

        PRINT CONCAT('Retry at ', SYSUTCDATETIME());

        -- Wait for a random period of time between 1 and 10 seconds before retrying
        SELECT @Delay = '00:00:0' + CAST(CAST(1 + RAND() * 8.999 AS decimal(5,3)) AS varchar(5));
        WAITFOR DELAY @Delay;
    END
    ELSE -- Raise error and exit loop
    BEGIN
        SELECT @RetryCount = -1;
        THROW;
    END
END CATCH
END;

Além de tempos limite e bloqueios, a redução pode encontrar erros devido a certos problemas conhecidos.

Os erros retornados e as etapas de mitigação são os seguintes:

  • Número do erro: 49503, mensagem de erro: %.*ls: A página %d: %d não pôde ser movida porque é uma página de armazenamento de versão persistente fora da linha. Motivo da retenção da página: %ls. Carimbo de data/hora de retenção de página: %I64d.

Este erro ocorre quando há transações ativas de longa execução que geraram versões de linha no armazenamento de versão persistente (PVS). As páginas que contêm essas versões de linha não podem ser movidas por encolhimento, portanto, não pode progredir e falha com esse erro.

Para atenuar, você precisa esperar até que essas transações de longa duração sejam concluídas. Como alternativa, você pode identificar e encerrar essas transações de longa duração, mas isso pode afetar seu aplicativo se ele não lidar com falhas de transação normalmente. Uma maneira de encontrar transações de longa execução é executando a seguinte consulta no banco de dados onde você executou o comando shrink:

-- Transactions sorted by duration
SELECT st.session_id,
       dt.database_transaction_begin_time,
       DATEDIFF(second, dt.database_transaction_begin_time, CURRENT_TIMESTAMP) AS transaction_duration_seconds,
       dt.database_transaction_log_bytes_used,
       dt.database_transaction_log_bytes_reserved,
       st.is_user_transaction,
       st.open_transaction_count,
       ib.event_type,
       ib.parameters,
       ib.event_info
FROM sys.dm_tran_database_transactions AS dt
INNER JOIN sys.dm_tran_session_transactions AS st
ON dt.transaction_id = st.transaction_id
OUTER APPLY sys.dm_exec_input_buffer(st.session_id, default) AS ib
WHERE dt.database_id = DB_ID()
ORDER BY transaction_duration_seconds DESC;

Você pode encerrar uma transação usando o comando e especificando o KILL valor associado session_id do resultado da consulta:

KILL 4242; -- replace 4242 with the session_id value from query results

Atenção

Encerrar uma transação pode afetar negativamente as cargas de trabalho.

Uma vez que as transações de longa execução tenham sido encerradas ou concluídas, uma tarefa interna em segundo plano limpará as versões de linha que não são mais necessárias depois de algum tempo. Você pode monitorar o tamanho do PVS para avaliar o progresso da limpeza, usando a consulta a seguir. Execute a consulta no banco de dados onde você executou o comando shrink:

SELECT pvss.persistent_version_store_size_kb / 1024. / 1024 AS persistent_version_store_size_gb,
       pvss.online_index_version_store_size_kb / 1024. / 1024 AS online_index_version_store_size_gb,
       pvss.current_aborted_transaction_count,
       pvss.aborted_version_cleaner_start_time,
       pvss.aborted_version_cleaner_end_time,
       dt.database_transaction_begin_time AS oldest_transaction_begin_time,
       asdt.session_id AS active_transaction_session_id,
       asdt.elapsed_time_seconds AS active_transaction_elapsed_time_seconds
FROM sys.dm_tran_persistent_version_store_stats AS pvss
LEFT JOIN sys.dm_tran_database_transactions AS dt
ON pvss.oldest_active_transaction_id = dt.transaction_id
   AND
   pvss.database_id = dt.database_id
LEFT JOIN sys.dm_tran_active_snapshot_database_transactions AS asdt
ON pvss.min_transaction_timestamp = asdt.transaction_sequence_num
   OR
   pvss.online_index_min_transaction_timestamp = asdt.transaction_sequence_num
WHERE pvss.database_id = DB_ID();

Uma vez que o tamanho do PVS relatado na coluna é substancialmente reduzido em comparação com seu tamanho original, a persistent_version_store_size_gb reexecução do encolhimento deve ser bem-sucedida.

  • Número do erro: 5223, mensagem de erro: %.*ls: A página vazia %d: %d não pôde ser deslocalizada.

Esse erro pode ocorrer se houver operações de manutenção de índice em andamento, como ALTER INDEX. Repita o comando shrink depois que essas operações forem concluídas.

Se este erro persistir, o índice associado poderá ter de ser reconstruído. Para localizar o índice a ser reconstruído, execute a seguinte consulta no mesmo banco de dados em que você executou o comando shrink:

SELECT OBJECT_SCHEMA_NAME(pg.object_id) AS schema_name,
       OBJECT_NAME(pg.object_id) AS object_name,
       i.name AS index_name,
       p.partition_number
FROM sys.dm_db_page_info(DB_ID(), <file_id>, <page_id>, default) AS pg
INNER JOIN sys.indexes AS i
ON pg.object_id = i.object_id
   AND
   pg.index_id = i.index_id
INNER JOIN sys.partitions AS p
ON pg.partition_id = p.partition_id;

Antes de executar essa consulta, substitua os <file_id> espaços reservados e <page_id> pelos valores reais da mensagem de erro recebida. Por exemplo, se a mensagem for Página vazia 1:62669 não pôde ser deslocalizada, então <file_id> é e <page_id> é 626691 .

Recrie o índice identificado pela consulta e tente novamente o comando shrink.

  • Número do erro: 5201, mensagem de erro: DBCC SHRINKDATABASE: ID do arquivo %d do ID do banco de dados %d foi ignorado porque o arquivo não tem espaço livre suficiente para recuperar.

Este erro significa que o arquivo de dados não pode ser encolhido ainda mais. Você pode passar para o próximo arquivo de dados.

Próximos passos

Para obter informações sobre tamanhos máximos de banco de dados, consulte: