Como reduzir a base de dados tempdb no SQL Server

Este artigo aborda 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 dá-lhe controlo total sobre o 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 reiniciar o SQL Server. O terceiro método permite-lhe diminuir ficheiros individuais em de 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 de ' Gestor de empresas ' e o analisador de consultas para efectuar estas operações.

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 criadas explicitamente.
• Worktables resultados intermédios criado durante a consulta de processamento e ordenação.
• Materialized cursores estáticos.

SQL Server regista apenas informações suficientes no registo de transacções de tempdb, para anular uma transaçã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 em de tempdb. Para além disso, não é necessário para registar informações para refazer as transacções porque o tempdb é recriada sempre que reiniciar o servidor de SQL; assim, não tem quaisquer transacções para rollforward ou recuperar. Quando o SQL Server é iniciado, o tempdb é criado novamente 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 está configurado para autogrow conforme necessário; assim, 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 o último tamanho configurado. O tamanho configurado é o tamanho de explícito último definido com um tamanho de ficheiro alterar operação como 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 inferior respectivo tamanho configurado.

Método 1 para encolher tempdb

Este método requer que reinicie o SQL Server.

1. Pare servidor SQL. Abra uma linha de comandos e reinicie o SQL Server, escrevendo o seguinte comando:
   
   -c - sqlservr de f
   
   O - c e -f parâmetros provocar o SQL Server para iniciar num 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, deve alterar para a pasta adequada ( Program Files\Microsoft SQL Server\MSSQL $ instância name\Binn ) e utilizar o -s mudar (-s instance_name %).
2. Ligar ao SQL Server com o analisador de consultas e, em seguida, executar 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. Parar servidor de SQL, premindo CTRL-C na janela Linha de comandos, reiniciar o servidor de SQL como um serviço e, em seguida, verificar o tamanho dos ficheiros tempdb.mdf e Templog.ldf.

Uma limitação deste método é que este só funciona no ficheiros de lógica de tempdb predefinido, tempdev e templog . Se foram adicionados ficheiros adicionais para tempdb poderá diminui-los depois de reiniciar 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 de ALTER DATABASE com a opção Remover 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 o parâmetro percentagem_de_destino , que é a percentagem pretendida do lado esquerdo do espaço livre no ficheiro da base de dados depois da base de dados é diminuído. Se utilizar o comando DBCC SHRINKDATABASE, poderá ter de reiniciar o SQL Server.

importante : se executar DBCC SHRINKDATABASE, sem qualquer outra actividade pode estar a ocorrer com a base de dados tempdb . Para se certificar de que outros processos não podem utilizar tempdb enquanto é executado o DBCC SHRINKDATABASE, tem de iniciar do 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 tempdb utilizando o procedimento de sp_spaceused armazenados. Em seguida, calcular a percentagem de espaço livre disponível para ser utilizado como um parâmetro para DBCC SHRINKDATABASE; este cálculo baseia-se no tamanho de base de dados desejado.
   
   Nota : em alguns casos poderá ser necessário 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 sp_spaceused armazenados.
   
   Considere este exemplo:
   Suponha que tempdb tem dois ficheiros, o ficheiro de dados primário (tempdb.mdf), que é 100 MB em tamanho e o ficheiro de registo (tempdb.ldf), que é 30 MB. Suponha que sp_spaceused indica que o ficheiro de dados principal contém 60 MB de dados. Suponha também que pretende reduzir o ficheiro de dados principal a 80 MB. Calcular a percentagem pretendida de espaço livre disponível depois da 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 log de transacções é diminuído em conformidade, deixando de 25 % ou 20 MB de espaço livre depois da base de dados é diminuído.
2. Ligar ao SQL Server com o analisador de consultas e, em seguida, executar 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 que o tamanho especificado quando a base de dados foi criada ou o último tamanho explicitamente definido com uma operação alterar tamanho do ficheiro como ALTER DATABASE com a opção de modificar o ficheiro ou o comando DBCC SHRINKFILE. Outro limite de 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 os ficheiros individuais tempdb . DBCC SHRINKFILE fornece mais flexibilidade do DBCC SHRINKDATABASE porque pode utilizá-lo num ficheiro de base de dados de single sem afectar outros ficheiros que pertencem à mesma base de dados. DBCC SHRINKFILE recebe o parâmetro de tamanho de destino , que é o tamanho de final pretendido para o ficheiro de base de dados.

importante : tem de executar DBCC SHRINKFILE comando enquanto não outra actividade ocorre na base de dados tempdb . Para se certificar de que outros processos não podem utilizar tempdb enquanto executa o comando DBCC SHRINKFILE, tem de reiniciar do SQL Server no modo de utilizador único. Para mais informações sobre o comando DBCC SHRINKFILE, consulte a secção de 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 analisador de consultas e, em seguida, executar 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 de DBCC SHRINKFILE é que o pode reduzir o tamanho de um ficheiro para um tamanho mais pequeno do que o tamanho original. Pode emitir DBCC SHRINKFILE em qualquer um dos ficheiros de dados ou o registo. Uma limitação do DBCC SHRINKFILE é que não é possível fazer a base de dados inferior ao tamanho da base de dados do 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 definida na; assim, existe 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 sessão no SQL Server 7.0, clique no número de artigo existente abaixo para visualizar o artigo na Microsoft Knowledge Base:

Efeitos da execução de DBCC SHRINKDATABASE ou DBCCSHRINKFILE enquanto tempdb está em utilização

Se tempdb está a ser utilizado e tentar diminui-lo utilizando os comandos DBCC SHRINKDATABASE ou DBCC SHRINKFILE, poderá receber vários erros de coerência semelhante à seguinte tipo e a operação de redução poderá falhar: Embora não possa ser indicativas de quaisquer danos em tempdb de erro 2501, faz com que a operação de redução falha. Por outro lado, o erro 8909 foi de indicam danos na base de dados tempdb . Reinicie o servidor de SQL para recriar tempdb e limpar os erros de consistência. No entanto, tenha em atenção que poderão existir outros motivos para erros de corrupção de dados físicos como erro 8909 e os incluem problemas de subsistema de entrada/saída.

REFERÊNCIAS

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