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.
