Como reduzir a base de dados tempdb no SQL Server

Este artigo descreve três métodos que pode utilizar para diminuir a base de dados tempdb para um tamanho mais pequeno do que o respectivo tamanho configurado último. O primeiro método dá-lhe controlo total do tamanho dos ficheiros tempdb, mas requer que reinicie o SQL Server. O segundo método diminui o tempdb como um todo, com algumas limitações que poderá incluir a reiniciar o SQL Server. O terceiro método permite-lhe diminuir ficheiros individuais no tempdb. Os últimos dois métodos requerem que sem actividade ocorrer 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 SQL Server Management Studio em vez do Enterprise Manager e Query Analyzer para efectuar estas operações. Mais, tenha em atenção que o estúdio de gestão de SQL Server em 2005 não mostra o tamanho correcto dos ficheiros de tempdb após uma operação de redução. O valor 'Actualmente atribuído espaço' sempre é retirado do sys.master_files DMV e este valor não é actualizada depois de uma operação de redução acontece para a base de dados tempdb. Para localizar o tamanho correcto do tempdb ficheiros após uma operação de redução execute a seguinte instrução no estúdio de gestão:
Nota SQL Server 2008 não é afectado por este problema (GUI mostrando tamanho incorrecto).

Informações de TempDB

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

• Armazenamento de tabelas temporárias explicitamente criados.
• Worktables resultados intermédios criado durante a consulta de processamento e ordenação.
• Cursores estáticos materialized.

SQL Server regista apenas informações suficientes no registo de transacções tempdb para reverter uma transacção mas não para refazer transacções durante a recuperação da base de dados. Esta funcionalidade aumenta o desempenho de instruções INSERT no tempdb. Além disso, não é necessário registar informações para refazer quaisquer transacções porque o tempdb é recriada sempre que reiniciar o SQL Server; por conseguinte, não tem quaisquer transacções para rollforward ou recuperar. Quando o SQL Server é iniciado, o tempdb recriada utilizando uma cópia da base de dados modelo e é reposto para o respectivo tamanho configurado último.

Por predefinição, a base de dados tempdb está configurado para autogrow conforme necessário; por conseguinte, esta base de dados pode crescer a tempo para um tamanho maior do que desejar. Um reinício simples do SQL Server repõe o tamanho do tempdb para o respectivo tamanho configurado último. O tamanho configurado é o último tamanho explícito definido com um tamanho de ficheiro alterar operação como, por exemplo, ALTER DATABASE com a opção de modificar o ficheiro ou a instrução DBCC SHRINKFILE. Este artigo apresenta três métodos que pode utilizar para diminuir tempdb para um tamanho mais pequeno do que o respectivo tamanho configurado.

Método 1 para encolher Tempdb

Este método requer que reinicie o SQL Server.

1. Interromper o SQL Server. Abra uma linha de comandos e, em seguida, inicie o SQL Server, escrevendo o seguinte comando:
   
   sqlservr - c-f
   
   O - c e -f parâmetros fazer com que o SQL Server para iniciar no modo de configuração mínima com um tamanho 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 do SQL Server, tem de mudar para a pasta apropriada (Program Files\Microsoft SQL Server\MSSQL $ instância name\Binn) e utilizar o -s mudar (-s % Nome_da_instância %).
2. Ligar ao SQL Server com o Query Analyzer e, em seguida, execute os seguintes comandos de Transact-SQL:

      ALTER DATABASE tempdb MODIFY FILE
      (NAME = 'tempdev', SIZE = target_size_in_MB) 
      --Desired target size for the data file
   
      ALTER DATABASE tempdb MODIFY FILE
      (NAME = 'templog', SIZE = target_size_in_MB)
      --Desired target size for the log file
   					

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

Uma limitação deste método é que ele só funciona na predefinição tempdb lógico ficheiros, tempdev e templog. Se ficheiros adicionais foram adicionados à tempdb que pode diminuir depois de reiniciar o SQL Server como um serviço. Todos os ficheiros de tempdb são recriados durante o arranque; por conseguinte, estão vazias e pode ser removidos. Para remover ficheiros adicionais em tempdb, utilize o comando ALTER DATABASE com a opção REMOVE o ficheiro.

Método 2 para encolher Tempdb

Utilize o comando DBCC SHRINKDATABASE para diminuir a base de dados tempdb como um todo. DBCC SHRINKDATABASE recebe parâmetro percentagem_de_destino, que é a percentagem pretendida da esquerda de espaço livre no ficheiro de base de dados depois da base de dados é diminuído. Se utilizar DBCC SHRINKDATABASE, poderá ter de reiniciar o servidor de SQL.

Importante: Se executar DBCC SHRINKDATABASE, sem actividade pode estar a ocorrer com a base de dados tempdb. Para se certificar de que outros processos não é possível utilizar tempdb enquanto é executado o DBCC SHRINKDATABASE, tem de iniciar o SQL Server no modo de utilizador único. Para mais informações consulte a secção Effects of Execution of DBCC SHRINKDATABASE or DBCCSHRINKFILE While Tempdb Is In Use deste artigo.

