Reduzir o banco de dados tempdb

  • Artigo

Aplica-se a: SQL Server Instância Gerenciada de SQL do Azure

Este artigo discute vários métodos que você pode usar para reduzir o banco de dados tempdb no SQL Server.

Você pode usar qualquer um dos seguintes métodos para alterar o tamanho do tempdb. As três primeiras opções são descritas neste artigo. Se você quiser usar o SQL Server Management Studio (SSMS), siga as instruções em Reduzir um banco de dados.

Método Exige reinicialização? Mais informações
ALTER DATABASE Sim Fornece controle total sobre o tamanho dos arquivos padrão tempdb (tempdev e templog).
DBCC SHRINKDATABASE Não Opera em nível de banco de dados.
DBCC SHRINKFILE Não Permite reduzir arquivos individuais.
SQL Server Management Studio Não Reduzir arquivos de banco de dados por meio de uma interface gráfica do usuário.

Comentários

Por padrão, o banco de dados tempdb é configurado para crescer automaticamente conforme necessário. Portanto, esse banco de dados pode crescer inesperadamente com o tempo para um tamanho maior do que o tamanho desejado. Tamanhos de banco de dados maiores tempdb não afetarão negativamente o desempenho do SQL Server.

Quando o SQL Server é iniciado, tempdb é recriado usando uma cópia do banco de dados model e tempdb é redefinido para seu último tamanho configurado. O tamanho configurado é o último tamanho explícito que foi definido usando uma operação de alteração de tamanho do arquivo, como ALTER DATABASE que usa a opção MODIFY FILE ou as instruções DBCC SHRINKFILE ou DBCC SHRINKDATABASE. Portanto, a menos que você precise usar valores diferentes ou obter resolução imediata para um banco de dados grande tempdb, você pode aguardar a próxima reinicialização do serviço SQL Server para que o tamanho diminua.

Você pode diminuir tempdb enquanto a atividade tempdb está em andamento. No entanto, você pode encontrar outros erros, como bloqueio, deadlocks e assim por diante, que podem impedir a conclusão de redução. Portanto, para garantir que uma redução de tempdb com êxito, recomendamos que você faça isso enquanto o servidor estiver no modo de usuário único ou quando você tiver interrompido toda a atividade tempdb.

O SQL Server registra apenas informações suficientes no log de transações tempdb para reverter uma transação, mas não para refazer transações durante a recuperação do banco de dados. Esse recurso aumenta o desempenho das instruções INSERT no tempdb. Além disso, você não precisa registrar informações para refazer nenhuma transação, pois tempdb é recriado sempre que você reiniciar o SQL Server. Portanto, ele não tem transações para efetuar roll forward ou para reverter.

Para obter mais informações sobre gerenciamento e monitoramento tempdb, veja planejamento da capacidade e Monitorar o uso tempdb.

Use o comando ALTER DATABASE

Observação

Esse comando opera somente nos arquivos lógicos tempdb padrão tempdev e templog. Se mais arquivos forem adicionados ao tempdb, você poderá reduzi-los depois de reiniciar o SQL Server como um serviço. Todos os arquivos tempdb 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.

Esse método requer que você reinicie o SQL Server.

  1. Interrompa o SQL Server.

  2. Em um prompt de comando, inicie a instância no modo de configuração mínima. Para fazer isso, siga estas etapas:

    1. Em um prompt de comando, altere para a pasta onde o SQL Server está instalado (substitua <VersionNumber> e <InstanceName> no exemplo a seguir):

      cd C:\Program Files\Microsoft SQL Server\MSSQL<VersionNumber>.<InstanceName>\MSSQL\Binn

    2. Se a instância for uma instância nomeada do SQL Server, execute o seguinte comando (substitua <InstanceName> no exemplo a seguir):

      sqlservr.exe -s <InstanceName> -c -f -mSQLCMD

    3. Se a instância for a instância padrão do SQL Server, execute o seguinte comando:

      sqlservr -c -f -mSQLCMD

      Observação

      Os parâmetros -c e -f fazem com que o SQL Server seja iniciado em um modo de configuração mínimo que tenha um tempdb tamanho de 1 MB para o arquivo de dados e 0,5 MB para o arquivo de log. O parâmetro -mSQLCMD impede que qualquer outro aplicativo que não seja sqlcmd assuma a conexão de usuário único.

  3. Conecte-se o SQL Server ao sqlcmd e execute os seguintes comandos Transact-SQL. Substitua <target_size_in_MB> pelo tamanho desejado:

    ALTER DATABASE tempdb MODIFY FILE
(NAME = 'tempdev', SIZE = <target_size_in_MB>);

ALTER DATABASE tempdb MODIFY FILE
(NAME = 'templog', SIZE = <target_size_in_MB>);

  4. Interrompa o SQL Server. Para fazer isso, 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.

