Introdução

Que o TEMPDB é um dos bancos de sistema mais importantes do SQL Server todo mundo sabe, mas e mudar a localização dos arquivos quando eles são criados acidentalmente no C:\ do servidor? Vamos aprender agora como fazer tal
procedimento.

Diferentemente dos bancos de usuários, o TEMPDB não permite que seja feito um DETACH/ATTACH para que os arquivos sejam movidos outro disco. É preciso executar alguns simples comandos para que isso seja feito de maneira segura e prática. Os requisitos para realizar essa tarefa são:

• Ter uma outra unidade de disco disponível, de preferência com blocksize de 64kb
• Permissões apropriadas para executar o script da mudança do local dos arquivos
• Reiniciar o serviço do SQL Server para que a alteração tenha efeito

Mãos a obra

Vamos executar o script abaixo para trazer a informação de onde está localizado os arquivos do TEMPDB

[code lang=”sql”]
Use [master]
GO
SELECT
name AS [LogicalName]
,physical_name AS [Location]
,state_desc AS [Status]
FROM sys.master_files
WHERE database_id = 2;
GO
[/code]

Vejamos que o arquivo tempdb.mdf e templog.ldf estão no C:\ do servidor. Os demais arquivos já se encontram em outro disco apropriado numa pasta genérica.

Iremos criar a pasta E:\SQL\TEMPDB e mover todos os arquivos de DADOS para esse local e o arquivo de LOG do TEMPDB para a pasta de LOGs do servidor:

Com a pasta TEMPDB criada, o próximo passo é alterar a localização dos 2 arquivos mencionados:

[code lang=”sql”]
USE master;
GO
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = ‘E:\SQL\TEMPDB\tempdb.mdf’);
GO
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev1, FILENAME = ‘E:\SQL\TEMPDB\tempdb1.ndf’);
GO
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev2, FILENAME = ‘E:\SQL\TEMPDB\tempdb2.ndf’);
GO
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev3, FILENAME = ‘E:\SQL\TEMPDB\tempdb3.ndf’);
GO
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev4, FILENAME = ‘E:\SQL\TEMPDB\tempdb4.ndf’);
GO
ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = ‘E:\SQL\LOG\templog.ldf’);
GO
[/code]

O resultado da query acima é mostrado abaixo, e uma mensagem informativa dizendo que o novo caminho será utilizado no próximo restart do SQL Server

Ao checar os arquivos nas propriedades do TEMPDB, veja que a configuração já está setada, aguardando somente o restart do serviço para mover os arquivos para outro local:

Fazer as alterações entrarem em vigor

Vamos reiniciar o serviço do SQL Server para que os arquivos sejam recriados no novo local:

1º – Clicar com o botão direito no serviço do SQL Server e em seguida clicar em Restart:

2º – Aguardar a transição entre STOPPING e START

3º – Continuar aguardando a transição entre STOPPING e START

4º – Depois de iniciado o serviço, veja que os arquivos de dados foram criados no novo local: E:\SQL\TEMPDB

5º – O arquivo de LOG foram criados no caminho: E:\SQL\LOG

Feito isso, basta acessar o diretório onde continham os arquivos antigos do TEMPDB e fazer a deleção dos arquivos obsoletos:

1º – Removendo os arquivos de dados obsoletos

2º – Removendo o arquivo de log e um arquivo .mdf obsoleto do tempdb:

TraceFlags e TEMPDB

Uma coisa que sempre configuro no TEMPDB são as traceflags 1117 e 1118. Elas alteram o comportamento do TEMPDB nas versões mais antigas e são fundamentais no desempenho desse banco. Vale lembrar que da versão 2016 em diante esses traceflags não possuem efeito e são controlados por outras opções de alter database.

Para checar se os traceflags estão ativos na sua instância, digite o comando abaixo:

[code lang=”sql”]
DBCC TRACESTATUS
[/code]

Caso não estejam habilitados, basta digitar os comandos abaixo. A configuração entra em vigor no momento da alteração, porém essa configuração não é persistida em caso de restart da instância.

[code lang=”sql”]
dbcc traceon (1117, -1);
dbcc traceon (1118, -1);
[/code]

Para garantir que esses traceflags sejam mantidos mesmo em caso de restart do servidor, basta incluí-los na aba STARTUP PARAMETERS do serviço do SQL Server, conforme imagens abaixo:

Feito isso, basta reiniciar o serviço para que as configurações entrem em vigor.