Está publicado no Git da Data Tuning, os scripts do nosso segundo Meetup que foi realizado no último dia 27/06/2019. Fiquem a vontade para tirar dúvidas sobre a reprodução dos problemas discutidos no Meetup.
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”.
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.