1. Determine o espaço actualmente utilizado em tempdb, utilizando o procedimento armazenado sp_spaceused. Em seguida, calcular a percentagem de espaço livre esquerda para utilização como um parâmetro para DBCC SHRINKDATABASE; este cálculo é baseado no tamanho da base de dados pretendido.
   
   Nota Em alguns casos poderá ter de executar sp_spaceused @ updateusage = true para recalcular o espaço utilizado e obter um relatório actualizado. Consulte SQL Server Books Online para obter mais informações sobre o procedimento armazenado sp_spaceused.
   
   Considere este exemplo:
   Partem do princípio de que tempdb tem dois ficheiros, o ficheiro de dados primário (Tempdb.mdf), que é 100 MB no tamanho e o ficheiro de registo (Tempdb.ldf), que é 30 MB. Suponha que sp_spaceused comunica que o ficheiro de dados principal contém 60 MB de dados. Também partem do princípio de que pretende diminuir o ficheiro de dados principal para 80 MB. Calcule a percentagem pretendida da esquerda de espaço livre após a redução, 80 MB - 60 MB = 20 MB. Agora, dividir 20 MB por 80 MB = 25 % e que é o percentagem_de_destino. O ficheiro de registo de transacções é diminuído em conformidade, deixando 25 % ou 20 MB de espaço livre depois da base de dados é diminuído.
2. Ligar ao SQL Server com o Query Analyzer e, em seguida, execute os seguintes comandos de Transact-SQL:

      dbcc shrinkdatabase (tempdb, 'target percent') 
      -- This command shrinks the tempdb database as a whole
   					

Existem limitações para utilização do comando DBCC SHRINKDATABASE na base de dados tempdb. O tamanho de destino para ficheiros de dados e de registo não pode ser menor do que o tamanho especificado quando a base de dados foi criada ou o último tamanho explicitamente definido com uma operação de alteração de tamanho do ficheiro como, por exemplo, ALTER DATABASE com a opção de modificar o ficheiro ou o comando DBCC SHRINKFILE. Outra limitação do DBCC SHRINKDATABASE é o cálculo do parâmetro target_percentage e respectiva dependência no actual espaço utilizado.

Método 3 para encolher Tempdb

Utilize o comando DBCC SHRINKFILE para diminuir ficheiros individuais tempdb. DBCC SHRINKFILE fornece mais flexibilidade do que DBCC SHRINKDATABASE porque pode utilizá-lo num ficheiro de base de dados única sem afectar outros ficheiros que pertencem à mesma base de dados. DBCC SHRINKFILE recebe o parâmetro de tamanho de destino, que é o tamanho final pretendido para o ficheiro de base de dados.

Importante: tem de executar o comando de DBCC SHRINKFILE enquanto sem actividade ocorre na base de dados tempdb. Para se certificar de que outros processos não é possível utilizar tempdb enquanto executa DBCC SHRINKFILE, tem de reiniciar o SQL Server no modo de utilizador único. Para obter mais informações sobre DBCC SHRINKFILE, consulte a secção Effects of Execution of DBCC SHRINKDATABASE or DBCCSHRINKFILE While Tempdb Is In Use deste artigo.

1. Determine o tamanho pretendido para o ficheiro de dados primário (tempdb.mdf), o ficheiro de registo (templog.ldf), e/ou ficheiros adicionais à tempdb. Certifique-se de que o espaço utilizado nos ficheiros é menor ou igual ao tamanho de destino pretendido.
2. Ligar ao SQL Server com o Query Analyzer e, em seguida, execute os seguintes comandos de Transact-SQL para os ficheiros de base de dados específica que necessitar de diminuir:

      use tempdb
      go
   
      dbcc shrinkfile (tempdev, 'target size in MB')
      go
      -- this command shrinks the primary data file
   
      dbcc shrinkfile (templog, 'target size in MB')
      go
      -- this command shrinks the log file, look at the last paragraph.
   						

Uma vantagem do DBCC SHRINKFILE é que ele pode reduzir o tamanho de um ficheiro para um tamanho mais pequeno do que o respectivo tamanho original. Pode emitir DBCC SHRINKFILE em qualquer um dos ficheiros de dados ou registo. Uma limitação do DBCC SHRINKFILE é que não pode efectuar a base de dados mais pequena do que o tamanho da base de dados modelo.

No SQL Server 7.0 diminuir um registo de transacções é uma operação diferida e tem que emitir uma truncagem do registo e a cópia de segurança para ajudar a operação de redução de uma base de dados. No entanto, por predefinição, tempdb tem a opção de truncagem sessão chkpt definir; por conseguinte, aí é não necessário emitir uma truncagem do registo para essa base de dados. Para obter informações adicionais como para diminuir uma base de dados de transacção iniciar sessão no SQL Server 7.0, clique no número de artigo existente 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á em utilização

Se tempdb está em utilização e tentar diminuir utilizando os comandos DBCC SHRINKDATABASE ou DBCC SHRINKFILE, poderá receber vários erros de coerência semelhantes à seguinte tipo e a operação de diminuição poderá falhar: Apesar de erro 2501 poderá não ser indicam de quaisquer danos em tempdb, faz com que a falha da operação de redução. Por outro lado, erro 8909 poderia indicar danos na base de dados tempdb. Reinicie o SQL Server para recriar o tempdb e limpar os erros de consistência. No entanto, tenha em atenção que poderão existir outras razões para erros de Corrupção de dados físicos como erro 8909 e aqueles incluem problemas de subsistema de entrada/saída.

REFERÊNCIAS

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