DBCC SHRINKFILE (Transact-SQL)

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada de SQL do Azure

Reduz o tamanho do arquivo de log ou dos dados especificados do banco de dados atual. Você pode usá-lo para mover os dados de um arquivo para outros no mesmo grupo de arquivos, o que esvazia o arquivo e permite a remoção do banco de dados dele. Você pode reduzir um arquivo a menos da metade do tamanho de criação dele, definindo um novo valor para o tamanho mínimo do arquivo.

Convenções de sintaxe de Transact-SQL

Sintaxe

DBCC SHRINKFILE   
(  
    { file_name | file_id }   
    { [ , EMPTYFILE ]   
    | [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]  
    }  
)  
[ WITH 
  {     
      [ WAIT_AT_LOW_PRIORITY 
        [ ( 
            <wait_at_low_priority_option_list>
        )] 
      ] 
      [ , NO_INFOMSGS]
  }
]
       
< wait_at_low_priority_option_list > ::=  
    <wait_at_low_priority_option>
    | <wait_at_low_priority_option_list> , <wait_at_low_priority_option>
 
< wait_at_low_priority_option > ::=
    ABORT_AFTER_WAIT = { SELF | BLOCKERS }

Argumentos

file_name

O nome lógico do arquivo que será reduzido.

file_id

O número de identificação (ID) do arquivo que será reduzido. Para obter uma ID de arquivo, use a função do sistema FILE_IDEX ou confira a exibição do catálogo sys.database_files do banco de dados atual.

target_size

Um inteiro que representa o novo tamanho de megabyte do arquivo. Se for 0 ou não estiver especificado, DBCC SHRINKFILE fará a redução para o tamanho de criação do arquivo.

É possível reduzir o tamanho padrão de um arquivo vazio usando DBCC SHRINKFILE <target_size>. Por exemplo, se você cria um arquivo com 5 MB e depois o reduz para 3 MB enquanto o arquivo ainda está vazio, o tamanho do arquivo padrão é definido como 3 MB. Isso só se aplica a arquivos vazios que nunca contiveram dados.

Essa opção não tem suporte em contêineres de grupo de arquivos FILESTREAM.

Se especificado, DBCC SHRINKFILE tentará reduzir o arquivo até o target_size. As páginas usadas na área do arquivo a ser liberadas serão movidas para espaço livres nas áreas do arquivo que serão mantidas. Por exemplo, com um arquivo de dados de 10 MB, uma operação DBCC SHRINKFILE com um target_size 8 move todas as páginas usadas nos últimos 2 MB do arquivo para quaisquer páginas não alocadas nos primeiros 8 MB dele. DBCC SHRINKFILE não reduz um arquivo além do tamanho necessário para os dados armazenados. Por exemplo, quando você usa 7 MB de um arquivo de dados de 10 MB, uma instrução DBCC SHRINKFILE com target_size igual a 6 reduz o arquivo para somente 7 MB, não 6 MB.

EMPTYFILE

Migra todos os dados do arquivo especificado para outros arquivos no mesmo grupo de arquivos. Em outras palavras, EMPTYFILE migra dados de um arquivo especificado para outros no mesmo grupo de arquivos. EMPTYFILE garante que nenhum dado seja adicionado ao arquivo, apesar de ele não ser somente leitura. Você pode usar a instrução ALTER DATABASE para remover um arquivo. Se você usar a instrução ALTER DATABASE para alterar o tamanho do arquivo, o sinalizador somente leitura será redefinido e dados poderão ser adicionados.

No caso de contêineres de grupo de arquivos FILESTREAM, não é possível usar ALTER DATABASE para remover um arquivo até que o Coletor de Lixo FILESTREAM tenha executado e excluído todos os arquivos de contêiner de grupo de arquivos desnecessários que EMPTYFILE copiou para outro contêiner. Para obter mais informações, confira sp_filestream_force_garbage_collection. Para obter informações sobre como remover um contêiner FILESTREAM, consulte a seção correspondente em Opções de arquivo e grupos de arquivos de ALTER DATABASE (Transact-SQL)

NOTRUNCATE

Move páginas alocadas do final de um arquivo de dados para páginas não alocadas no início de um arquivo com ou sem a especificação de target_percent. O espaço livre no final do arquivo não é devolvido ao sistema operacional e o tamanho físico do arquivo não é alterado. Portanto, se NOTRUNCATE for especificado, o arquivo não será reduzido.

NOTRUNCATE é aplicável somente a arquivos de dados. Os arquivos de log não são afetados.

Essa opção não tem suporte em contêineres de grupo de arquivos FILESTREAM.

TRUNCATEONLY

Libera todo o espaço livre no final do arquivo para o sistema operacional, mas não executa nenhuma movimentação de página no arquivo. O arquivo de dados é reduzido somente para a última extensão alocada.