Usar o comando DBCC SHRINKDATABASE

DBCC SHRINKDATABASE recebe o parâmetro target_percent. É a porcentagem de espaço livre que você deseja deixar no arquivo de banco de dados após a redução do banco de dados. Se você usar DBCC SHRINKDATABASE, talvez seja necessário reiniciar o SQL Server.

  1. Determine o espaço que é usado atualmente no tempdb usando o procedimento armazenado sp_spaceused. Em seguida, calcule a porcentagem de espaço livre que é deixada para uso como um parâmetro para DBCC SHRINKDATABASE. Esse cálculo é baseado no tamanho desejado do banco de dados.

    Observação

    Em alguns casos, talvez seja necessário executar sp_spaceused @updateusage = true para recalcular o espaço usado e obter um relatório atualizado. Para obter mais informações, consulte sp_spaceused.

    Considere o seguinte exemplo:

    Suponha que tempdb tenha dois arquivos: o arquivo de dados primário (tempdb.mdf) que é 1.024 MB e o arquivo de log (tempdb.ldf) que é 360 MB. Suponha que relata que sp_spaceused o arquivo de dados primário contém 600 MB de dados. Além disso, suponha que você deseja reduzir o arquivo de dados primário para 800 MB. Calcule a porcentagem desejada de espaço livre restante após a redução: 800 MB - 600 MB = 200 MB. Agora, divida 200 MB por 800 MB = 25 por cento, e esse é o seu target_percent. O arquivo de log de transações é reduzido de acordo, deixando 25% ou 200 MB de espaço livre depois que o banco de dados é reduzido.

  2. Conecte-se ao SQL Server com o SSMS, o Azure Data Studio ou o sqlcmd e execute o seguinte comando Transact-SQL. Substitua <target_percent> pela porcentagem desejada:

    DBCC SHRINKDATABASE (tempdb, '<target_percent>');

Há limitações com o comando DBCC SHRINKDATABASE em tempdb. O tamanho de destino para arquivos de dados e de log não pode ser menor do que o tamanho especificado quando o banco de dados foi criado ou menor do que o último tamanho que foi explicitamente definido usando uma operação de alteração de tamanho do arquivo, como ALTER DATABASE que usa a opção MODIFY FILE. Outra limitação é o cálculo DBCC SHRINKDATABASE do parâmetro target_percentage e sua dependência do espaço atual utilizado.

Usar o comando DBCC SHRINKFILE

Use o comando DBCC SHRINKFILE para reduzir os arquivos individuais tempdb. DBCC SHRINKFILE fornece mais flexibilidade do que DBCC SHRINKDATABASE porque você pode usá-lo em um único arquivo de banco de dados sem afetar outros arquivos que pertencem ao mesmo banco de dados. DBCC SHRINKFILE recebe o parâmetro target_size. Este é o tamanho final desejado para o arquivo de banco de dados.

  1. Determine o tamanho desejado para o arquivo de dados primário (tempdb.mdf), o arquivo de log (templog.ldf) e os arquivos adicionais adicionados ao tempdb. Verifique se o espaço usado nos arquivos é menor ou igual ao tamanho de destino desejado.

  2. Conecte-se ao SQL Server com o SSMS, o Azure Data Studio ou o sqlcmd e execute os seguintes comandos Transact-SQL para os arquivos de banco de dados específicos que você deseja reduzir. Substitua <target_size_in_MB> pelo tamanho desejado:

    USE tempdb;
GO

-- This command shrinks the primary data file
DBCC SHRINKFILE (tempdev, '<target_size_in_MB>');
GO

-- This command shrinks the log file, examine the last paragraph.
DBCC SHRINKFILE (templog, '<target_size_in_MB>');
GO

Uma vantagem é DBCC SHRINKFILE que ele pode reduzir o tamanho de um arquivo para um tamanho menor do que seu tamanho original. Você pode emitir DBCC SHRINKFILE em qualquer um dos arquivos de dados ou de log. Não é possível tornar o banco de dados menor do que o tamanho do banco de dados model.

Erro 8909 ao executar operações de redução

Se tempdb estiver sendo usado e se você tentar reduzi-lo usando os comandos DBCC SHRINKDATABASE DBCC SHRINKFILE, você poderá receber mensagens semelhantes à seguinte, dependendo da versão do SQL Server que você está usando:

Server: Msg 8909, Level 16, State 1, Line 1 Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (6:8040) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).

esse erro não indica nenhuma corrupção real no tempdb. No entanto, pode haver outras razões para erros de dados corrompidos físicos, como o erro 8909, e que esses motivos incluem problemas de subsistema de E/S. Portanto, se o erro acontecer fora das operações de redução, você deve fazer mais investigação.

Embora uma mensagem 8909 seja retornada ao aplicativo ou ao usuário que está executando a operação de redução, as operações de redução não falharão.

Conteúdo relacionado