DBCC SHRINKFILE (Transact-SQL)

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

Reduz os dados especificados do banco de dados atual ou o tamanho do arquivo de log. Você pode usá-lo para mover dados de um arquivo para outros arquivos no mesmo grupo de arquivos, o que esvazia o arquivo e permite sua remoção do banco de dados. Você pode reduzir um arquivo para menos do que seu tamanho na criação, redefinindo o tamanho mínimo do arquivo para o novo valor. Use DBCC SHRINKFILE somente quando necessário.

Observação

As operações de encolhimento não devem ser consideradas um procedimento 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.

Transact-SQL convenções de sintaxe

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 ficheiro a ser reduzido.

file_id

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

target_size

Um inteiro que representa o novo tamanho de megabyte do arquivo. Se não for especificado ou for 0, DBCC SHRINKFILE reduz-se ao tamanho de criação do ficheiro.

Você pode reduzir o tamanho padrão de um arquivo vazio usando DBCC SHRINKFILE <target_size>. Por exemplo, se você criar um arquivo de 5 MB e, em seguida, reduzir o arquivo para 3 MB enquanto o arquivo ainda estiver vazio, o tamanho padrão do arquivo será definido como 3 MB. Isso se aplica apenas a arquivos vazios que nunca contiveram dados.

Esta opção não é suportada para contêineres de grupo de arquivos FILESTREAM.

Se especificado, DBCC SHRINKFILE tenta reduzir o arquivo para target_size. As páginas usadas na área do arquivo a serem liberadas são movidas para o espaço livre nas áreas mantidas do arquivo. Por exemplo, com um arquivo de dados de 10 MB, uma operação de DBCC SHRINKFILE com um 8target_size move todas as páginas usadas nos últimos 2 MB do arquivo para quaisquer páginas não alocadas nos primeiros 8 MB. DBCC SHRINKFILE não reduz um arquivo além do tamanho de dados armazenados necessário. Por exemplo, se 7 MB de um arquivo de dados de 10 MB for usado, uma instrução DBCC SHRINKFILE com uma target_size de 6 reduz o arquivo para apenas 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 arquivos no mesmo grupo de arquivos. EMPTYFILE garante que nenhum novo dado seja adicionado ao arquivo, apesar de esse arquivo 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 os dados poderão ser adicionados.

Para contêineres de grupo de arquivos FILESTREAM, você não pode 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, consulte sp_filestream_force_garbage_collection. Para obter informações sobre como remover um contêiner FILESTREAM, consulte a seção correspondente em ALTER DATABASE File and Filegroup Options (Transact-SQL)

EMPTYFILE não é suportado na Base de Dados SQL do Azure ou na Hiperescala da Base de Dados SQL do Azure.

NOTRUNCATE

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

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

Esta opção não é suportada para 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 nenhum movimento de página dentro do arquivo. O arquivo de dados é reduzido apenas até a última extensão alocada.

target_size é ignorado se especificado com TRUNCATEONLY.

A opção TRUNCATEONLY não move informações no log, mas remove VLFs inativos do final do arquivo de log. Esta opção não é suportada para contêineres de grupo de arquivos FILESTREAM.

SEM 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 do SQL do Azure

O recurso de espera em prioridade baixa reduz a contenção de bloqueio. Para obter mais informações, consulte Compreensão dos problemas de simultaneidade com o DBCC SHRINKDATABASE.

Este recurso é semelhante ao WAIT_AT_LOW_PRIORITY com operações de índice on-line, com algumas diferenças.

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

AGUARDAR_COM_BAIXA_PRIORIDADE

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

Quando um comando shrink é executado no modo WAIT_AT_LOW_PRIORITY, novas consultas que exigem estabilidade de esquema (Sch-S) não são bloqueadas pela operação shrink em espera até que a operação shrink pare de esperar e comece a ser executada. A operação de redução é executada quando é capaz de obter um bloqueio para modificar o esquema (Sch-M). 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á expirando 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á por atingir 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 de Sch-M devido a uma consulta ou consultas simultâneas que mantêm bloqueios Sch-S. Quando ocorre um tempo limite, o erro 49516 é enviado para o 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. Repita a operação de redução no modo WAIT_AT_LOW_PRIORITY.

ABORT_AFTER_WAIT = [ PRÓPRIO | BLOQUEADORES ]

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

