Como reduzir o banco de dados tempdb no SQL Server

Este artigo discute três métodos que você pode usar para reduzir o banco de dados tempdb para um tamanho menor do que o último tamanho configurado. O primeiro método fornece controle completo do tamanho dos arquivos tempdb, mas exige que você reinicie o SQL Server. O segundo método reduz o tempdb como um todo, com algumas limitações que podem incluir a reinicialização do SQL Server. O terceiro método permite você reduzir os arquivos individuais no tempdb. Os dois últimos métodos exigem que nenhuma atividade ocorra no banco de dados tempdb durante a operação de redução.

Observação Se você estiver usando o SQL Server 2005, estes métodos também se aplicam. Entretanto, você deve usar o SQL Server Management Studio ao invés do Enterprise Manager e o Analisador de Consultas para realizar estas operações. Observe ainda que o SQL Server management studio em 2005 não exibe o tamanho correto dos arquivos tempdb depois da operação de redução. O valor 'Espaço alocado atualmente' é sempre puxado do sys.master_files DMV e este valor não é atualizado depois de uma operação de redução ocorrer no banco de dados tempdb. Para encontrar o tamanho correto dos arquivos tempdb depois de uma operação de redução, execute a seguinte instrução no management studio:
Observação O SQL Server 2008 não é afetado por este problema (GUI mostrando o tamanho incorreto.)

Informação Tempdb

O tempdb é um espaço de trabalho temporário. Entre outros usos, o SQL Server usa o tempdb para:

• Armazenar tabelas temporárias criadas explicitamente.
• Tabelas de trabalho que possuem resultados intermediários criados durante o processo e classificação da consulta.
• Cursores estáticos materializados.

O SQL Server registra somente informações suficientes no log de transação tempdb para reverter uma transação, mas não refaz transações durante a recuperação do banco de dados. Este recurso aumenta o desempenho das instruções INSERT no tempdb. Além disso, não há necessidade de registrar informações para refazer quaisquer transações porque o tempdb é re-criado cada vez que você reinicia o SQL Server; portanto, não tem quaisquer transações para efetuar roll forward ou roll back. Quando o SQL Server inicia, o tempdb é re-criado usando uma cópia do banco de dados modelo e é reiniciado para seu último tamanho configurado.

Por padrão, o banco de dados tempdb é configurado para aumentar automaticamente conforme necessário; portanto, esse banco de dados pode crescer no tempo para um tamanho maior do que o desejado. Uma reinicialização simples do SQL Server redefine o tamanho do tempdb para o seu último tamanho configurado. O tamanho configurado é o último tamanho explícito definido com uma operação de mudança de tamanho do arquivo como ALTER DATABASE com a opção MODIFY FILE ou a instrução DBCC SHRINKFILE. Este artigo apresenta três métodos que você pode usar para reduzir o tempdb a um tamanho menor do que o tamanho configurado.

Método 1 para reduzir Tempdb

Este método exige que você reinicie o SQL Server.

1. Pare o SQL Server. Abra um prompt de comando e inicie o SQL Server digitando o seguinte comando:
   
   sqlservr -c -f
   
   Os parâmetros -c e -f fazem com que o SQL Server inicie em um modo de configuração mínima com o tamanho do tempdb de 1 MB para o arquivo de dados e 0,5 MB para o arquivo de log.
   
   OBSERVAÇÃO: Se você usar uma instância nomeada SQL Server, você deve alterar para a pasta adequada (Program Files\Microsoft SQL Server\MSSQL$instance name\Binn) e usar o botão -s (-s%instance_name%).
2. Conecte ao SQL Server com o Analisador de Consulta e execute os seguintes comandos Transact-SQL:

      ALTER DATABASE tempdb MODIFY FILE
      (NAME = 'tempdev', SIZE = target_size_in_MB) 
      --Tamanho alvo desejado para o arquivo de dados
   
      ALTER DATABASE tempdb MODIFY FILE
      (NAME = 'templog', SIZE = target_size_in_MB)
      --Tamanho alvo desejado para o arquivo de log
   					

3. Pare o SQL Server pressionando Ctrl-C na janela de prompt de comando, reinicie o SQL Server como um serviço e verifique o tamanho dos arquivos Tempdb.mdf e Templog.ldf.

Uma limitação deste método é que ele somente opera nos arquivos lógicos tempdb, tempdev e templog padrões. Se mais arquivos são adicionados ao tempdb você pode reduzi-los depois de reiniciar o SQL Server como um serviço. Todos os arquivos tempdb são recriados durante a reinicialização; portanto, eles estão vazios e podem ser removidos. Para remover arquivos adicionais no tempdb, use o comando ALTER DATABASE com a opção REMOVE FILE.

Método 2 para reduzir Tempdb

Use o comando DBCC SHRINKDATABASE para reduzir o banco de dados tempdb como um todo. DBCC SHRINKDATABASE recebe o parâmetro target_percent, que é a porcentagem desejada de espaço livre no arquivo do banco de dados depois do banco de dados ser reduzido. Se você usa o DBCC SHRINKDATABASE, você pode ter que reiniciar SQL Server.

IMPORTANTE: Se você executar o DBCC SHRINKDATABASE, nenhuma outra atividade pode estar ocorrendo com o banco de dados tempdb. Para certificar-se de que outros processos não podem usar o tempdb enquanto o DBCC SHRINKDATABASE estiver executando, você deve iniciar o SQL Server no modo de usuário único. Para obter mais informações, consulte a seção Efeitos da execução do DBCC SHRINKDATABASE ou DBCCSHRINKFILE enquanto o Tempdb está em uso deste artigo.