O target_size será ignorado se for especificado com TRUNCATEONLY.

A opção TRUNCATEONLY não move informações no log, mas remove VLFs inativos do final do arquivo de log. Essa opção não tem suporte em contêineres de grupo de arquivos FILESTREAM.

WITH NO_INFOMSGS

Suprime todas as mensagens informativas.

WAIT_AT_LOW_PRIORITY com operações de redução

Aplica-se a: SQL Server 2022 (16.x) e versões posteriores, Banco de Dados SQL do Azure, Instância Gerenciada de SQL do Azure

O recurso de espera de baixa prioridade reduz a contenção do bloqueio. Para obter mais informações, confira Noções básicas sobre problemas de simultaneidade com DBCC SHRINKDATABASE.

Esse recurso é semelhante a WAIT_AT_LOW_PRIORITY com operações de índice online, mas com algumas diferenças.

• Não é possível especificar a opção ABORT_AFTER_WAIT como NONE.

WAIT_AT_LOW_PRIORITY

Aplica-se a: SQL Server (SQL Server 2022 (16.x) e posterior) e Banco de Dados SQL do Azure.

Quando um comando de redução é executado no modo WAIT_AT_LOW_PRIORITY, as novas consultas que exigem bloqueios de Sch-S (estabilidade de esquema) não são bloqueadas pela operação de redução em espera até que a espera termine e a execução da operação seja iniciada. A operação de redução será executada quando puder obter um bloqueio Sch-M (modificação de esquema). Se uma nova operação de redução no modo WAIT_AT_LOW_PRIORITY não puder obter um bloqueio devido a uma consulta de longa execução, a operação de redução acabará atingindo o tempo limite após 1 minuto por padrão e será encerrada silenciosamente.

Se uma nova operação de redução no modo WAIT_AT_LOW_PRIORITY não puder obter um bloqueio devido a uma consulta de longa execução, a operação de redução acabará atingindo o tempo limite após 1 minuto por padrão e será encerrada silenciosamente. Isso ocorrerá se a operação de redução não puder obter o bloqueio Sch-M devido a consultas simultâneas ou a consultas que contenham bloqueios Sch-S. Quando o tempo limite for atingido, uma mensagem de erro 49516 será enviada ao log de erros do SQL Server, por exemplo: Msg 49516, Level 16, State 1, Line 134 Shrink timeout waiting to acquire schema modify lock in WLP mode to process IAM pageID 1:2865 on database ID 5. Neste ponto, você pode simplesmente repetir a operação de redução no modo WAIT_AT_LOW_PRIORITY sabendo que não haverá impacto no aplicativo.

ABORT_AFTER_WAIT = [ SELF | BLOCKERS ]

Aplica-se a: SQL Server (SQL Server 2022 (16.x) e posterior) e Banco de Dados SQL do Azure.

• SELF

  Saia da operação de redução de arquivo em execução no momento sem realizar nenhuma ação.

• BLOCKERS

  Encerre todas as transações de usuário que bloqueiam a operação de redução de arquivo para que a operação possa continuar. A opção BLOCKERS exige que o logon tenha a permissão ALTER ANY CONNECTION.

Conjuntos de resultados

A tabela a seguir descreve as colunas do conjunto de resultados.

Comentários

DBCC SHRINKFILE aplica-se aos arquivos do banco de dados atual. Para obter mais informações sobre como alterar o banco de dados atual, consulte USE (Transact-SQL).

É possível interromper operações DBCC SHRINKFILE a qualquer momento e qualquer trabalho concluído é preservado. Se você usar o parâmetro EMPTYFILE e cancelar a operação, o arquivo não será marcado para impedir que dados adicionais sejam adicionados.

Quando uma operação DBCC SHRINKFILE falha, um erro é emitido.

Outros usuários podem trabalhar no banco de dados durante a redução do arquivo. O banco de dados não precisa estar no modo de usuário único. Não é necessário executar a instância do SQL Server em modo de usuário único para reduzir os bancos de dados de sistema.

Quando especificado com WAIT_AT_LOW_PRIORITY, a solicitação de bloqueio Sch-M da operação de redução aguardará com baixa prioridade ao executar o comando por um minuto. Se a operação for bloqueada pelo tempo da duração, a ação ABORT_AFTER_WAIT especificada será executada.

Noções básicas sobre problemas de simultaneidade com DBCC SHRINKFILE

