Como diminuir a base de dados tempdb no SQL Server

Este artigo indica três métodos que pode utilizar para diminuir a base de dados tempdb para um tamanho mais pequeno do que o último tamanho configurado. O primeiro método proporciona-lhe um controlo total do tamanho dos ficheiros tempdb mas requer o reinício do SQL Server. O segundo método diminui a totalidade do tempdb, com algumas limitações, que podem incluir o reinício do SQL Server. O terceiro método permite-lhe diminuir ficheiros individuais no tempdb. Os dois últimos métodos requerem que não exista actividade na base de dados tempdb durante a operação de redução.

Nota Se estiver a utilizar o SQL Server 2005, estes métodos também se aplicam. No entanto, deverá utilizar o SQL Server Management Studio em vez do Enterprise Manager e Query Analyzer para efectuar estas operações. Tenha em atenção que o SQL Server management studio em 2005 não exibe o tamanho correcto dos ficheiros tempdb após uma operação de redução. O valor "Espaço atribuído actual" é sempre recuperado dos sys.master_ficheiros DMV e este valor não é actualizado após uma operação de redução para uma base de dados tempdb. Para localizar o tamanho correcto dos ficheiros tempdb após uma operação de redução, execute a seguinte declaração no estúdio de gestão:
Nota O SQL Server 2008 não é afectado por este problema (a interface gráfica apresenta o tamanho incorrecto.)

Informações de Tempdb

O tempdb é uma área de trabalho temporária. Entre outras utilizações, o SQL Server utiliza o tempdb para:

• Armazenamento de tabelas temporárias explicitamente criadas.
• Tabelas de funcionamento que guardam resultados intermédios durante a ordenação e processamento de consultas.
• Cursores estáticos materializados.

O SQL Server regista apenas as informações suficientes no registo de transacção de tempdb para rolar uma transacção para trás, mas não para refazer transacções durante a recuperação da base de dados. Esta função aumenta o desempenho dos extractos INSERT no tempdb. Além disso, não existe necessidade de introduzir informações para refazer quaisquer transacções, uma vez que o tempdb é recriado de cada vez que reiniciar o SQL Server; não tem quaisquer transacções para rolar para a frente ou para trás. Quando o SQL Server inicia, o tempdb é recriado, utilizando uma cópia da base de dados do modelo e é reposto para o último tamanho configurado.

Por predefinição, a base de dados tempdb é configurada para aumentar automaticamente como necessário; por conseguinte, esta base de dados pode aumentar com o tempo para um tamanho maior do que o pretendido. Basta reiniciar o SQL Server para repor o tamanho do tempdb para o último tamanho configurado. O tamanho configurado é o último tamanho explícito definido com uma operação de modificação de tamanho de ficheiro como ALTER DATABASE com a opção MODIFY FILE ou o extracto DBCC SHRINKFILE. Este artigo apresenta três métodos que pode utilizar para diminuir o tempdb para um tamanho mais pequeno do que o último tamanho configurado.

Método 1 para Diminuir Tempdb

Este método requer que o SQL Server seja reiniciado.

1. Paragem do SQL Server. Abra uma linha de comandos e inicie o SQL Server, escrevendo o comando seguinte:
   
   sqlservr -c -f
   
   Os parâmetros -c e -f forçam o SQL Server a iniciar num modo de configuração mínimo com um de tempdb de 1 MB para o ficheiro de dados e 0,5 MB para o ficheiro de registo.
   
   NOTA: Se utilizar uma instância nomeada SQL Server, necessita de alterar para a pasta adequada (Program Files\Microsoft SQL Server\MSSQL$instance name\Binn) e utilizar o -s parâmetro (-s%instance_name%).
2. Ligue ao SQL Server com o Analisador de Contas e, em seguida, execute os comandos Transact-SQL:

      ALTER DATABASE tempdb MODIFY FILE
      (NAME = 'tempdev', SIZE = target_size_in_MB) 
      --Tamanho de destino pretendido para o ficheiro de dados
   
      ALTER DATABASE tempdb MODIFY FILE
      (NAME = 'templog', SIZE = target_size_in_MB)
      --Tamanho de destino pretendido para o ficheiro de registo
   					

3. Interrompa o SQL Server, premindo Ctrl-C na janela de linha de comandos, reinicie o SQL Server como um serviço e, em seguida, verifique o tamanho dos ficheiros Tempdb.mdf e Templog.ldf.

Uma das limitações deste método prende-se com o facto de funcionar apenas com ficheiros tempdb lógicos, tempdev e templog. Se os ficheiros adicionais tiverem sido adicionados ao tempdb pode diminuí-los após reiniciar o SQL Server como um serviço. Todos os ficheiros tempdb são recriados durante o arranque; portanto, estão vazios e podem ser removidos. Para remover ficheiros adicionais no tempdb, utilize o comando ALTER DATABASE com a opção REMOVE FILE.

Método 2 para Diminuir Tempdb

Utilize o comando DBCC SHRINKDATABASE para reduzir a totalidade da base de dados tempdb. DBCC SHRINKDATABASE recebe o parâmetro target_percent, que é a percentagem pretendida de espaço disponível no ficheiro da base de dados após a base de dados ter sido reduzida. Se utilizar o DBCC SHRINKDATABASE, poderá ter de reiniciar o SQL Server.

IMPORTANTE: Se executar o DBCC SHRINKDATABASE, não pode haver qualquer outro tipo de actividade da base de dados tempdb. Para assegurar que não existem outros processos a utilizar o tempdb, enquanto o DBCC SHRINKDATABASE está a ser executado, necessita de utilizar o SQL Server no modo de utilizador único. Para mais informações, consulte a secção Efeitos da Execução do DBCC SHRINKDATABASE ou DBCCSHRINKFILE Enquanto Tempdb Está a ser Utilizado deste artigo.

