Você está offline; aguardando reconexão

Como reduzir o banco de dados tempdb no SQL Server

Extended support for SQL Server 2005 ends on April 12, 2016

If you are still running SQL Server 2005 after April 12, 2016, you will no longer receive security updates and technical support. We recommend upgrading to SQL Server 2014 and Azure SQL Database to achieve breakthrough performance, maintain security and compliance, and optimize your data platform infrastructure. Learn more about the options for upgrading from SQL Server 2005 to a supported version here.

Sumário
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 oferece total controle do tamanho dos arquivos tempdb. No entanto, o primeiro método exige a reinicialização do Microsoft SQL Server. O segundo método é reduzido ao banco de dados tempdb e você poderá precisar reiniciar o SQL Server. O terceiro método permite você reduzir os arquivos individuais no banco de dados tempdb. Os dois últimos métodos exigem que nenhuma atividade possa ocorrer 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. Além disso, observe que o SQL Server Management Studio no SQL Server 2005 não exibe o tamanho correto dos arquivos tempdb depois da operação de redução. O valor "Espaço alocado atualmente" é sempre obtido 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 declaração no SQL Server Management Studio:
use tempdbselecione (tamanho*8) como FileSizeKB do sys.database_files

Observação O SQL Server 2008 não é afetado pelo problema de que o tamanho incorreto dos arquivos tempdb é exibido após uma operação de redução. 

Informações sobre o Tempdb

O tempdb é um espaço de trabalho temporário. Entre outros usos, o SQL Server usa o tempdb para realizar o seguinte:
  • 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, você não tem os informações do log para refazer qualquer transação porque o tempdb é recriado toda vez que você inicia o SQL Server. No entanto, ele não tem transações para rolar para frente ou para reverter. Quando o SQL Server inicia, o tempdb é recriado usando uma cópia do banco de dados modelo e o tempdb é reiniciado para seu último tamanho configurado.

Por padrão, o banco de dados tempdb é configurado para o crescimento automático conforme necessário. Portanto, esse banco de dados pode aumentar com o tempo para um tamanho maior do que o tamanho 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 usando uma operação de mudança de tamanho do arquivo como ALTER DATABASE que usa opção MODIFY FILE ou a instrução DBCC SHRINKFILE.

Você pode usar os seguintes três métodos para reduzir o tempdb para um tamanho que é menor do que o tamanho configurado.

Método 1: Use os comandos Transact-SQL
Observação Este método exige a reinicialização do SQL Server.

  1. Pare o SQL Server. Em um prompt de comando, digite o seguinte comando para iniciar o SQL Server:
    sqlservr -c -f
    Os parâmetros -c e -f fazem com que o SQL Server inicie em um modo de configuração mínima que tem um 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 o SQL Server usando 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. Para fazer isto, pressione Ctrl+C na janela do 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 padrões: tempdev e templog. Se mais arquivos são adicionados ao tempdb, você pode reduzi-los depois de reiniciar o SQL Server como um serviço. Todos os arquivostempdb são recriados durante a inicialização. No entanto, 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: Use o comando DBCC SHRINKDATABASE
Use o comando DBCC SHRINKDATABASE para reduzir o banco de dados tempdb. O 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ê 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 de execução do DBCC SHRINKDATABASE ou DBCCSHRINKFILE enquanto tempdb está em uso".
  1. Determine o espaço que é usado atualmente no tempdb usando o procedimento armazenado sp_spaceused. Calcule a porcentagem de espaço livre deixada para ser usada como um parâmetro para DBCC SHRINKDATABASE. Esse cálculo é baseado no tamanho do banco de dados desejado.

    Observação Em alguns casos, você pode precisar 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 o seguinte exemplo:
    Assuma que o tempdb tenha dois arquivos: o arquivo de dados primário (Tempdb.mdf) que é 100 MB e o arquivo de log (Tempdb.ldf) que é 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 de espaço livre deixado após a redução: 80 MB - 60 MB = 20 MB. Agora, divida 20 MB por 80 MB = 25 porcento 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 usando o Analisador de Consulta e execute os seguintes comandos Transact-SQL:
       dbcc shrinkdatabase (tempdb, 'target percent')    -- Este comando reduz o banco de dados tempdb					
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 menos do que o último tamanho definido explicitamente usando uma operação de mudança de tamanho de arquivo, como o ALTER DATABASE que usa 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: Use o comando DBCC SHRINKFILE
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 e este é 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 o DBCC SHRINKFILE, consulte a seção "Efeitos de execução do DBCC SHRINKDATABASE ou DBCCSHRINKFILE enquanto tempdb está em uso".
  1. Determine o tamanho desejado para o arquivo de dados primário (tempdb.mdf), o arquivo de log (templog.ldf) e 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 usando 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, verifique o ú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. Por padrão, o tempdb tem a opção truncar log no chkpt definida para ATIVADA. Portanto, não é necessário emitir um truncamento de 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 abaixo para ler o artigo na Base de Dados de Conhecimento Microsoft:
256650 INF: Como reduzir o log de transação do SQL Server 7.0

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, é possível receber vários erros de consistência similares ao tipo seguinte e a operação de redução pode falhar:
  • Servidor: Mensagem 2501, Nível 16, Estado 1, Linha 1 não pode encontrar a tabela nomeada '1525580473'. Verificar sysobjects.
  • Servidor: Mensagem 8909, Nível 16, Estado 1, Linha 0 Tabela Corrompida: Objeto ID 1, índice ID 0, página ID %S_PGID. A PageId no cabeçalho da página = %S_PGID.
Apesar do erro 2501 poder não indicar qualquer corrupção no tempdb, este erro 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, esteja ciente de 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"

Propriedades

ID do Artigo: 307487 - Última Revisão: 01/08/2014 17:07:00 - Revisão: 6.0

Microsoft SQL Server 2000 Standard Edition, Microsoft SQL Server 7.0 Standard Edition, Microsoft SQL Server 2000 64-bit Edition, Microsoft SQL Server 2005 Standard Edition, Microsoft SQL Server 2005 Developer Edition, Microsoft SQL Server 2005 Enterprise Edition, Microsoft SQL Server 2005 Workgroup Edition

  • kbsqlsetup kbhowtomaster KB307487
Comentários
=">