DBCC SHRINKDATABASE (Transact-SQL)SQL)

• 07/17/2017
• 8 minutos para ler
• • p
  • M
  • j
  • i
  • K
  • +12

Candidata-se a: ServidorSQL (todas as versões suportadas) Base de dados SQL Azure Azure Synapse Analytics

Diminui o tamanho dos dados e arquivos de log na base de dados especificada.

Convenções de sintaxe Transact-SQL

Sintaxe

DBCC SHRINKDATABASE ( database_name | database_id | 0 ) 

-- Azure Synapse AnalyticsDBCC SHRINKDATABASE ( database_name ) 

Argumentos

nome_da_base de dados | database_id | 0
É o nome ou ID da base de dados a ser encolhido. 0 especifica que a base de dados actual é utilizada.

target_percent
É a percentagem de espaço livre que se pretende deixar no ficheiro da base de dados após a base ter sido encolhida.

NOTRUNCATE
Moves assigned pages from the file’s end to unassigned pages in the front of the file. Esta ação compacta os dados dentro do arquivo. target_percent é opcional. Azure Synapse Analytics não suporta esta opção.

O espaço livre no final do arquivo não é devolvido ao sistema operacional, e o tamanho físico do arquivo não muda. Como tal, o banco de dados parece não encolher quando você especifica NOTRUNCATE.

NOTRUNCATE é aplicável apenas a arquivos de dados. NOTRUNCATE não afecta o ficheiro de registo.

TRUNCATEONLY
Release todo o espaço livre no fim do ficheiro para o sistema operativo. Não move nenhuma página dentro do arquivo. O arquivo de dados encolhe apenas até a última extensão atribuída. Ignora o target_percent se especificado com TRUNCATEONLY. Azure Synapse Analytics não suporta esta opção.

TRUNCATEONLY afeta o arquivo de log. Para truncar apenas o arquivo de dados, use DBCC SHRINKFILE.

WITH NO_INFOMSGS
Suprime todas as mensagens informativas que tenham níveis de severidade de 0 a 10.

Resultados

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

Nome da coluna	Descrição
DbId	Número de identificação da base de dados do arquivo que o Motor da Base de Dados tentou encolher.
FileId	Número de identificação do arquivo que o Database Engine tentou encolher.
Tamanho atual	Número de páginas de 8-KB que o arquivo atualmente ocupa.
Tamanho mínimo	Número de páginas de 8-KB que o arquivo poderia ocupar, no mínimo. Este valor corresponde ao tamanho mínimo ou tamanho originalmente criado de um arquivo.
Páginas usadas	Número de páginas 8-KB atualmente usadas pelo arquivo.
Páginas estimadas	Número de páginas 8-KB que o Mecanismo de Banco de Dados estima que o arquivo poderia ser reduzido.

Comentários

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

Para ver a quantidade atual de espaço livre (não alocado) na base de dados, execute sp_spaceused.

DBCC SHRINKDATABASE as operações podem ser interrompidas em qualquer ponto do processo, e qualquer trabalho concluído é mantido.

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

Vamos dizer que uma base de dados é originalmente criada com um tamanho de 10 MB de tamanho. Então, ele cresce para 100 MB. O menor banco de dados pode ser reduzido para 10 MB, mesmo que todos os dados do banco tenham sido excluídos.

Especifique a opção NOTRUNCATE ou a opção TRUNCATEONLY quando você executar DBCC SHRINKDATABASE. Se não o fizer, o resultado é o mesmo que se executar uma operação DBCC SHRINKDATABASE com NOTRUNCATE seguida de executar uma operação DBCC SHRINKDATABASE com TRUNCATEONLY.

O banco de dados encolhido não precisa estar no modo de usuário único. Outros usuários podem estar trabalhando na base de dados quando ela está encolhida, incluindo bases de dados do sistema.

Você não pode encolher uma base de dados enquanto a base de dados estiver sendo copiada. Inversamente, você não pode fazer o backup de uma base de dados enquanto uma operação de encolhimento da base de dados estiver em processo.

Como funciona o DBCC SHRINKDATABASE

DBCC SHRINKDATABASE encolhe arquivos de dados por arquivo, mas encolhe arquivos de log como se todos os arquivos de log existissem em um pool contíguo de log. Os arquivos são sempre encolhidos do final.

Casar que você tem um par de arquivos de log, um arquivo de dados, e uma base de dados chamada mydb. Os arquivos de dados e de log são 10 MB cada e o arquivo de dados contém 6 MB de dados. O Motor de Base de Dados calcula um tamanho alvo para cada ficheiro. Este valor é o tamanho para o qual o ficheiro deve ser encolhido. Quando o DBCC SHRINKDATABASE é especificado com target_percent, o Database Engine calcula o tamanho do target a ser o target_percent de espaço livre no ficheiro depois de encolher.

Por exemplo, se você especificar um target_percent de 25 para mydb encolhido, o Database Engine calcula o tamanho do target para o ficheiro de dados a ser 8 MB (6 MB de dados mais 2 MB de espaço livre). Como tal, o Database Engine move quaisquer dados dos últimos 2 MB do ficheiro de dados para qualquer espaço livre nos primeiros 8 MB do ficheiro de dados e depois encolhe o ficheiro.

Conteça que o ficheiro de dados do mydb contém 7 MB de dados. Especificar um target_percent de 30 permite que este ficheiro de dados seja encolhido para a percentagem livre de 30. No entanto, especificar um target_percent de 40 não encolhe o ficheiro de dados porque o Motor de Base de Dados não encolhe um ficheiro para um tamanho menor do que os dados actualmente ocupados.

