Compartilhar via

Recuperando o banco de dados "master"

  • Artigo

Introdução

Este artigo apresenta um procedimento para acessar e recuperar o banco de dados "master" de uma instância do SQL Server, habilitando o acesso de usuários(logon), linkedservers, configurações da instância (sp_configure) e a utilização de outros bancos de dados de usuários, após ocorrer uma falha que impossibilita o seu acesso.

Segue algumas alternativas para demonstrar as possíveis soluções para quem tenha uma dificuldade semelhante em reestabelecer/acessar o serviço de sua instância SQL Server.

Este procedimento foi testado e poderá ser utilizado à partir do SQL Server 2008 até a versão atual.

Sinta-se livre para adicionar ou modificar o conteúdo deste artigo.

Conhecendo os Problemas mais Comuns

Existem algumas falhas comuns de hardware e software que podem impossibilitar o uso do banco de dados "master". Segue algumas destas falhas:

Erro 574: Falha na alteração do CONFIG

Este erro pode ser causado por uma falha na atualização de parâmetros de uma instância SQL, impossibilitando a utilização de novos parâmetros e colocando o banco de dados "master" imediatamente no status "OFFLINE" .

Veja abaixo a mensagem de erro exibida e uma breve descrição sobre o que ocorreu:

Error: 574, Severity: 16, State: 0.
CONFIG statement cannot be used inside a user transaction.

Este erro ocorre porque o SQL Server não obteve sucesso ao atualizar um ou mais parâmetros através da procedure de sistema sp_configure, impossibilitando o uso parcial, e em alguns casos, total da instância SQL.

Erro 605: Falha na localização da Página de Dados

Este erro pode ocorrer devido à condições de escrita perdidas ou condições de leitura obsoletas. Também poderá ser associado à erro de integridade de dados relacionados para um ou mais objetos do banco de dados.

Veja abaixo a mensagem de erro exibida e uma breve descrição sobre o que ocorreu:

Error: 605, Severity: 21, State: 1 
2015-05-15 14:12:27.67 spid63
Attempt to fetch logical page (1:123456) in database 'XYZ' belongs to object 'Table1', not to object 'Table2'...

Este erro ocorreu porque houve uma perda de referência de paginação no Nível Folha, impossobilitando o I/O lógico dos dados.

Erro 823: Falha no Dispositivo ou no Fim de Arquivo

Este erro pode ser causado por uma falha de I/O lógico ou físico do SQL Server no quando manipulando o banco de dados. Para outros bancos de dados de usuários, é recomendado como primeira alternativa, executar o comando DBCC CHECKDB, no caso do banco de dados "master".

Este erro é o mais comum entre estes casos e esta também relacionadas às API's do Windows:

  • ReadFile; 
  • WriteFile;
  • ReadFileScatter;
  • WriteFileGather, e;
  • GetOverLappedResult;

Caso necessário, é possível adicionar o Trace "818" (habilita um buffer de anel de memória para rastrear as últimas 2048 operações com sucesso) para obter maiores informações sobre o problema através do SQL Server Logs. 

Veja abaixo as possíveis mensagens de erro e uma breve descrição sobre o que ocorreu:

