Partilhar via

Reduzir o banco de dados tempdb

Aplica-se a:SQL ServerAzure SQL Managed Instance

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

Pode usar qualquer um dos seguintes métodos para alterar o tamanho de 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 Requer reinicialização? Mais informações
ALTER DATABASE Sim Dá controle total sobre o tamanho dos arquivos de tempdb padrão (tempdev e templog).
DBCC SHRINKDATABASE Não Opera no nível do banco de dados.
DBCC SHRINKFILE Não Permite reduzir ficheiros individuais.
SQL Server Management Studio Não Reduza os arquivos de banco de dados através de uma interface gráfica do usuário.

Comentários

Por padrão, o banco de dados tempdb é configurado para crescimento automático conforme necessário. Portanto, esta base de dados pode crescer inesperadamente ao longo do tempo até um tamanho superior ao desejado. Tamanhos maiores de banco de dados tempdb não afetam negativamente o desempenho do SQL Server.

Quando o SQL Server inicia, tempdb é recriado usando uma cópia do banco de dados model, e tempdb é reajustado para o seu último tamanho configurado. O tamanho configurado é o último tamanho explícito que definiste através de uma operação de alteração de tamanho de ficheiro, como ALTER DATABASE com a opção MODIFY FILE, ou as instruções DBCC SHRINKFILE ou DBCC SHRINKDATABASE. Portanto, a menos que precise de usar valores diferentes ou queira resolver imediatamente uma grande tempdb base de dados, pode esperar pelo próximo reinício do serviço SQL Server para que o tamanho diminua.

Você pode reduzir tempdb enquanto a atividade de tempdb está em andamento. No entanto, poderá encontrar outros erros, como bloqueios, deadlocks e assim por diante, que podem impedir a conclusão do processo de diminuição. Para garantir que a redução de tempdb seja bem-sucedida, realize esta operação enquanto o servidor está em modo de utilizador exclusivo, ou quando parar toda a atividade de tempdb.

O SQL Server registra apenas informações suficientes no log de transações do tempdb para reverter uma transação, mas não para refazer transações durante a recuperação do banco de dados. Este recurso aumenta o desempenho nas instruções de INSERT em tempdb. Além disso, você não precisa registrar informações para refazer nenhuma transação porque tempdb é recriada toda vez que você reinicia o SQL Server. Portanto, ele não tem transações para avançar ou para reverter.

Para obter mais informações sobre como gerir e monitorizar tempdb, consulte Capacity planning e Monitorizar o uso de tempdb.

Use o comando ALTER DATABASE

Observação

Este comando funciona apenas nos ficheiros tempdb lógicos predefinidos tempdev e templog. Se adicionares mais ficheiros a tempdb, podes reduzi-los depois de reiniciares o SQL Server como serviço. Todos os arquivos tempdb são recriados durante a inicialização. No entanto, esses arquivos estão vazios e podem ser removidos. Para remover ficheiros extra em tempdb, use o ALTER DATABASE comando com a REMOVE FILE opção.

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

Observação

Você pode se conectar a uma instância do SQL Server usando qualquer ferramenta de cliente familiar do SQL Server, como sqlcmd, SQL Server Management Studio (SSMS) ou a extensão MSSQL para Visual Studio Code.

  1. Pare o SQL Server.

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

    1. Em um prompt de comando, mude 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 inicie em um modo de configuração mínimo que tenha um tamanho tempdb 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 além de sqlcmd assuma a conexão de usuário único.

  3. Conecte-se ao SQL Server com sqlcmd e execute os seguintes comandos Transact-SQL. Substitui <target_size_in_MB> pelo tamanho que queres:

    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. Pare o SQL Server. Para isso, pressione Ctrl+C na janela do prompt de comandos, reinicie o SQL Server como um serviço e depois verifique o tamanho dos ficheiros tempdb.mdf e templog.ldf.

Utilize o comando DBCC SHRINKDATABASE

DBCC SHRINKDATABASE Toma o target_percent parâmetro. Este parâmetro define a percentagem de espaço livre que pretende deixar no ficheiro da base de dados após encolher a base de dados. Se usares DBCC SHRINKDATABASE, talvez precises de reiniciar o SQL Server.

  1. Use o sp_spaceused procedimento armazenado para verificar o espaço atualmente utilizado por tempdb. Depois, calcula a percentagem de espaço livre a usar como parâmetro para DBCC SHRINKDATABASE. Esse cálculo é baseado no tamanho desejado do banco de dados.

    Observação

    Em alguns casos, pode ser necessário correr sp_spaceused @updateusage = true para recalcular o espaço utilizado e obter um relatório atualizado. Para obter mais informações, consulte sp_spaceused.

    Considere o seguinte exemplo:

    Suponha que tempdb tem 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 sp_spaceused informa que 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 percentagem desejada de espaço livre deixado após a redução: 800 MB - 600 MB = 200 MB. Agora, divida 200 MB por 800 MB = 25 por cento, e esse valor é 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. Executa o seguinte comando Transact-SQL. Substitua <target_percent> pela percentagem pretendida:

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

O DBCC SHRINKDATABASE comando tem limitações quando usado em tempdb. Não podes definir o tamanho alvo dos dados e ficheiros de registo para ser menor do que o especificado quando a base de dados foi criada. Também não pode defini-lo mais pequeno do que o último tamanho que definiu explicitamente através de uma operação de alteração de tamanho do ficheiro, como por exemplo ALTER DATABASE, utilizando a opção MODIFY FILE. Outra limitação de DBCC SHRINKDATABASE é o cálculo do target_percentage parâmetro e a sua dependência do espaço atual que é utilizado.

Utilize o comando DBCC SHRINKFILE

Use o DBCC SHRINKFILE comando para reduzir ficheiros 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 Toma o target_size parâmetro. Este parâmetro define o tamanho final desejado para o ficheiro da base 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 extras que são adicionados a tempdb. Certifique-se de que o espaço utilizado nos ficheiros é inferior ou igual ao tamanho alvo desejado.

  2. Liga-te ao SQL Server com SSMS, Visual Studio Code ou sqlcmd. Depois executa os seguintes comandos Transact-SQL para os ficheiros específicos da base de dados que queres 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 de DBCC SHRINKFILE é que ele pode reduzir o tamanho de um ficheiro para menos do que o seu tamanho original. Pode executar DBCC SHRINKFILE em qualquer um dos ficheiros de dados ou ficheiros de registo. 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 em uso e tentar diminuí-lo usando os comandos DBCC SHRINKDATABASE ou DBCC SHRINKFILE, pode receber mensagens semelhantes à saída seguinte. A mensagem exata depende da versão do SQL Server que estás a usar:

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).

Este erro não indica qualquer corrupção real no tempdb. No entanto, pode haver outras razões para erros de corrupção de dados 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 investigar mais.

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

Conteúdo relacionado

  • Last updated on