1. Determine o espaço usado atualmente no tempdb usando o procedimento armazenado sp_spaceused. Calcule a porcentagem de espaço livre restante para usar como um parâmetro para DBCC SHRINKDATABASE; este cálculo é baseado no tamanho do banco de dados desejado.
   
   Observação Em alguns casos você pode ter que executar sp_spaceused @updateusage=true para recalcular o espaço usado e obter um relatório atualizado. Consulte os Manuais Online do SQL Server para obter mais informações sobre o procedimento armazenado sp_spaceused.
   
   Considere este exemplo:
   Assuma que o tempdb tem dois arquivos, o arquivo de dados primário (Tempdb.mdf), que tem 100 MB de tamanho e o arquivo de log (Tempdb.ldf), que tem 30 MB. Assuma que o sp_spaceused relata que o arquivo de dados primário contém 60 MB de dados. Também assuma que você quer reduzir o arquivo de dados primário para 80 MB. Calcule a porcentagem desejada do espaço livre depois da redução, 80 MB - 60 MB = 20 MB. Agora, divida 20 MB por 80 MB = 25% e este é o seu target_percent. O arquivo de log de transação é reduzido de acordo, deixando 25% ou 20 MB de espaço livre depois de reduzir o banco de dados.
2. Conecte o SQL Server com o Analisador de Consulta e execute os seguintes comandos Transact-SQL:

      dbcc shrinkdatabase (tempdb, 'target percent') 
      -- Este comando reduz o banco de dados tempdb como um todo
   					

Não existem limites para o uso do comando DBCC SHRINKDATABASE no banco de dados tempdb. O tamanho alvo para arquivos de log e de dados não pode ser menor do que o tamanho especificado quando o banco de dados foi criado ou o último tamanho definido explicitamente com uma operação de mudança de tamanho de arquivo, como o ALTER DATABASE com a opção MODIFY FILE ou o comando DBCC SHRINKFILE. Outra limitação do DBCC SHRINKDATABASE é o cálculo do parâmetro target_percentage e sua dependência no espaço usado atual.

Método 3 para reduzir Tempdb

Use o comando DBCC SHRINKFILE para reduzir os arquivos tempdb individuais. O DBCC SHRINKFILE fornece mais flexibilidade do que o DBCC SHRINKDATABASE, pois você o usa em um banco de dados único sem afetar outros arquivos que pertençam ao mesmo banco de dados. O DBCC SHRINKFILE recebe o parâmetro do tamanho alvo, que é o tamanho final desejado para o arquivo do banco de dados.

IMPORTANTE: Você deve executar o comando DBCC SHRINKFILE enquanto nenhuma outra atividade ocorrer no banco de dados tempdb. Para certificar-se de que outros processos não podem usar o tempdb enquanto o DBCC SHRINKFILE é executado, você deve reiniciar o SQL Server no modo de usuário único. Para obter mais informações sobre DBCC SHRINKFILE, veja a seção Efeitos de execução do DBCC SHRINKDATABASE ou DBCCSHRINKFILE enquanto o Tempdb está em uso deste artigo.

1. Determine o tamanho desejado para o arquivo de dados primário (tempdb.mdf), o arquivo de log (templog.ldf) e/ou arquivos adicionados ao tempdb. Certifique-se de que o espaço usado nos arquivos é menor ou igual ao tamanho alvo desejado.
2. Conecte ao SQL Server com o Analisador de Consulta e execute os seguintes comandos Transact-SQL para os arquivos do banco de dados específico que deseja reduzir:

      use tempdb
      ir
   
      dbcc shrinkfile (tempdev, 'tamanho alvo em MB')
      ir
      -- este comando reduz o arquivo de dados primário
   
      dbcc shrinkfile (templog, 'tamanho alvo em MB')
      ir
      -- este comando reduz o arquivo de log, olhe no último parágrafo.
   						

Uma vantagem do DBCC SHRINKFILE é que ele pode reduzir o tamanho de um arquivo a um tamanho menor do que o original. Você pode emitir DBCC SHRINKFILE em qualquer arquivos de log ou de dados. Uma limitação do DBCC SHRINKFILE é que você não pode tornar o banco de dados menor do que o tamanho do banco de dados modelo.

No SQL Server 7.0, uma redução do log de transação é uma operação adiada e você deve emitir um truncamento e backup do log para ajudar a operação de redução em um banco de dados. Entretanto, por padrão, o tempdb tem a opção de truncar log no chkpt definida para ATIVADO; entretanto, você não precisa emitir um truncamento do log para este banco de dados. Para obter mais informações sobre como reduzir um log de transação do banco de dados no SQL Server 7.0, clique no número de artigo abaixo para vizualizá-lo na Base de Dados de Conhecimento Microsoft:

Efeitos da execução do DBCC SHRINKDATABASE ou DBCCSHRINKFILE enquanto o Tempdb estiver em uso

Se o tempdb está em uso e você tentar reduzi-lo usando os comandos DBCC SHRINKDATABASE ou DBCC SHRINKFILE, você pode receber vários erros de consistência similares ao tipo seguinte e a operação de redução pode falhar: Apesar do erro 2501 poder não indicar qualquer corrupção no tempdb, ele causa falha na operação de redução. Por outro lado, o erro 8909 pode indicar corrupção no banco de dados do tempdb. Reinicie o SQL Server para recriar o tempdb e limpar os erros de consistência. Entretanto, tenha em mente que pode haver outras razões para erros de corrupção de dados físicos como o erro 8909 e eles incluem problemas de subsistema de entrada/saída.

REFERÊNCIAS

Manuais Online do SQL Server; tópicos: "DBCC SHRINKFILE"; "DBCC SHRINKDATABASE"