Error: 823, Severity: 24, State: 2.
2010-03-06 22:41:19.55 spid58      
The operating system returned error 38(Reached the end of the file...

Este erro pode ocorrer no arquivo de banco de dados durante a operação de I/O, após o deslocamento de dados dentro do arquivo físico de leitura ou escrita foi perdida.

Error: 823, Severity: 24, State: 2 
2015-05-16 14:32:35.12 spid75    
I/O error 1117 (The request could not be performed because of an I/O device error.) ...

Este erro pode ocorrer devido a um problema no sistema operacional ou no hardware. Neste caso, se possível ser executado, o comando DBCC CHECKDB pode não indicar nenhum problema.

Error: 823, Severity: 24, State: 2 
2015-05-16 17:32:21.62 spid66
I/O error (bad page ID) detected during read at offset...

Este erro ocorre por uma falha lógica na verificação do cabeçalho da Página de Dados (Nível Folha), não sendo possível obter mais informações através de I/O físico.

Error: 823, Severity: 24, State: 4 
I/O error 2(The system cannot find the file specified.) ...

Este erro ocorre porque o SQL Server obteve sucesso ao abrir o arquivo do banco de dados, mas não recebeu um erro "identificador inválido" indicando uma falha de leitura e/ou escrita.

Isto normalmente está associado a uma falha de operação no FileSystem do sistema operacional ou em um dispositivo/volume vinculado ao sistema operacional com um ou mais arquivos deste bancos de dados (.MDF ou .NDF ou .LDF). 

Erro 3417: Falha ao Atualizar Versão

Este erro ocorre quando SQL Server não consegue inicializar por um problema com o banco de dados "master". Caso o banco de dados "master" ou "tempdb" não puderem ficar com status ONLINE, o serviço da instância SQL Server não poderá ser executada.

Esse erro normalmente surge em consequência de outros erros. Revise os Logs de Erros do SQL Server para encontrar a causa original.

Caso necessário, é possível adicionar o Trace "902" (SQL Server pode não inicializar seu serviço após a instalação/remoção de atualizações, como indicado no KB 2163980) para obter maiores informações sobre o problema utilizando o SQL Server Agent ou um Pacote de Atualização Cumulativa. 

Veja abaixo as possíveis mensagens de erro e uma breve descrição sobre o que ocorreu:

Error: 3417, Severity: 21, State: 3.
Script level upgrade for database ‘master’ failed because upgrade step ‘xxxxx’ encountered error...

Pode ser uma indicação que o banco de dados "master" é inválido para o SQL Server. Neste caso é necessário ajustar a referência da localização dos banco de dados de sistema do SQL Server no Registro do Windows (regedit). Para editar a Chave do Registro para um diretório válido é acessar: 

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.SuaInstanciaSQL\Setup\SQLDataRoot

Veja na imagem abaixo a localização da chave "SQLDataRoot" (clique na Imagem para aumentar):

Error: 3417, Severity: 21, State: 3.
2016-05-18 21:35:12.96 spid7s 
Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.

Este problema pode ocorrer por uma falha no SQL Agent da versão "SQL Server 2008 R2", que aparece durante a atualização do banco de dados "master" devido a falta de parametrizações armazenadas no banco de dados "msdb", o que impossibilita a execução normal do SQL Server.

É possível que a correção deste problema esteja relacionada apenas a habilitar o SQL Agent, para isso será necessário executar o script T-SQL abaixo:

EXEC sp_configure 'show advanced', 1
RECONFIGURE
GO

EXEC sp_configure 'allow updates', 0
RECONFIGURE
GO

EXEC sp_configure 'Agent XPs', 1
RECONFIGURE
GO

Para correção deste problema, é recomendado a utilização de uma versão mais recente do SQL Server, preferencialmente à partir do "SQL Server 2012".

Caso não seja possível, será necessário atualizar sua versão do "SQL Server 2008 R2" à partir do Pacote "Cumulative Update 2" ou superior, como indicado no KB 2072493.

Erro 3448 e 3456: Falha no Log de Transações

Estes erros podem ocorrer por uma falha na operação de restauração do Log de Transações.

Assim que a falha ocorre o SQL Server altera o status do banco de dados afetado para SUSPECT. O filegroup PRIMARY e, possivelmente, outros filegroups relacionados à este banco podem estar danificados.

O banco de dados não pode ser recuperado durante a inicialização do serviço da Instância SQL Server e, portanto, não está disponível.

Caso necessário, é possível adicionar o Trace "818" (habilita um buffer de anel de memória para rastrear as últimas 2048 operações com sucesso) para obter maiores informações sobre o problema através do SQL Server Logs. 

Veja abaixo as possíveis mensagens de erro e uma breve descrição sobre o que ocorreu:

Error: 3448, Severity: 21, State: 1
2003-07-24 16:52:40.99 spid63
Could not undo log record (1:1234:15), for transaction ID (0:5321), on page (1:7890), database 'XYZ' (database ID 9). Page information: LSN = (4567:98765:18), type = 2. Log information: OpCode = 2, context 1...

Este problema pode ocorrer quando o SQL Server fecha automaticamente as conexões com o banco de dados e logo à seguir tenta abrir e recuperar o banco de dados.

É possível que a primeira página no Nível Folha que apresenta o erro 605 ou 823, considere uma página com problema e então o ID da Página é apenas alocado pelo SQL Server.

Antes da fase de recuperação, quando o SQL Server tenta ler o ID da Página com problema, os principais detalhes sobre o cabeçalho da Página são registrados no Log de Erros do SQL Server.

Esta ação é importante porque pode ajudar a distinguir os cenários entre perda de dados durante a execução da API "WriteFile" e a leitura de dados obsoletos utilizados pela API "ReadFile".

Error: 3456, Severity: 21, State: 1

2010-02-06 15:57:24.14 spid17s
Could not redo log record (1:987654:23), for transaction ID (0:123456), on page (1:78901), database 'XYZ' (database ID 8). Page: LSN = (4567:5432:10), type = 3. Log: OpCode = 2, context 5, PrevPageLSN: (7890:123456:78). Restore from a backup of the database, or repair the database.

A causa dessa ocorrência pode ser obtida examinando o Log de Eventos do Windows para procurar um erro que indique a falha específica no SQL Server.

A ação a ser implementada depende das informações indicadas no Log de Eventos do Windows, indicando se o erro do SQL Server foi provocado por uma condição transitória ou por uma falha permanente.

Soluções Comuns

Para solucionar estes problemas de falha no banco de dados "master", seja qual for o erro indicado, é recomendado seguir uma ou mais sugestões de solução como indicado.

É possível optar por descartar algumas destas sugestões em um primeiro momento, com o intuito de reestabelecer o uso da Instância SQL Server no menor espaço de tempo possível, porém é interessante entender que estas orientações também servem como um caratér preventivo, indicando procedimentos a serem verificados com determinada regularidade em seu ambiente.

Segue abaixo as sugestões de soluções para reestabelecer o banco de dados "master":

Diagnosticar falhas de Integridade no I/O

Esta é uma das sugestões que tem um caráter mais preventivo do que corretivo, porque o diagnótico é uma forma de simular através de instruções em massa e aleatórias a capacidade de carga e suporte à integridade de dados, sem necessáriamente aplicar medidas corretivas durante sua execução.

Para esta tarefa é recomendado a utilização do SQLIOSim para simular a leitura de páginas que ele escreveu e validar os seus dados, utilizando um conjunto de buffers para gerar dados aleatóriamente para indicar se existem possíveis falhas no I/O com a instância SQL Server que podem prejudicar um arquivo de banco de dados de forma irrecuperável.

Para obter resultados significativos, é recomendado a configuração do utilitário através do arquivo "default.cfg.ini" para execução com pelo menos 10 ciclos de teste em 300 segundos/ciclo (em caso de testes básicos).

Este utilitário também pode controlar sobre consumo de memória, a utilização de arquivos por banco de dados, comandos de SHRINK e outros. Isso irá ajudá-lo a melhorar sua segurança e a disponibilidade de acesso a seus dados.

O utilitário SQLIOSim é disponibilizado em todas as versões do SQL Server à partir do 2008 nas plataformas x86 e x64, porém você poderá fazer o download através dos links abaixo:

Como alternativa, é possível também utilizar o SQLIO para diagnosticar a capacidade de I/O de um sistema de discos, que está sendo consumida pelo SQL Server, obtendo diversos logs de operações e verificações realizadas. 

Para auxiliar a configuração do SQLIO, é recomendada a leitura do artigo SQLIO Disk Subsystem Benchmark Tool: Troubleshooting Hangs and Errors.

Nota
Para executar os utilitários SQLIOSim e SQLIO, é necessário utilizar privilégios de Administrador no servidor.

Corrigir Problemas no disco

Fazer um diagnóstico no hardware e nos discos pode ajudar a localizar o problema e corrigir falhas no disco de armazenamento de seus bancos de dados.

Execute o comando Chkdsk no Prompt de Comando para obter maiores informações e corrigir falhas nas trilhas de um disco.

Certifique-se que sua Instância SQL Server está atendendo todos os requisitos mínimos de I/O para uso do Mecanismo do Banco de Dados (Database Engine).

Executar o CHECKDB

Quando um banco de dados está definido em "Modo de Emergência" seu status permanece ONLINE mas com acesso READ_ONLY.

O CHECKDB pode executar alguns reparos especiais para recuperar o banco de dados, como os parâmetros de reparação: REPAIR_FAST, REPAIR_REBUILD e REPAIR_ALLOW_DATA_LOSS.

Como última opção, é possível utilizar o parâmetro REPAIR_ALLOW_DATA_LOSS que permite corrigir bancos de dados geralmente irrecuperáveis, descartando páginas marcadas como corrompidas, com problemas de I/O e inconsistências transacionais no Log do banco de dados. 

ATENÇÃO
O parâmetro REPAIR_ALLOW_DATA_LOSS pode causar perda de dados e deve ser utilizado como um último recurso, apenas quando não for possível reestabelecer o uso do banco de dados.

Efetuar o RESTORE

Contando que você tem o BACKUP FULL do banco de dados "master", você deve efetuar o RESTORE utilizando o utilitário "sqlcmd", mas antes de efetuar esta tarefa você deve configurar sua instância SQL para ser inicializada em modo "SINGLE USER".

Para isso, acesse o "SQL Server Configuration Manager", selecione "SQL Server Services" e clique com o botão direito no serviço "SQL Server (SuaInstância)" para selecionar a opção "Properties".

Assim que a janela "Properties" abrir, selecione a opção "Startup Parameters".

Adicione o parâmetro "-m" (minúsculo) e à seguir reinicie o serviço de sua instância. Veja a imagem abaixo:

https://social.technet.microsoft.com/Forums/getfile/637378

Assim que sua instância SQL estiver inicializada como "SINGLE USER", então utilize o "Prompt de Comando" executando com permissão de "Administrador" para executar o "sqlcmd".

Faça a conexão com sua instância SQL utilizando o parâmetro -S "SeuServidor\SuaInstancia", segue o comando abaixo:

sqlcmd -S SeuServidor\SuaInstancia

Logo que for identificado o acesso, será habilitado a primeira linha "1)" para manipulação desta instância SQL.

Basta digitar a instrução SQL para RESTORE do banco "master" com "REPLACE" para substituir o banco corrompido. Segue o script T-SQL abaixo:

RESTORE DATABASE  master FROM  DISK = 'C:\SeuCaminhoOndeEstaBackUp\master.bkp' WITH  REPLACE;

Veja a imagem abaixo o procedimento de RESTORE através do Prompt, utilizando SQLCMD:

Nota
O SQLCMD utiliza o driver ODBC para acessar sua Instância SQL, devido às diversas opções que podem ser aplicadas, é possível obter um resultado diferente ao executar a mesma consulta no SQL Server Management Studio(SSMS) no modo "SQLCMD" e no utilitário SQLCMD.

Reinstalar a Instância SQL

Como alternativa, é recomendado remover a instância SQL Server deste servidor e reinstalar de acordo com os recursos necessários.

Antes de remover, tenha certeza de que todos os serviços SQL estão parados e, se possível, caso não tenha BACKUP dos bancos de dados então faça uma cópia dos arquivos disponíveis em outro local.

Temporariamente, enquanto o ambiente não está plenamente reestabelecido, é possível utilizar os bancos de dados de usuários em outra Instância SQL Server.

Conclusão

É importante identificar os possíveis problemas que podem ocorrer em seu ambiente e criar ao menos um procedimento de recuperação eficiente.

Este artigo abordou um dos problemas críticos que podem impedir a utilização de sua Instância SQL e manter rotinas preventivas para sua instância SQL e o BACKUP de seus bancos de dados de sistema, principalmente o "master", pode ser a diferença entre perder ou recuperar objetos importantes no seu ambiente.

Outros Recursos

Este artigo também obteve suporte ao seu conteúdo através dos KB's: 826433, 828339 e 2015755.

Referências

Veja Também