Movimentação incorreta da TempDB/Validação do caminho físico no SQL Server 2014!


Fala galera tranquilo?

 Hoje vou comentar sobre um erro que pode ocorrer em seu SQL Server quando é feita uma movimentação incorreta dos arquivos da base de sistema TempDB, e sobre o que foi feito no SQL Server 2014 para evitar este problema.

 Minha simulação começa com o SQL Server 2005 RTM. Notem na tela abaixo que tenho 3 arquivos de dados para minha TempDB e 1 arquivo de log:



 Ok. Quero movimentar os arquivos de dados para outra unidade, só que sem querer vejam o nome físico que digitei no comando referente ao segundo arquivo NDF (tempdev_2), eu coloquei exatamente o mesmo nome físico do primeiro arquivo NDF (tempdev_1). Ou seja, dois arquivos de nomes lógicos diferentes apontando agora para um mesmo nome físico no disco:


 Erro? Negativo, o SQL Server acata meu comando e diz que a alteração será válida na próxima vez que o serviço for reiniciado:

The file "tempdev" has been modified in the system catalog. The new path will be used the next time the database is started.
The file "tempdev_1" has been modified in the system catalog. The new path will be used the next time the database is started.
The file "tempdev_2" has been modified in the system catalog. The new path will be used the next time the database is started.


 Vamos então reiniciar o serviço via SQL Configuration Manager, faço o stop e logo na sequência o start do mesmo:


Parando...
 


Iniciando e...


Ops...

 O serviço não subiu. Vamos analisar no ERRORLOG do SQL Server o que pode estar ocorrendo, veja a mensagem marcada em vermelho, que interessante:

2014-01-06 04:22:18.63 spid9s      Starting up database 'tempdb'.
2014-01-06 04:22:18.94 spid9s      Error: 5171, Severity: 16, State: 2.
2014-01-06 04:22:18.94 spid9s      E:\MAGU\2005\tempdev_1.ndf is not a primary database file.
2014-01-06 04:22:19.00 spid9s      Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized.
2014-01-06 04:22:19.00 spid9s      SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
2014-01-06 04:22:19.02 Server      The SQL Network Interface library could not deregister the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b. Administrator should deregister this SPN manually to avoid client authentication errors.
 Um erro bastante estranho. Bom, mas precisamos subir o SQL Server para tentar entender o que está ocorrendo. Abra um prompt de comando elevado, navegue até o diretório onde se encontram os binários do SQL Server, e execute o seguinte comando: 

sqlserver.exe -s MSSQL2005 -f -T3608 

 Reparem que minha instalação tem uma instância nomeada, por isso o parâmetro –s. O parâmetro -f, indica que quero subir o SQL Server com uma configuração mínima, montando apenas o Resource Database e o master. E o trace flag 3608 serve para não fazer o recovery dos bancos de dados de usuário.

 Se o comando for executado corretamente, a instância do SQL Server vai iniciar numa janela de console, algo parecido com tela abaixo:

 Abra outro prompt de comando elevado e use o SQLCMD para conectar a instância do SQL Server: 

sqlcmd -S .\MSSQL2005 -E 

 Note que uso o -S para indicar a instância que deve ser conectada, e o -E para indicar que a minha conta do Windows deve ser usada para o login (este parâmetro é opcional). Lembrando que neste ponto a instância aceita apenas um único login de usuário com privilégios de sysadmin, uma vez que a instância também está em single-user mode.
 Assim que o login for completado veremos o prompt do SQLCMD (1>_). Vamos conferir agora a tabela de sistema sysaltfiles, ela possui informações referentes aos arquivos das bases de dados. Digite os comandos abaixo em linhas separadas, dando ENTER ao final de cada um deles:





 Você verá algo parecido com a imagem abaixo, veja a parte correspondente a base TempDB, notem que dois nomes lógicos apontam para um mesmo arquivo físico no disco:

 Isto está impedindo nossa instância de subir, precisamos resolver isso, como? Agora que estamos logados na instância basta remover um dos arquivos, no caso estou removendo o segundo arquivo NDF com os comandos abaixo:



 Feito isso, vamos parar o SQL Server pressionando CTRL+C, depois digitando “Y” e dando ENTER:


 Volte no SQL Configuration Manager e inicie normalmente o SQL Server. Note que aogra a instância inicia normalmente:


 Este comportamento se repetiu nos meus testes com o SQL Server 2008, SQL Server 2008 R2 e SQL Server 2012. Mas com o SQL Server 2014 CTP2 (CTP1 também) a história ficou diferente. 

Repetindo os procedimentos no SQL Server 2014 CTP2... 

 Notem que em minha instância do SQL Server 2014 CTP2 tenho 3 arquivos de dados e 1 arquivo e log para o tempdb:

 Quero movimentar meus arquivos de dados do tempdb para outra unidade, porém cometo aquele mesmo erro que cometi no SQL Server 2005: aponto dois nomes lógicos para um mesmo arquivo físico no disco:


  
 Mas agora uma mensagem de erro é exibida quando um nome lógico aponta para um arquivo físico que já foi utilizado no ALTER DATABASE, veja a mensagem em destaque na tela abaixo:
 

The file "tempdev" has been modified in the system catalog. The new path will be used the next time the database is started.
The file "tempdev_1" has been modified in the system catalog. The new path will be used the next time the database is started.
Msg 12106, Level 16, State 1, Line 9
The path name 'D:\MAGU\2014\tempdev_1.ndf' is already used by another database file. Change to another valid and UNUSED name.
 Ou seja, no SQL Server 2014 foi introduzida uma validação dos arquivos físicos usados no ALTER DATABASE, evitando o problema que demonstrei de ocorrer em versões anteriores do SQL Server. Também testei o comando para bancos de dados de usuário, tentei mover um arquivo de dados para um mesmo caminho físico de um outro arquivo já existente e apenas o SQL Server 2014 não permitiu a movimentação.

--ATUALIZAÇÃO 22/08/2014--

Pessoal, apenas uma rápida atualização neste post. Através do Connect questionei a Microsoft o motivo deste comportamento nas versões anteriores ao SQL Server 2014, tive um retorno hoje dizendo que não irá haver correção para estas versões. Na minha opinião, para o SQL Server 2008 / 2008 R2 não seria razoável esperar por uma correção, uma vez que o suporte principal destes produtos terminou recentemente no dia 08/07/2014:

http://support.microsoft.com/lifecycle/search/default.aspx?sort=PN&alpha=sql+server+2008&Filter=FilterNO

Para o SQL Server 2012 havia uma esperança pois seu suporte principal só termina em 11/07/2017:

http://support.microsoft.com/lifecycle/search/default.aspx?sort=PN&alpha=sql+server+2012&Filter=FilterNO

Todo caso fica o alerta do problema para quem estiver rodando alguma destas versões =] 

O link do connect para quem tiver interesse segue abaixo:

https://connect.microsoft.com/SQLServer/feedbackdetail/view/813287/tempdb-alter-database-modify-file-allows-duplicated-physical-paths

Espero que tenham gostado do post pessoal, um abraço e até a próxima!

4 comentários: