DBCC SHRINKDATABASE (Transact-SQL)

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada de SQL do AzureAzure Synapse Analytics

Reduz o tamanho dos arquivos de dados e de log do banco de dados especificado.

Convenções de sintaxe de Transact-SQL

Sintaxe

Sintaxe para SQL Server:

DBCC SHRINKDATABASE   
( database_name | database_id | 0   
     [ , target_percent ]   
     [ , { 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 }

Sintaxe para Azure Synapse Analytics:

DBCC SHRINKDATABASE
( database_name
     [ , target_percent ]
)
[ WITH NO_INFOMSGS ]

Argumentos

database_name | database_id | 0

O nome ou a ID do banco de dados a ser reduzido. Se 0 for especificado, o banco de dados atual será usado.

target_percent

A porcentagem de espaço livre que você deseja manter no arquivo de banco de dados após a redução do banco de dados.

NOTRUNCATE

Move as páginas atribuídas do final do arquivo para páginas não atribuídas no início do arquivo. Essa ação compacta os dados dentro do arquivo. target_percent é opcional. O Azure Synapse Analytics não dá suporte a essa opção.

O espaço livre no final do arquivo não é retornado ao sistema operacional, e o tamanho físico do arquivo não é alterado. Devido a isso, o banco de dados não parecerá reduzido quando você especificar NOTRUNCATE.

NOTRUNCATE é aplicável somente a arquivos de dados. NOTRUNCATE não afeta o arquivo de log.

TRUNCATEONLY

Libera todo o espaço livre no final do arquivo para o sistema operacional. Não move todas as páginas dentro do arquivo. O arquivo de dados é reduzido somente para a última extensão atribuída. Ignora target_percent quando especificado com TRUNCATEONLY. O Azure Synapse Analytics não dá suporte a essa opção.

DBCC SHRINKDATABASE com a opção TRUNCATEONLY afeta apenas o arquivo de log de transações do banco de dados. Para truncar o arquivo de dados, use DBCC SHRINKFILE nesse caso. Para obter mais informações, confira DBCC SHRINKFILE.

WITH NO_INFOMSGS

Suprime todas as mensagens informativas com níveis de severidade de 0 a 10.

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

Quando um comando de redução é executado no modo WAIT_AT_LOW_PRIORITY, novas consultas que exigem bloqueios de Sch-S (estabilidade de esquema) não são bloqueadas pela operação de redução de espera até que a operação de redução saia da espera e comece a ser executada. 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 execução prolongada, 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 execução prolongada, a operação de redução acabará expirando 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. Aqui, é possível 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 ]

• SELF

  SELF é a opção padrão. Saia da operação de redução de banco de dados 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 banco de dados para que a operação possa continuar. A opção BLOCKERS requer 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

Para reduzir todos os arquivos de log e dados de um banco de dados específico, execute o comando DBCC SHRINKDATABASE. Para reduzir um arquivo de dados ou de log de cada vez para um banco de dados específico, execute o comando DBCC SHRINKFILE.

Para exibir a quantidade atual de espaço livre (não alocado) no banco de dados, execute sp_spaceused.

Operações DBCC SHRINKDATABASE podem ser interrompidas a qualquer momento do processo, e todo o trabalho concluído é preservado.

O banco de dados não pode ser menor que o tamanho mínimo configurado do banco de dados. Você especifica o tamanho mínimo quando o banco de dados é originalmente criado. Ou então, o tamanho mínimo pode ser o último tamanho explicitamente definido por meio de uma operação de alteração de tamanho do arquivo. Operações como DBCC SHRINKFILE ou ALTER DATABASE são exemplos de operações de alteração de tamanho de arquivo.

Considere que um banco de dados foi criado originalmente com o tamanho de 10 MB. Em seguida, ele atinge 100 MB. O tamanho mínimo ao qual o banco de dados pode ser reduzido é 10 MB, mesmo se todos os dados no banco de dados são excluídos.

Especifique a opção NOTRUNCATE ou a opção TRUNCATEONLY ao executar DBCC SHRINKDATABASE. Se você não fizer isso, o resultado será igual ao fornecido ao executar uma operação DBCC SHRINKDATABASE com NOTRUNCATE seguida pela execução de uma operação DBCC SHRINKDATABASE com TRUNCATEONLY.

O banco de dados reduzido não precisa estar no modo do usuário único. Outros usuários podem trabalhar no banco de dados quando ele é reduzido, incluindo bancos de dados do sistema.

Não é possível reduzir um banco de dados enquanto ele estiver sendo armazenado em backup. Da mesma forma, não é possível fazer backup de um banco de dados enquanto houver uma operação de redução em processamento.

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.

Como DBCC SHRINKDATABASE funciona

DBCC SHRINKDATABASE reduz os arquivos de dados individualmente, mas faz isso como se todos eles existissem em uma série de logs contíguos. Os arquivos são sempre reduzidos do final.

Suponha que você tem alguns arquivos de log, um arquivo de dados e um banco de dados denominado mydb. Os arquivos de dados e de log têm 10 MB cada e o arquivo de dados contém 6 MB de dados. Para cada arquivo, o Mecanismo de Banco de Dados calcula um tamanho de destino. O valor é o tamanho a que se pretende chegar após a redução do arquivo. Quando DBCC SHRINKDATABASE é especificado com target_percent, o Mecanismo de Banco de Dados calcula o tamanho do destino como a quantidade de target_percent de espaço livre no arquivo após a redução.

Por exemplo, se você especificar um target_percent igual a 25 para a redução de mydb, o Mecanismo de Banco de Dados calculará o tamanho de destino para o arquivo de dados como 8 MB (6 MB de dados mais 2 MB de espaço livre). Portanto, o Mecanismo de Banco de Dados moverá todos os dados dos últimos 2 MB do arquivo de dados para qualquer espaço livre nos primeiros 8 MB do arquivo de dados e, em seguida, reduzirá o arquivo.

Considere que o arquivo de dados de mydb contém 7 MB de dados. Especificar um target_percent igual a 30 permite que esse arquivo de dados seja reduzido para um percentual livre igual a 30. No entanto, especificar um target_percent igual a 40 não reduz o arquivo de dados porque não é possível criar espaço livre suficiente no tamanho total atual do arquivo de dados.

Você também pode pensar nessa questão de outra forma: um arquivo de dados com 40 por cento de espaço livre desejado + 70 por cento de espaço de dados completo (7 MB de 10 MB) é igual a mais de 100 por cento. Qualquer target_percentage maior que 30 não reduzirá o arquivo de dados. Ele não será reduzido porque o percentual de espaço que você deseja mais o percentual atual ocupado pelo arquivo de dados soma mais de 100%.

Para arquivos de log, o Mecanismo de Banco de Dados usa target_percent para calcular o tamanho de destino do log inteiro. É por isso que target_percent é a quantidade de espaço livre no log após a operação de redução. O tamanho designado do log inteiro é convertido no tamanho designado de cada arquivo de log.

DBCC SHRINKDATABASE tenta reduzir cada arquivo de log físico imediatamente para o tamanho de destino. Digamos que nenhuma parte do log lógico permanece nos logs virtuais além do tamanho de destino do arquivo de log. Em seguida, o arquivo será truncado com êxito e DBCC SHRINKDATABASE será concluído sem nenhuma mensagem. No entanto, se parte do log lógico ficar nos logs virtuais além do 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 SHRINKDATABASE pode ser usado para liberar o espaço restante.

Um arquivo de log só pode ser reduzido para um limite de arquivo de log virtual. Esse é o motivo pelo qual reduzir um arquivo de log para um tamanho menor que o tamanho de um arquivo de log virtual pode não ser possível. Isso pode não ser possível mesmo que ele não esteja sendo usado. O tamanho do arquivo de log virtual é definido dinamicamente pelo Mecanismo de Banco de Dados quando os arquivos de log são criados ou estendidos.

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

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 WLP (redução em baixa prioridade) de espera resolve esse problema usando um bloqueio de modificação de esquema no modo WAIT_AT_LOW_PRIORITY. 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.

Práticas recomendadas

Considere as seguintes informações ao planejar reduzir um banco de dados:

• Uma operação de redução é mais eficiente depois de uma operação que cria espaço não utilizado, como operações truncate table ou drop table.
• 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. Esse resultado é outra razão para não reduzir o banco de dados repetidamente.
• A menos que você tenha um requisito específico, não defina a opção de banco de dados AUTO_SHRINK como ON.

Solucionar problemas

É possível bloquear operações de redução por uma transação que está esteja executada em um nível de isolamento baseado em controle de versão de linha. Por exemplo, uma grande operação de exclusão é executada em um nível de isolamento baseado em controle de versão de linha quando uma operação DBCC SHRINKDATABASE é executada. Quando essa situação ocorrer, a operação de redução aguardará a operação de exclusão ser concluída para então reduzir os arquivos. Quando a operação de redução aguarda e as operações DBCC SHRINKFILE e DBCC SHRINKDATABASE imprimem uma mensagem informativa (5202 para SHRINKDATABASE e 5203 para SHRINKFILE). Essa mensagem é registrada no log de erros do SQL Server a cada cinco minutos na primeira hora e, posteriormente, a cada hora. Por exemplo, se o log de erros contiver a seguinte mensagem de erro:

DBCC SHRINKDATABASE for database ID 9 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.

Esse erro significa que as transações de instantâneo que têm carimbos de data/hora anteriores a 109 bloquearão a operação de redução. Essa transação é a última transação concluída pela operação de redução. Ele também indica que as colunas transaction_sequence_num ou first_snapshot_sequence_num na exibição de gerenciamento dinâmica sys.dm_tran_active_snapshot_database_transactions (Transact-SQL) contêm um valor igual a 15. A coluna transaction_sequence_num ou first_snapshot_sequence_num da exibição pode conter um número menor que o da última transação concluída por uma operação de redução (109). Nesse caso, a operação de redução esperará o término dessas transações.

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

• Encerrar a transação que está bloqueando a operação de redução.
• Encerrar a operação de redução. Todo o trabalho concluído será preservado.
• 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 banco de dados e especificar um percentual de espaço livre

O exemplo a seguir reduz o tamanho dos arquivos de dados e de log no banco de dados de usuário UserDB para permitir 10 por cento de espaço livre no banco de dados.

DBCC SHRINKDATABASE (UserDB, 10);
GO

B. Truncar um banco de dados

O exemplo a seguir reduz os arquivos de dados e de log no banco de dados de exemplo AdventureWorks2022 até a última extensão atribuída.

DBCC SHRINKDATABASE (AdventureWorks2022, TRUNCATEONLY);

C. Reduzir um banco de dados do Azure Synapse Analytics

DBCC SHRINKDATABASE (database_A);
DBCC SHRINKDATABASE (database_B, 10);

D. Reduzir um arquivo de banco de dados com WAIT_AT_LOW_PRIORITY

O exemplo a seguir tenta reduzir o tamanho dos arquivos de dados e de log no banco de dados AdventureWorks2022 para liberar 20% de espaço livre no banco de dados. Se não for possível obter um bloqueio em um minuto, a operação de redução será anulada.

DBCC SHRINKDATABASE ([AdventureWorks2022], 20) 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.databases (Transact-SQL)
• sys.database_files (Transact-SQL)

Próximas etapas

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