Os comandos de redução de banco de dados e de redução de arquivo podem causar problemas de simultaneidade, principalmente com a manutenção ativa, como a recompilação de índices ou em ambientes OLTP com trabalho intensivo. Quando o aplicativo executa consultas em tabelas de banco de dados, essas consultas adquirem e mantém um bloqueio Sch-S (estabilidade de esquema) até que as consultas concluam as operações. No momento, ao tentar recuperar espaço durante o uso regular, as operações de redução de banco de dados e de redução de arquivo exigem um bloqueio Sch-M (modificação de esquema) ao mover ou excluir páginas IAM, impedindo os bloqueios Sch-S necessários para consultas de usuário. Como resultado, consultas de longa execução bloquearão uma operação de redução até que as consultas sejam concluídas. Isso significa que as novas consultas que exigirem bloqueios Sch-S também serão enfileiradas após a operação de redução de espera e também serão bloqueadas, agravando ainda mais esse problema de simultaneidade. Isso pode afetar significativamente o desempenho da consulta do aplicativo e também causar dificuldades para concluir a manutenção necessária de redução dos arquivos de banco de dados. Introduzido no SQL Server 2022 (16.x), o recurso de espera reduzida em baixa prioridade de remoção resolve esse problema usando um modo WAIT_AT_LOW_PRIORITY de bloqueio de modificação de esquema. Para obter mais informações, confira WAIT_AT_LOW_PRIORITY com operações de redução.

Para obter mais informações sobre bloqueios Sch-S e Sch-M, confira o Guia de controle de versão de linha e de bloqueio de transações.

Reduzir um arquivo de log

Para arquivos de log, o Mecanismo de Banco de Dados usa target_size para calcular o tamanho de destino do log inteiro. Portanto, target_size é o espaço livre do log após a operação de redução. O tamanho de destino do log inteiro é então convertido para o tamanho designado para cada arquivo de log. DBCC SHRINKFILE tenta reduzir cada arquivo de log físico imediatamente para o tamanho de destino. No entanto, se parte do log lógico residente nos logs virtuais for maior que o tamanho designado, o Mecanismo de Banco de Dados liberará o espaço disponível possível e emitirá uma mensagem informativa. A mensagem descreve as ações necessárias para mover o log lógico dos logs virtuais no final do arquivo. Depois que as ações são executadas, DBCC SHRINKFILE pode ser usado para liberar o espaço restante.

Como um arquivo de log poderá ser reduzido somente a um limite de arquivo de log virtual, talvez não seja possível reduzir um arquivo de log para um tamanho menor que o tamanho de um arquivo de log virtual, mesmo que o arquivo não esteja sendo usado. O Mecanismo de Banco de Dados escolhe dinamicamente o tamanho do arquivo de log virtual quando os arquivos de log são criados ou estendidos.

Práticas recomendadas

Considere as seguintes informações ao planejar a redução de arquivos:

• Uma operação de redução é mais eficiente depois de uma operação que cria uma grande quantidade de espaço não utilizado, como operações que truncam ou excluem uma tabela.

• A maioria dos bancos de dados exige algum espaço livre disponível para operações comuns rotineiras. Se você reduzir um arquivo de banco de dados repetidamente e perceber que ele aumentou novamente, isso indica a necessidade de espaço livre para as operações regulares. Nesses casos, reduzir repetidamente um banco de dados é uma operação inútil. Os eventos de crescimento automático necessários para aumentar o arquivo de banco de dados prejudicam o desempenho.

• Uma operação de redução não preserva o estado de fragmentação de índices do banco de dados e, em geral, aumenta o nível de fragmentação. Essa fragmentação é outra razão para não reduzir o banco de dados repetidamente.

• Reduza vários arquivos no mesmo banco de dados sequencialmente, e não simultaneamente. A contenção em tabelas do sistema pode causar o bloqueio e gerar atrasos.

Solucionar problemas

Esta seção descreve como diagnosticar e corrigir problemas que podem ocorrer ao executar o comando DBCC SHRINKFILE.

O arquivo não é reduzido

Se o tamanho do arquivo não sofrer alterações após uma operação de redução sem erros, tente o seguinte para verificar se o arquivo tem espaço livre o suficiente:

• Execute a seguinte consulta.

SELECT name
    , size / 128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT) / 128.0 AS AvailableSpaceInMB
FROM sys.database_files;

• Execute o comando DBCC SQLPERF para retornar o espaço usado no log de transações.

A operação de redução não poderá reduzir mais o tamanho do arquivo se não houver espaço livre suficiente disponível.

Normalmente, o arquivo de log é que parece não ser reduzido. Em geral, a não redução é resultado de um arquivo de log que não foi truncado. Para truncar o log, defina o modelo de recuperação de banco de dados como SIMPLE ou faça backup do log e execute a operação DBCC SHRINKFILE novamente.

A operação de redução está bloqueada

