tempdb – Error 5042 The file cannot be removed because it is not empty

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.

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.

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

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.

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 os passos acima agora conseguimos realizar a exclusão dos arquivos do tempdb que queremos.

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

errorlog

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.

SQL Server 2016 – Configuração do Tempdb

Olá pessoal,

Nesta semana eu precisei instalar o SQL Server 2016 CTP 3.2 em uma VM para testar uma nova funcionalidade e durante a instalação identifiquei um novo recurso que eu confesso que muito me agradou, que é a possibilidade de configuração da quantidade de arquivos do tempdb e seus respectivos caminhos, tamanhos, crescimento e log. Essa mudança vem desde o CTP 2.4 e estou na torcida para que essa funcionalidade perdure na versão final.

Ao chegar na tela de Database Engine Configuration temos a aba TempDB, como podemos ver na imagem abaixo:

Acima notamos que para o data file temos por default:

• 1 arquivo de dados que terá o nome de tempdb.mdf;
• Tamanho inicial de 8 MB;
• Autogrowth de 64 MB;
• Criação do data file no caminho default especificado anteriormente.

Para o log file temos por default:

• Tamanho inicial de 8 MB;
• Autogrowth de 64 MB;
• Criação do log file no caminho default especificado anteriormente.

Como para mim foi uma novidade essa tela de configuração eu aproveitei para testá-la e deixei a minha da seguinte maneira:

Para os data files eu deixei:

• 2 data files que segundo o instalador os nomes seriam tempdb.mdf e tempdb_mssql_#.ndf, onde normalmente o # é a representação de um número;
• Ambos com tamanho iniciais e crescimento de 100 MB.
• No Data directories eu percebi que era possível colocar mais de um caminho, então eu coloquei “C:\MSSQL\Data\tempdb#01 e C:\MSSQL\Data\tempdb#02” para ver se seria criado um data file em cada diretório, no intuito de simular discos diferentes em um ambiente de produção;

Para o log file eu deixei:

• Tamanho inicial e crescimento de 100 MB;
• Log directory em “C:\MSSQL\Log”.

Confesso que não houve nenhum uso de boas práticas no tamanho e crescimento dos data files e log do tempdb e muito menos para o caminho dos arquivos. Como dito isso foi um teste em um VM pessoal.

Bom com a instalação do SQL Server 2016 CTP 3.2 concluída eu fui ver o resultado e ficou da seguinte maneira:

O instalador criou os dois data files nos diretórios que eu especifiquei e com os tamanhos especificados. A única coisa que eu achei que fosse diferente seria o nome do segundo data file, eu achei que seria “tempdb_mssql_1.ndf”, mas o SQL Server criou como “tempdb_mssql_2.ndf”, mas isso não faz a menor diferença.

Bom pessoal é isso aí que eu queria compartilhar com vocês. Para uma nova instalação de SQL Server 2016 até o momento(CTP 3.2) é possível fazer algumas mudanças no tempdb e com isso evitar/diminuir as famosas contenções de tempdb, caso você já saiba que isso ocorra em outros ambientes que você administra.

Um abraço e até a próxima!

Dica: DELETE com WHERE “traiçoeiro”

Olá pessoal,

Recentemente passei por uma situação um tanto inusitada no trabalho. Fui acionado para analisar um DELETE com cláusula WHERE em uma subquery que deveria ter dado erro de sintaxe pelo fato do campo utilizado na subquery não fazer parte da tabela da mesma. O SQL Server só não deu erro como quase deletou todos os registros da tabela, e isso só não ocorreu pois o DBA percebeu a demora e cancelou a operação. Ficou confuso? Vamos ao exemplo.

USE tempdb
GO
create table tb01(cod_tb01 int)
create table tb02(cod_tb02 int)
GO
insert into tb01 values(1),(2),(3),(4),(5)
insert into tb02 values(10),(20),(30),(40),(50)
GO
delete from tb01 where cod_tb01 in(select cod_tb01 from tb02)
GO