• EU MESMO

  Saia da operação de redução de arquivos que está sendo executada no momento sem executar nenhuma ação.

• BLOQUEADORES

  Mate todas as transações do usuário que bloqueiam a operação de reduzir o arquivo para que a operação possa continuar. A opção BLOCKERS requer que o login tenha a permissão ALTER ANY CONNECTION.

Conjunto de resultados

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

Nome da coluna	Descrição
DbId	Número de identificação do banco de dados do arquivo que o Mecanismo de Banco de Dados tentou reduzir.
FileId	O número de identificação do arquivo que o Mecanismo de Banco de Dados tentou reduzir.
CurrentSize	Número de páginas de 8 KB que o arquivo ocupa atualmente.
MinimumSize	Número de páginas de 8 KB que o arquivo poderia ocupar, no mínimo. Esse número corresponde ao tamanho mínimo ou ao tamanho originalmente criado de um arquivo.
UsedPages	Número de páginas de 8 KB atualmente usadas pelo arquivo.
EstimatedPages	Número de páginas de 8 KB para as quais o Mecanismo de Banco de Dados estima que o arquivo pode ser reduzido.

Comentários

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

Você pode parar as operações DBCC SHRINKFILE a qualquer momento, e qualquer trabalho concluído será 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 é gerado.

Outros usuários podem trabalhar no banco de dados durante a redução de arquivos; 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 no modo de usuário único para reduzir os bancos de dados do sistema.

Quando especificado com WAIT_AT_LOW_PRIORITY, a solicitação de bloqueio de Sch-M da operação de redução aguardará com baixa prioridade ao executar o comando por 1 minuto. Se a operação for bloqueada durante o período, a ação especificada ABORT_AFTER_WAIT será executada.

Problemas conhecidos

Aplica-se a: SQL Server, Azure SQL Database (Banco de Dados SQL do Azure), Instância Gerenciada SQL do Azure, pool SQL dedicado do Azure Synapse Analytics

• Atualmente, os tipos de coluna LOB (varbinary(max), varchar(max)e nvarchar(max)) em segmentos columnstore compactados não são afetados por DBCC SHRINKDATABASE e DBCC SHRINKFILE.

Compreender os problemas de concorrência com DBCC SHRINKFILE

Os comandos shrink database e shrink file podem levar a problemas de simultaneidade, especialmente com manutenção ativa, como a reconstrução de índices, ou em ambientes OLTP ocupados. Quando seu aplicativo executa consultas em tabelas de banco de dados, essas consultas adquirirão e manterão um bloqueio de estabilidade de esquema (Sch-S) até que as consultas concluam suas operações. Ao tentar recuperar espaço durante o uso regular, as operações de redução de banco de dados e de ficheiros atualmente exigem um bloqueio de modificação de esquema (Sch-M) ao deslocar ou eliminar páginas do Mapa de Alocação de Índice (IAM), impedindo os bloqueios de Sch-S necessários para as consultas dos utilizadores. 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 quaisquer novas consultas que exijam bloqueios de Sch-S também são enfileiradas atrás da operação de redução em 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 para reduzir os arquivos do banco de dados. Introduzido no SQL Server 2022 (16.x), a funcionalidade de redução em modo de espera com baixa prioridade soluciona este problema ao usar um bloqueio de modificação de esquema no modo WAIT_AT_LOW_PRIORITY. Para obter mais informações, consulte WAIT_AT_LOW_PRIORITY com operações de redução.

Para obter mais informações sobre os bloqueios Sch-S e Sch-M, consulte o guia de bloqueio de transações e controlo de versão de linha.

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 de todo o log é então convertido para o tamanho de destino de cada arquivo de log. DBCC SHRINKFILE tenta reduzir cada arquivo de log físico para seu tamanho de destino imediatamente. No entanto, se parte do log lógico residir nos logs virtuais que estão além do tamanho de destino, o Mecanismo de Banco de Dados liberará o máximo de espaço possível e, em seguida, emitirá uma mensagem informativa. A mensagem descreve quais ações são necessárias para mover o log lógico para fora dos logs virtuais no final do arquivo. Depois que as ações são executadas, DBCC SHRINKFILE podem ser usadas para liberar o espaço restante.

Como um arquivo de log só pode ser reduzido para um limite de arquivo de log virtual, reduzir um arquivo de log para um tamanho menor do que o tamanho de um arquivo de log virtual pode não ser possível, mesmo que ele não esteja sendo usado. O Motor de Base de Dados escolhe dinamicamente o tamanho do ficheiro de log virtual quando os ficheiros de log são criados ou estendidos.