1. Determine o espaço que está a ser utilizado no tempdb, através do procedimento de sp_spaceused armazenado. Em seguida, calcule a percentagem de espaço livre para utilização como parâmetro para DBCC SHRINKDATABASE; este cálculo é baseado no tamanho pretendido.
   
   Nota Em alguns casos poderá ter de executar sp_spaceused @updateusage=true para recalcular o espaço utilizado e para obter um relatório actualizado. Para obter mais informações sobre o procedimento sp_spaceused armazenado, consulte o SQL Server Books Online.
   
   Considere este exemplo:
   o tempdb tem dois ficheiros, o ficheiro de dados primário (Tempdb.mdf), que tem 100 MB e o ficheiro de registo (Tempdb.ldf), que tem 30 MB. O sp_spaceused indica que o ficheiro de dados primário contém 60 MB de dados. Considere, igualmente, que pretende reduzir o ficheiro de dados primário para 80 MB. Calcule a percentagem do espaço disponível pretendida após a redução, 80 MB - 60 MB = 20 MB. Em seguida, divida 20 MB por 80 MB = 25% e obtém o target_percent. O ficheiro de registo da transacção é reduzido em conformidade, deixando 25% ou 20 MB de espaço livre após a redução da base de dados.
2. Ligue ao SQL Server com o Analisador de Contas e, em seguida, execute os comandos Transact-SQL:

      dbcc shrinkdatabase (tempdb, 'target percent') 
      -- Este comando reduz a totalidade da base de dados tempdb
   					

Existem limitações para a utilização do comando DBCC SHRINKDATABASE na base de dados tempdb. O tamanho de destino dos ficheiros de dados e registo não pode ser inferior ao tamanho especificado quando a base de dados foi criada ou o último tamanho explicitamente definido, como ALTER DATABASE com a opção MODIFY FILE ou o comando DBCC SHRINKFILE. Outra das limitações do DBCC SHRINKDATABASE é o cálculo do parâmetro target_percentage e a respectiva dependência do espaço actual utilizado.

Método 3 para Diminur Tempdb

Utilize o comando DBCC SHRINKFILE para reduzir os ficheiros tempdb individuais. O DBCC SHRINKFILE proporciona mais flexibilidade do que o DBCC SHRINKDATABASE, uma vez que pode utilizá-lo num único ficheiro de base de dados, sem afectar outros ficheiros que pertencem à mesma base de dados. O DBCC SHRINKFILE recebe o parâmetro tamanho de destino, que representa o tamanho final pretendido do ficheiro da base de dados.

IMPORTANTE: Necessita de executar o comando DBCC SHRINKFILE enquanto não ocorre mais actividade na base de dados tempdb. Para assegurar que não existem outros processos a utilizar o tempdb enquanto o DBCC SHRINKFILE está a ser executado, necessita de reiniciar o SQL Server no modo de utilizador único. Para mais informações sobre o DBCC SHRINKFILE, consulte a secção Efeitos da Execução do DBCC SHRINKDATABASE ou DBCCSHRINKFILE Enquanto Tempdb Está a ser Utilizado deste artigo.

1. Determine o tamanho pretendido do ficheiro de dados primário (tempdb.mdf), o ficheiro registo tempdb (templog.ldf), e ou ficheiros adicionados a tempdb. Certifique-se de que o espaço utilizado nos ficheiros é inferior ou igual ao tamanho de destino pretendido.
2. Ligue ao SQL Server com o Analisador de Contas e, em seguida, execute os comandos Transact-SQL para os ficheiros da base de dados específica que necessita reduzir:

      utilize tempdb
      go
   
      dbcc shrinkfile (tempdev, 'target size in MB')
      go
      -- este comando reduz o ficheiro de dados primário
   
      dbcc shrinkfile (templog, 'target size in MB')
      go
      -- este comando reduz o ficheiro de registo, observe o último parágrafo.
   						

Uma das vantagens do DBCC SHRINKFILE é poder reduzir o tamanho de um ficheiro para um tamanho inferior ao do tamanho original. Pode emitir o DBCC SHRINKFILE em quaisquer ficheiros de dados ou de registo. Uma das limitações do DBCC SHRINKFILE é não poder tornar a base de dados mais pequena do que a base de dados modelo.

No SQL Server 7.0, uma redução do registo de transacção é uma operação diferida e necessita de emitir um truncamento de registo e cópia de segurança para ajudar na operação de redução numa base de dados. No entanto, por predefinição, otempdb tem a opção trunc log on chkpt definida para LIGADO; portanto, não necessita de emitir um truncamento de registo para a base de dados. Para obter informações adicionais sobre como reduzir um registo de transacção da base de dados no SQL Server 7.0, clique no número de artigo abaixo, para visualizar o artigo na Base de Dados de Conhecimento da Microsoft:

Efeitos da Execução do DBCC SHRINKDATABASE ou DBCCSHRINKFILE Enquanto Tempdb Está a ser Utilizado

Se o tempdb estiver a ser utilizado e tentar reduzi-lo, utilizando os comandos DBCC SHRINKDATABASE ou DBCC SHRINKFILE, poderá receber múltiplos erros de consistência semelhantes ao seguinte tipo e a operação de redução pode falhar: Apesar de o erro 2501 poder não indicar qualquer tipo de corrupção no tempdb, a operação de redução falha. Por outro lado, o erro 8909 pode indicar corrupção na base de dados tempdb. Reinicie o SQL Server para recriar o tempdb e limpar os erros de consistência. No entanto, não se esqueça que é possível que haja outras razões para os erros de corrupção de dados físicos, como o erro 8909 e os problemas de subsistema de entrada/saída.

REFERÊNCIAS

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