Recentemente ao realizar o acerto dos arquivos do tempdb em instâncias do SQL Server 2012 e superiores, me deparei com uma situação que ainda não tinha visto nas versões anteriores ao SQL Server 2012.

Vamos a simulação do problema!

Ao executar o comando abaixo:

ALTER DATABASE tempdb REMOVE FILE temp03

O erro abaixo é apresentado:

Msg 5042, Level 16, State 1, Line 2
The file ‘temp03’ cannot be removed because it is not empty.

Com a mensagem acima, executei o comando abaixo:

USE tempdb
GO
DBCC SHRINKFILE(temp03,EMPTYFILE)

DBCC SHRINKFILE: Page 3:32 could not be moved because it is a work table page.

Msg 2555, Level 16, State 1, Line 4
Cannot move all contents of file “temp03” to other places to complete the emptyfile operation.

E agora? O SQL Server informa que não é possível remover o arquivo do tempdb porque o mesmo não está vazio e ao tentar realizar o shrink o SQL informa que não consegue mover todo o conteúdo para outro arquivo.
Confesso que a primeira vez que vi o erro fiquei confuso!

Abaixo, eu mostro como está a distribuição dos arquivos do tempdb da instância.

Trace Flag 3608

Para a simulação desse problema, estou utilizando o SQL Server 2017.
Uma das formas que eu encontrei para contornar esse comportamento é utilizando a Trace Flag 3608.
A TF 3608 faz com que não ocorra o start e recovery de nenhuma base de dados, exceto a master. Nessa situação as bases de dados só passarão pelo start e recovery ao serem acessadas e com isso é possível executar o comando de exclusão dos arquivos do tempb sem receber o erro “The file xxx cannot be removed because it is not empty”.

https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql?view=sql-server-2017

Habilitando a Trace Flag 3608

A primeira coisa a fazer é acessar o SQL Server Configuration Manager, ir nas propriedades do serviço do SQL Server e selecionar a aba Startup Parameter.

No campo Specify a startup parameter adicione o parâmetro -T3608 e clique no botão Add.

Feito isso, basta realizar stop/start do serviço do SQL Server e com o SQL Server reiniciado, podemos observar no ERRORLOG que a engine fez o recovery apenas na base de dados master.

Com isso podemos observar que ao listar os arquivos do tempdb novamente, apenas os arquivos padrão de quando o SQL Server foi instalado será listado.

Porém se listarmos os arquivos do tempdb pela sys.master_files iremos observar que os demais arquivos estão configurados, porém como o tempdb não foi realizado o recovery eles não estão em uso.

Feito isso agora iremos conseguir realizar a exclusão dos arquivos do tempdb com sucesso.

Agora iremos remover a TF 3608 e dar stop/start no serviço do SQL Server para verificarmos se a remoção do arquivo temp03 de fato ocorreu e analisar que sem a TF 3608 todas as bases são startadas e o recovery é executado após o restart do serviço.

tempdb files

ERRORLOG

ERRORLOG após remoção da TF 3608.

Bom pessoal, espero que isso possa ajudá-los no dia a dia. Remover arquivos do tempdb não é uma tarefa que se realiza com frequência nos ambientes, mas é importante sempre checar essas configurações para que o tempdb não esteja configurado de forma errada.
Até a próxima.