Posts com Tag ‘error5042’

Recentemente ao realizar o acerto dos datafiles do tempdb em meus ambientes 2012+ me deparei com uma situação de erro onde eu não me deparo quando realizo a mesma tarefa em meus ambientes 2008/R2.

Ao executar o comando abaixo:


alter database tempdb remove file temp03

Retornou o seguinte erro:

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

Como as mensagens de erro no SQL Server são claras, executei:


USE tempdb
GO
DBCC SHRINKFILE(temp03,EMPTYFILE)

E então me retornou o erro abaixo:

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.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Pesquisando, eu encontrei algumas formas para contornar esse problema da remoção dos datafiles do tempdb.

Primeiramente, vamos analisar como está feita a distribuição dos meus arquivos do tempdb e vamos a uma das soluções.

Arquivos do tempdb

Atualmente meu tempdb está com três arquivos de dados e eu quero deixá-lo com dois arquivos de dados e um de log.

tempdb

Solução: Habilitando Trace Flag 3608

Para esse post eu estou usando SQL Server 2017 Developer Edition.

Uma das possíveis soluções para contornar esse comportamento é iniciar a instância com a TF 3608.

A TF 3608 faz com que não ocorra o start e recovery de nenhuma base 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”.

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

Obs: importante realizar essa operação com em ambiente controlado sem nenhum acesso além do seu, pois se alguma sessão usar o tempdb o recovery será executado e não será possível realizar a exclusão do arquivo.

Habilitando a TF 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.

SQLServerConfigurationManagerStartupParameter

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

3608

Feito isso, basta dar stop/start no serviço do SQL Server.

Com o SQL Server iniciado, podemos observar que a engine fez o recovery apenas na base de dados master.

errorlog3608

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.

tempdb3608

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.

sysmasterfile3608

Feito os passos acima agora conseguimos realizar a exclusão dos arquivos do tempdb que queremos.

tempdb_removefile

Agora basta retirarmos a TD 3608 e dar stop/start no serviço do SQL Server para comprovarmos a remoção do arquivo temp03 de fato e ver que sem a TF 3608 todas as bases são startadas e o recovery é feito.

tempdb files

tempdb2

errorlog

errorlog_sem3608

Bom pessoal, espero que isso possa ajudá-los no dia a dia. Logo mais postarei uma segunda forma de contornar esse comportamento que começou no SQL Server 2012 e até onde eu pesquisei não encontrei o motivo dessa mudança de comportamento.