Melhores práticas

Considere as seguintes informações ao planejar reduzir um arquivo:

• Uma operação de redução de espaço é mais eficaz após uma operação que cria uma grande quantidade de espaço não utilizado, como uma operação de truncamento de tabela ou uma operação de eliminação de tabela.

• A maioria dos bancos de dados requer algum espaço livre para estar disponível para operações regulares do dia-a-dia. Se você reduzir um arquivo de banco de dados repetidamente e notar que o tamanho do banco de dados cresce novamente, isso indica que o espaço livre é necessário para operações regulares. Nesses casos, reduzir repetidamente o arquivo de banco de dados é uma operação desperdiçada. 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 dos índices no banco de dados e, geralmente, aumenta a fragmentação em um grau. Essa fragmentação é outra razão para não reduzir repetidamente o banco de dados.

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

Solução de problemas

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

O arquivo não encolhe

Se o tamanho do arquivo não mudar após uma operação de redução sem erros, tente o seguinte para verificar se o arquivo tem espaço livre adequado:

• 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 pode reduzir ainda mais o tamanho do arquivo se não houver espaço livre disponível.

Geralmente, é o ficheiro de log que parece não encolher, resultado de não ter sido truncado por um backup regular do log de transações. Para truncar o log, faça backup do log de transações e execute a operação DBCC SHRINKFILE novamente. Se a recuperação point-in-time não for necessária, considere o modelo de recuperação de banco de dados SIMPLE .

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

Uma transação executada sob um nível de isolamento baseado em controle de versão de linha pode bloquear operações de redução. Por exemplo, se uma grande operação de eliminação, em execução sob um isolamento baseado em versionamento de linha, estiver em andamento quando uma operação DBCC SHRINKDATABASE for executada, a operação de redução aguardará que a eliminação seja concluída 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. Esta mensagem é registada a cada cinco minutos na primeira hora e, em seguida, a cada hora. Por exemplo:

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.

Esta mensagem significa que as transações instantâneos com marcas temporais anteriores a 109 (a última transação concluída pela operação de encolhimento) estão bloqueando a operação de encolhimento. Também indica que as colunas transaction_sequence_numou first_snapshot_sequence_num na vista de gestão dinâmica sys.dm_tran_active_snapshot_database_transactions contêm um valor de 15. Se a coluna de exibição transaction_sequence_num ou first_snapshot_sequence_num contiver um número menor do que a última transação concluída de uma operação de redução (109), a operação de redução aguardará a conclusão dessas transações.

Para resolver o problema, você pode executar uma das seguintes tarefas:

• Encerre a transação que está bloqueando a operação de redução.
• Conclua a operação de redução. Qualquer trabalho concluído será mantido se a operação de redução terminar.
• Não faça nada e permita que a operação de redução aguarde até que a transação de bloqueio seja concluída.

Permissões

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

Exemplos

Um. 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 no 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 no banco de dados AdventureWorks2022 para 1 MB. Para permitir que o comando DBCC SHRINKFILE reduza o arquivo, o arquivo é primeiro 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 ficheiro de dados

O exemplo a seguir trunca o arquivo de dados primário no banco de dados AdventureWorks2022. A visualização do catálogo sys.database_files é consultada para obter a 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 ficheiro

O exemplo a seguir demonstra o esvaziamento de um arquivo para que ele possa ser removido do banco de dados. Para as finalidades deste exemplo, um arquivo de dados é criado primeiro e contém 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 no banco de dados de usuário atual para 1 MB. A visualização do catálogo sys.database_files é consultada para obter a file_id do ficheiro de dados, neste exemplo, file_id 5. Se um bloqueio não puder ser obtido dentro de um minuto, a operação de encolhimento será abortada.

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);

Conteúdo relacionado

• Reduzir um banco de dados
• Reduzir um arquivo
• DBCC SHRINKDATABASE (Transact-SQL)
• Considerações para as configurações de crescimento automático e redução automática no SQL Server
• arquivos de banco de dados e grupos de arquivos
• sys.database_files (Transact-SQL)
• sys.databases (Transact-SQL)
• FILE_ID (Transact-SQL)
• BASE DE DADOS ALTER (Transact-SQL)