Uma transação em execução em um nível de isolamento baseado em controle de versão de linha pode bloquear as operações de redução. Por exemplo, se uma grande operação de exclusão em execução em um nível de isolamento baseado em controle de versão de linha estiver em andamento durante a execução de uma operação DBCC SHRINKDATABASE, a operação de redução aguardará a conclusão da exclusão antes de continuar. Quando esse bloqueio acontece, as operações DBCC SHRINKFILE e DBCC SHRINKDATABASE imprimem uma mensagem informativa (5202 para SHRINKDATABASE e 5203 para SHRINKFILE) no log de erros do SQL Server. Essa mensagem é registrada a cada cinco minutos na primeira hora e, em seguida, a cada hora. Por exemplo, se o log de erros contiver a mensagem de erro a seguir, ocorrerá o seguinte erro:

DBCC SHRINKFILE for file ID 1 is waiting for the snapshot
transaction with timestamp 15 and other snapshot transactions linked to
timestamp 15 or with timestamps older than 109 to finish.

Essa mensagem significa que transações de instantâneo com carimbos de data/hora anteriores a 109, que é a última transação concluída pela operação de redução, estão bloqueando a operação de redução. Também indica que as colunas transaction_sequence_num ou first_snapshot_sequence_num na exibição de gerenciamento dinâmico sys.dm_tran_active_snapshot_database_transactions contêm um valor igual a 15. Se as colunas de exibição transaction_sequence_num ou first_snapshot_sequence_num contiverem um número menor que o da última transação concluída por uma operação de redução (109), a operação de redução esperará o término dessas transações.

Para solucionar o problema, você pode executar uma destas tarefas:

• Encerrar a transação que está bloqueando a operação de redução.
• Encerrar a operação de redução. Todo trabalho concluído será preservado se a operação for encerrada.
• Não interferir e permitir que a operação de redução aguarde até que a transação de bloqueio seja concluída.

Permissões

Exige associação à função de servidor fixa sysadmin ou à função de banco de dados fixa db_owner .

Exemplos

a. Reduzir um arquivo de dados para um tamanho de destino especificado

O exemplo a seguir reduz o tamanho de um arquivo de dados chamado DataFile1 do banco de dados de usuário UserDB para 7 MB.

USE UserDB;
GO
DBCC SHRINKFILE (DataFile1, 7);
GO

B. Reduzir um arquivo de log para um tamanho de destino especificado

O exemplo a seguir reduz o arquivo de log do banco de dados AdventureWorks2022 para 1 MB. Para permitir que o comando DBCC SHRINKFILE reduza o arquivo, primeiro o arquivo é truncado definindo o modelo de recuperação de banco de dados como SIMPLE.

USE AdventureWorks2022;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE AdventureWorks2022
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AdventureWorks2022_Log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE AdventureWorks2022
SET RECOVERY FULL;
GO

C. Truncar um arquivo de dados

O exemplo a seguir trunca o arquivo de dados primário no banco de dados AdventureWorks2022. A exibição do catálogo sys.database_files é consultada para obter o file_id do arquivo de dados.

USE AdventureWorks2022;
GO
SELECT file_id, name
FROM sys.database_files;
GO
DBCC SHRINKFILE (1, TRUNCATEONLY);

D. Esvaziar um arquivo

O exemplo a seguir demonstra como esvaziar um arquivo de forma que ele possa ser removido do banco de dados. Para os fins deste exemplo, é preciso, primeiro, criar um arquivo de dados que contenha dados.

USE AdventureWorks2022;
GO
-- Create a data file and assume it contains data.
ALTER DATABASE AdventureWorks2022
ADD FILE (
    NAME = Test1data,
    FILENAME = 'C:\t1data.ndf',
    SIZE = 5MB
    );
GO
-- Empty the data file.
DBCC SHRINKFILE (Test1data, EMPTYFILE);
GO
-- Remove the data file from the database.
ALTER DATABASE AdventureWorks2022
REMOVE FILE Test1data;
GO

E. Reduzir um arquivo de banco de dados com WAIT_AT_LOW_PRIORITY

O exemplo a seguir tenta reduzir o tamanho de um arquivo de dados chamado no banco de dados de usuário atual para 1 MB. A exibição do catálogo sys.database_files é consultada para obter o file_id do arquivo de dados. Neste exemplo, file_id 5. Se não for possível obter um bloqueio em um minuto, a operação de redução será anulada.

USE AdventureWorks2022;
GO

SELECT file_id, name
FROM sys.database_files;
GO

DBCC SHRINKFILE (5, 1) WITH WAIT_AT_LOW_PRIORITY (ABORT_AFTER_WAIT = SELF);

Confira também

• Considerações para as configurações de crescimento automático e redução automática no SQL Server
• Arquivos e grupos de arquivos do banco de dados
• sys.database_files (Transact-SQL)
• sys.databases (Transact-SQL)
• FILE_ID (Transact-SQL)

Próximas etapas

• ALTER DATABASE (Transact-SQL)
• DBCC SHRINKDATABASE (Transact-SQL)
• Reduzir um banco de dados
• Reduzir um arquivo