Reduzir o banco de dados tempdb

Aplica-se a:SQL ServerAzure SQL Managed Instance

Este artigo discute vários métodos que você pode usar para reduzir o banco de dados tempdb no SQL Server.

Você pode usar qualquer um dos seguintes métodos para alterar o tamanho do tempdb. As três primeiras opções são descritas neste artigo. Se você quiser usar o SQL Server Management Studio (SSMS), siga as instruções em Reduzir um banco de dados.

Comentários

Por padrão, o banco de dados tempdb é configurado para crescimento automático conforme necessário. Portanto, esse banco de dados pode crescer inesperadamente com o tempo para um tamanho maior do que o tamanho desejado. Tamanhos maiores de banco de dados tempdb não afetam negativamente o desempenho do SQL Server.

Quando o SQL Server é iniciado, tempdb é recriado usando uma cópia do banco de dados model e tempdb é redefinido para seu último tamanho configurado. O tamanho configurado é o último tamanho explícito que foi definido usando uma operação de alteração de tamanho de arquivo, como ALTER DATABASE que usa a opção MODIFY FILE ou as instruções DBCC SHRINKFILE ou DBCC SHRINKDATABASE. Portanto, a menos que você tenha que usar valores diferentes ou obter resolução imediata para um banco de dados de tempdb grande, você pode aguardar a próxima reinicialização do serviço SQL Server para que o tamanho diminua.

Você pode reduzir tempdb enquanto a atividade de tempdb está em andamento. No entanto, poderá encontrar outros erros, como bloqueios, deadlocks e assim por diante, que podem impedir a conclusão do processo de diminuição. Portanto, para garantir que uma redução de tempdb seja bem-sucedida, recomendamos que se faça isso enquanto o servidor estiver no modo de utilizador único, ou quando parar toda a atividade tempdb.

O SQL Server registra apenas informações suficientes no log de transações do tempdb para reverter uma transação, mas não para refazer transações durante a recuperação do banco de dados. Este recurso aumenta o desempenho nas instruções de INSERT em tempdb. Além disso, você não precisa registrar informações para refazer nenhuma transação porque tempdb é recriada toda vez que você reinicia o SQL Server. Portanto, ele não tem transações para avançar ou para reverter.

Para obter mais informações sobre como gerir e monitorizar tempdb, consulte Capacity planning e Monitorizar o uso de tempdb.

Use o comando ALTER DATABASE

Esse método requer que você reinicie o SQL Server.

1. Pare o SQL Server.

2. Em um prompt de comando, inicie a instância no modo de configuração mínima. Para fazer isso, execute as seguintes etapas:

   1. Em um prompt de comando, mude para a pasta onde o SQL Server está instalado (substitua <VersionNumber> e <InstanceName> no exemplo a seguir):

      cd C:\Program Files\Microsoft SQL Server\MSSQL<VersionNumber>.<InstanceName>\MSSQL\Binn
      

   2. Se a instância for uma instância nomeada do SQL Server, execute o seguinte comando (substitua <InstanceName> no exemplo a seguir):

      sqlservr.exe -s <InstanceName> -c -f -mSQLCMD
      

   3. Se a instância for a instância padrão do SQL Server, execute o seguinte comando:

      sqlservr -c -f -mSQLCMD
      

      Observação

      Os parâmetros -c e -f fazem com que o SQL Server inicie em um modo de configuração mínimo que tenha um tamanho tempdb de 1 MB para o arquivo de dados e 0,5 MB para o arquivo de log. O parâmetro -mSQLCMD impede que qualquer outro aplicativo além de sqlcmd assuma a conexão de usuário único.

3. Conecte-se ao SQL Server com sqlcmd e execute os seguintes comandos Transact-SQL. Substitua <target_size_in_MB> pelo tamanho desejado:

   ALTER DATABASE tempdb MODIFY FILE
   (NAME = 'tempdev', SIZE = <target_size_in_MB>);
   
   ALTER DATABASE tempdb MODIFY FILE
   (NAME = 'templog', SIZE = <target_size_in_MB>);
   

4. Pare o SQL Server. Para fazer isso, pressione Ctrl+C na janela do prompt de comando, reinicie o SQL Server como serviço e depois verifique o tamanho dos arquivos tempdb.mdf e templog.ldf.

Utilize o comando DBCC SHRINKDATABASE

DBCC SHRINKDATABASE recebe o parâmetro target_percent. Esta é a porcentagem desejada de espaço livre deixado no arquivo de banco de dados depois que o banco de dados é reduzido. Se você usar DBCC SHRINKDATABASE, talvez seja necessário reiniciar o SQL Server.