O DBA que me acionou me falou: O DELETE acima deletou todos os registros da tabela tb01, mas deveria ter dado erro de sintaxe pois a coluna cod_tb01 não faz parte da tabela tb02 e mesmo se fizesse nenhum registro da tb01 existe na tb02. Quando eu executo apenas a subquery o SQL Server dá erro dizendo que a coluna cod_tb01 não faz parte da tabela tb02, mas quando eu executo o statement todo o SQL Server não dá erro de sintaxe e apaga todos os registros da tb01. Por que?

Pois bem, analisando rapidamente e executando apenas a subquery, realmente o campo cod_tb01 não pertence a tabela tb02 e deveria ter dado erro de sintaxe, mas nesse caso acima o SQL Server não reconheceu como erro pois o SELECT da subquery não tem uma relação direta com a query de fora que é o DELETE e o desenvolvedor não usou um alias de tabela para forçar de qual tabela era o campo cod_tb01, ou seja, as queries são independentes para o SQL Server e para o analisador sintático do SQL Server o usuário estava querendo usar propositalmente o campo da tabela tb01 na subquery. Para o SQL Server o DELETE acima teria o mesmo efeito que o DELETE abaixo:

DELETE FROM tb01 WHERE 1 in(SELECT 1 from tb02)

Se o desenvolvedor tivesse escrito o DELETE da forma abaixo teria dado erro de sintaxe, pois da forma abaixo o desenvolvedor está dizendo de qual tabela pertence o campo cod_tb01 e o analisador sintático informaria que não existe o campo cod_tb01 na tabela tb02.

delete from tb01 where cod_tb01 in(select t2.cod_tb01 from tb02 t2)

Analisando o plano de execução do DELETE podemos ver claramente como o SQL Server interpretou a condição WHERE que acabou deletando todos os registros da tabela tb01.

Acima podemos ver que o predicado de busca que o SQL Server usou para fazer o WHERE foi:

tb01.cod_01 = tb01.cod_01

Observação:

Esse comportamento não é exclusivo do SQL Server. Juntamente com outro DBA Oracle, executamos o script acima da demonstração e o mesmo comportamento ocorreu no Oracle. Eu acredito que esse comportamento ocorrerá em qualquer banco de dados onde é possível usar subquery, mas não fiz testes em outros SGBDs.

Bom pessoal o que eu quero passar com esse post é:

Tomem cuidado com subqueries. Felizmente nesse caso o DBA cancelou a operação a tempo e os dados não foram apagados. Eu sempre fui contra usar subquery devido a baixa performance que ela tem comparado ao uso de JOIN. A partir de agora sou mais contra ainda o uso de subquery.

Dica: Em muitos casos subqueries são facilmente substituídas pelo uso de JOIN.

Um bom final de ano a todos!

The SQL Server (INSTANCENAME) service terminated with service-specific error The requested address is not valid in its context

Olá pessoal,

Hoje eu recebi um servidor que foi clonado de outro server que já tinha o SQL Server instalado e o serviço do SQL Server não iniciava.

Pelo Event Viewer em Windows Log\System a mensagem era a seguinte:

The SQL Server (INSTANCENAME) service terminated with service-specific error The requested address is not valid in its context..

Pesquisando na web eu não encontrei muita coisa sobre esse erro acima, então fui investigar mais no Event Viewer e nos eventos de Windows Log\Application e encontrei a seguinte log:

Could not start the network library because of an internal error in the network library.

SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

TDSSNIClient initialization failed with error 0x2741, status code 0x1. Reason: Initialization failed with an infrastructure error. Check for previous errors. The requested address is not valid in its context.

Server failed to listen on XXX.XXX.XXX.XXX <ipv4> XXXX

Opa… XXX.XXX.XXX.XXX era um IP…hummm as coisas começaram a ficar mais claro e fui no SQL Server Configuration Manager olhar o SQL Server Network Configuration em TCP/IP e heis que nas configurações de IP4 estava um IP que não era o do servidor, provavelmente do servidor que foi clonado.

Bom, foi só alterar o IP para o IP correto do servidor e o serviço subiu perfeitamente.

É isso aí pessoal, espero que isso possa ajudar vocês!