Pode também pensar nesta questão de outra forma: 40% de espaço livre desejado + 70% de arquivo de dados completo (7 MB em cada 10 MB) é mais de 100%. Qualquer tamanho_alvo superior a 30 não vai encolher o arquivo de dados. Não vai encolher porque a percentagem livre que você quer mais a percentagem actual que o ficheiro de dados ocupa é superior a 100%.

Para ficheiros de log, o Motor de Base de Dados usa o target_percent para calcular o tamanho do target para todo o log. É por isso que o target_percent é a quantidade de espaço livre no log após a operação de encolhimento. O tamanho alvo para todo o log é então traduzido para um tamanho alvo para cada ficheiro de log.

DBCC SHRINKDATABASE tenta encolher imediatamente cada ficheiro de log físico para o seu tamanho alvo. Digamos que nenhuma parte do log lógico fica nos logs virtuais além do tamanho de destino do arquivo de log. Então o arquivo é truncado com sucesso e o DBCC SHRINKDATABASE termina sem nenhuma mensagem. No entanto, se parte do log lógico fica nos logs virtuais além do tamanho do alvo, o mecanismo da base de dados liberta o máximo de espaço possível, e então emite uma mensagem informativa. A mensagem descreve que ações são necessárias para mover o log lógico para fora dos logs virtuais no final do arquivo. Após as ações serem executadas, o DBCC SHRINKDATABASE pode ser usado para liberar o espaço restante.

Um arquivo de log só pode ser encolhido para um limite de arquivo de log virtual. É por isso que encolher um arquivo de log para um tamanho menor do que o tamanho de um arquivo de log virtual pode não ser possível. Pode não ser possível mesmo que não esteja a ser utilizado. O tamanho do arquivo de log virtual é escolhido dinamicamente pelo Mecanismo de Banco de Dados quando arquivos de log são criados ou estendidos.

Best Practices

Considere as seguintes informações quando você planeja encolher um banco de dados:

• Uma operação de encolhimento é mais eficaz após uma operação que cria espaço não utilizado, como uma tabela truncada ou uma operação de tabela suspensa.
• A maioria dos bancos de dados requer algum espaço livre para estar disponível para operações diárias regulares. Você pode encolher um banco de dados repetidamente e notar que o tamanho do banco de dados cresce novamente. Este crescimento indica que o espaço encolhido é necessário para as operações regulares. Nestes casos, encolher repetidamente a base de dados é uma operação desperdiçada.
• Uma operação de encolhimento não preserva o estado de fragmentação dos índices na base de dados, e geralmente aumenta a fragmentação até um grau. Este resultado é outra razão para não encolher repetidamente o banco de dados.
• A menos que você tenha um requisito específico, não defina a opção AUTO_SHRINK banco de dados para ON.

Problemas de solução

É possível bloquear operações de encolhimento por uma transação que está sendo executada sob um nível de isolamento baseado em versões de linha. Por exemplo, uma operação de eliminação grande executada sob um nível de isolamento baseado em versão de linha está em andamento quando uma operação DBCC SHRINK DATABASE é executada. Quando esta situação acontece, a operação de retração espera que a operação de eliminação seja concluída antes de retrair os arquivos. Quando a operação de contração espera, as operações DBCC SHRINKFILE e DBCC SHRINKDATABASE imprimem uma mensagem informativa (5202 para SHRINKDATABASE e 5203 para SHRINKFILE). Esta mensagem imprime para o log de erros do SQL Server a cada cinco minutos na primeira hora e depois a cada hora seguinte. 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. 

Este erro significa que as transações de snapshot que têm timestamps mais antigos que 109 irão bloquear a operação de encolhimento. Essa transação é a última transação que a operação de encolhimento foi concluída. Ela também indica a transaction_sequence_num ou first_snapshot_sequence_num nas colunas sys.dm_tran_active_snapshot_database_transactions (Transact-SQL), que contém um valor 15. A coluna transaction_sequence_num ou first_snapshot_sequence_num na visão pode conter um número que é menor do que a última transação completada por uma operação de encolhimento (109). Se assim for, a operação de encolhimento esperará que essas transações terminem.

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

• End a transação que está bloqueando a operação de encolhimento.
• End a operação de encolhimento. Qualquer trabalho concluído é mantido.
• Não fazer nada e permitir que a operação de encolhimento espere até que a transação de bloqueio seja concluída.

Permissões

Requer a adesão à função servidor fixo sysadmin ou à função banco de dados fixo db_owner.

Exemplos

A. Encurtando uma base de dados e especificando uma percentagem de espaço livre

O exemplo seguinte reduz o tamanho dos dados e ficheiros de log na base de dados de utilizadores UserDB para permitir 10 por cento de espaço livre na base de dados.

DBCC SHRINKDATABASE (UserDB, 10); GO 

B. Truncating a database

O exemplo a seguir reduz o tamanho dos dados e arquivos de log na AdventureWorks base de dados de amostra até a última extensão atribuída.

DBCC SHRINKDATABASE (AdventureWorks2012, TRUNCATEONLY); 

C. Encolhendo um banco de dados Azure Synapse Analytics

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

Veja também

BASE DE DADOS ALTERAIS (Transact-SQL)
DBCC (Transact-SQL)
DBCC SHRINKFILE (Transact-SQL)
Encolher um banco de dados