1. Determine o espaço usado atualmente no tempdb usando o procedimento armazenado sp_spaceused. Em seguida, calcule a porcentagem de espaço livre que é deixado para uso como parâmetro para DBCC SHRINKDATABASE. Esse cálculo é baseado no tamanho desejado do banco de dados.

   Observação

   Em alguns casos, talvez seja necessário executar sp_spaceused @updateusage = true para recalcular o espaço usado e obter um relatório atualizado. Para obter mais informações, consulte sp_spaceused.

   Considere o seguinte exemplo:

   Suponha que tempdb tem dois arquivos: o arquivo de dados primário (tempdb.mdf) que é 1.024 MB e o arquivo de log (tempdb.ldf) que é 360 MB. Suponha que sp_spaceused informa que o arquivo de dados primário contém 600 MB de dados. Além disso, suponha que você deseja reduzir o arquivo de dados primário para 800 MB. Calcule a percentagem desejada de espaço livre deixado após a redução: 800 MB - 600 MB = 200 MB. Agora, divida 200 MB por 800 MB = 25 por cento, e essa é a sua target_percent. O arquivo de log de transações é reduzido de acordo, deixando 25% ou 200 MB de espaço livre depois que o banco de dados é reduzido.

2. Conecte-se ao SQL Server com SSMS, Azure Data Studio ou sqlcmd e execute o seguinte comando Transact-SQL. Substitua <target_percent> pela percentagem pretendida:

   DBCC SHRINKDATABASE (tempdb, '<target_percent>');
   

Há limitações com o comando DBCC SHRINKDATABASE em tempdb. O tamanho de destino para dados e arquivos de log não pode ser menor do que o tamanho especificado quando o banco de dados foi criado ou menor do que o último tamanho que foi explicitamente definido usando uma operação de alteração de tamanho de arquivo, como ALTER DATABASE que usa a opção MODIFY FILE. Outra limitação do DBCC SHRINKDATABASE é o cálculo do parâmetro target_percentage e sua dependência do espaço atual que é usado.

Utilize o comando DBCC SHRINKFILE

Use o comando DBCC SHRINKFILE para reduzir os arquivos tempdb individuais. DBCC SHRINKFILE fornece mais flexibilidade do que DBCC SHRINKDATABASE porque você pode usá-lo em um único arquivo de banco de dados sem afetar outros arquivos que pertencem ao mesmo banco de dados. DBCC SHRINKFILE recebe o parâmetro target_size. Este é o tamanho final desejado para o arquivo de banco de dados.

1. Determine o tamanho desejado para o arquivo de dados primário (tempdb.mdf), o arquivo de log (templog.ldf) e os arquivos extras que são adicionados a tempdb. Certifique-se de que o espaço que é usado nos arquivos é menor ou igual ao tamanho de destino desejado.

2. Conecte-se ao SQL Server com o SSMS, o Azure Data Studio ou sqlcmd e execute os seguintes comandos Transact-SQL para os arquivos de banco de dados específicos que você deseja reduzir. Substitua <target_size_in_MB> pelo tamanho desejado:

   USE tempdb;
   GO
   
   -- This command shrinks the primary data file
   DBCC SHRINKFILE (tempdev, '<target_size_in_MB>');
   GO
   
   -- This command shrinks the log file, examine the last paragraph.
   DBCC SHRINKFILE (templog, '<target_size_in_MB>');
   GO
   

Uma vantagem do DBCC SHRINKFILE é que ele pode reduzir o tamanho de um arquivo para um tamanho menor do que seu tamanho original. Você pode gerar DBCC SHRINKFILE em qualquer um dos arquivos de dado ou de log. Não é possível tornar o banco de dados menor do que o tamanho do banco de dados model.

Erro 8909 ao executar operações de redução

Se tempdb estiver sendo usado e se você tentar reduzi-lo usando os comandos DBCC SHRINKDATABASE ou DBCC SHRINKFILE, poderá receber mensagens semelhantes às seguintes, dependendo da versão do SQL Server que estiver usando:

Server: Msg 8909, Level 16, State 1, Line 1 Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (6:8040) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).

Este erro não indica qualquer corrupção real no tempdb. No entanto, pode haver outras razões para erros de corrupção de dados físicos, como o erro 8909, e que esses motivos incluem problemas de subsistema de E/S. Portanto, se o erro acontecer fora das operações de redução, você deve investigar mais.

Embora uma mensagem 8909 seja retornada para o aplicativo ou para o usuário que está executando a operação de redução, as operações de redução não falham.

Conteúdo relacionado

• 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.databases (Transact-SQL)
• sys.database_files (Transact-SQL)
• Reduzir um banco de dados
• DBCC SHRINKDATABASE (Transact-SQL)
• DBCC SHRINKFILE (Transact-SQL)
• excluir dados ou arquivos de log de um banco de dados
• Reduzir um arquivo