SQL Saturday #424 e #469

Olá pessoal,

Como já é de costume(um bom costume) esse ano teremos mais dois eventos SQL Saturday acontecendo no Brasil.

No dia 26/09/2015 acontecerá na cidade de São Paulo o SQL Saturday #424 que já tem sua agenda de palestras divulgadas e mais uma vez estarei participando desse evento como palestrante. No dia 21/11/2015 acontecerá em Brasília o SQL Saturday #469.

Essas são oportunidades únicas para quem está querendo ver um evento de alto nível técnico e para aumentar sua rede de contatos. Não percam essa oportunidade.

Para maiores informações da agenda e registro nos eventos clique aqui e aqui.

Nos vemos lá!

 

T-SQL Formatter

Olá pessoal,

Um grande desafio para quem trabalha com desenvolvimento T-SQL, Query Tuning ou até mesmo desenvolvimento de sistema em geral é quando nos deparamos com uma query T-SQL que não está formatada/identada, então gastamos um bom tempo formatando o texto para que ele fique o mais legível possível.

Hoje em dia existem várias ferramentas que podem formatar o texto da query, algumas pagas e outras não.  Hoje eu vou falar de uma que eu uso muito no dia a dia que é a Poor SQL.

A Poor SQL é free e open-source e uma grande vantagem dela é que é possível formatar sua query on-line através do próprio site ou instalar um dos diversos plugins offline existente. Atualmente os plugins são para SSMS/Visual Studio, Notepad++ e WinMerge. Além dos plugins também tem uma ferramenta console para formatar vários arquivos simultâneos, aplicação Windows Form e Library para você integrar com sua aplicação, ou seja, tem para todo mundo!

Enjoy!

SQL Server Além do Conceito: Blog Post Collection

É com grande satisfação que anuncio o lançamento do livro SQL Server Além do Conceito: Blog Post Collection onde sou um dos co-autores junto com mais 14 amigos que são profissionais que trabalham diretamente com SQL Server.

Além de grande conteúdo técnico com diversos assuntos sobre SQL Server o mais bacana é que a distribuição desse livro é totalmente gratuita.

Aproveitem para compartilhar com seus amigos, familiares e colegas de trabalho :).

Cliquem na imagem abaixo para fazer download do livro.

SQL Saturday #329 – Arquivos para download

Fala pessoal tudo bem?

No último sábado(25/10/2014) aconteceu o SQL Saturday #329 no RJ e mais uma vez eu pude palestrar ao lado de duas pessoas queridas que são Sulamita Dantas e Cibelle Castro.

Nessa oportunidade nós falamos sobre Policy Based Management que é uma feature muito útil presente desde a versão 2008 do SQL Server, que pode agilizar bastante seu trabalho mas que poucos utilizam.

Para quem quiser segue o link para download dos arquivos:

PPT, Demo e Polices.

 

SQL Saturday #325 – PPT e Script para Download

Aconteceu no último dia 27/09/2014 em São Paulo o SQL Saturday #325, onde palestrei junto com a Sulamita Dantas(blog | twitter) sobre Protheus e SQL Server 2014, onde abordamos algumas características do banco de dados desse ERP.

Para quem desejar estou colocando os arquivos para download.

PPT e Script

Obrigado à todos os participante. O evento não seria nada sem vocês.

SQL Saturday #325

No próximo sábado(27/09/2014) acontecerá em São Paulo o SQL Saturday #325.

Pela primeira vez estarei palestrando no SQL Saturday e em parceria com minha amiga Sulamita Dantas(Blog | Twitter) estaremos falando um pouco sobre Protheus e SQL Server 2014.

Protheus e SQL Server é um assunto que nós dois gostamos muito e então decidimos compartilhar um pouco do nosso conhecimento, passando dicas preciosas para o DBA que tem que administrar esse tipo de ambiente.

Não percam esse evento que tem tudo para ser o maior SQL Saturday realizado no Brasil.

Para maiores informações segue o link do evento:

http://sqlsaturday.com/325/eventhome.aspx

Esperamos vocês lá.