Simulando – Desastre e Recuperação de Bancos de Dados – Microsoft SQL Server 2008 R2 e 2012 – Parte IV.

Galera, bom dia.

Tudo bem?

Como prometido estou retornando com mais um post da série referente à Simulação de Desastre e Recuperação de Banco de Dados no Microsoft SQL Server 2008, R2 e 2012.

Neste post, vou destacar como podemos recuperar a estrutura física e lógica da tabela Clientes, que no post anterior acabou tendo a sua estrutura corrompida.

Você vai aprender e entender como podemos realizar este tipo de procedimento, identificando as páginas de dados que estão danificadas e quais os procedimentos devemos realizar para contornarmos este problema.

Vale ressaltar que este tipo de procedimento poderá apresentar riscos de perda de dados, sendo assim, recomendo a criação de uma ambiente de teste, com base, nos posts anteriores.

Para começar, vamos relembrar um pouco da estrutura do nosso banco de dados chamado MyDatabaseDesastre, sendo este banco, composto por uma tabela chamada Clientes, conforme apresenta a Figura 1 apresentada abaixo:

Figura1

Figura 1 – Estrutura do Banco de Dados MyDatabaseDesastre.

 

Agora que já relembramos a simples estrutura que estamos utilizando nesta série, podemos evoluir na identificação dos danos que foram causados.

 

Para começar, vou executar o Código 1, apresentado a seguir:

 

— Código 1 – Realizando um simples select na Tabela Clientes –

 

Select Codigo, Nome, Email from Clientes

 

Você poderá observar que o tempo de processamento deste simples código esta bem fora do que normalmente é executado, isso já pode ser considerado um sinal que existe algo de errado ou diferente em nosso ambiente.

 

Após alguns segundos o Management Studio conclui a execução deste bloco de código e para nosso espanto não temos o retorno dos dados, mas sim uma mensagem de erro é apresentada, conforme apresenta a Figura 2.

Recuperar-Parte4-Figura 2

 

Figura 2 – Mensagem de erro retornada pelo Management Studio.

Observe que a mensagem apresenta o código de erro 824 e nível de severidade 24, onde erros  com este nível de severidade normalmente nos indica que podemos estar se deparando com falhas de hardware relacioanadas com a mídia(disco rígido) utilizada para alocar os arquivos de nosso banco de dados, como também, podem ser considerados erros fatais, que impedem o funcionamento normal do SQL Server.

A Tabela 1 a seguir lista e descreve os níveis de severidade dos erros gerados pelo Mecanismo de banco de dados do SQL Server.

Nível de severidade Descrição
0-9 Mensagens informativas que retornam informações de status ou reportam erros que não sejam severos. O Mecanismo de Banco de Dados não gera erros de sistema com severidades de 0 a 9.
10 Mensagens informativas que retornam informações de status ou reportam erros que não sejam severos. Por razões de compatibilidade, o Mecanismo de Banco de Dados converte a severidade 10 em severidade 0 antes de retornar as informações de erro ao aplicativo de chamada.
11-16 Indica erros que podem ser corrigidos pelo usuário.
11 Indica que um determinado objeto ou entidade não existe.
12 Severidade especial para consultas que não usam bloqueio por causa de dicas de consulta especiais. Em alguns casos, operações de leitura executadas por essas instruções podem resultar em dados inconsistentes, pois os bloqueios não são usados para garantir a consistência.
13 Indica erros de deadlock de transação.
14 Indica erros relacionados à segurança, como uma permissão negada.
15 Indica erros de sintaxe no comando Transact-SQL.
16 Indica erros gerais que podem ser corrigidos pelo usuário.
17-19 Indica erros de software que não podem ser corrigidos pelo usuário. O usuário deve informar o problema ao seu administrador de sistema.
17 Indica que a instrução fez o SQL Server ficar sem recursos (como memória, bloqueios ou espaço em disco para o banco de dados) ou exceder algum limite definido pelo administrador de sistema.
18 Indica um problema no software Mecanismo de Banco de Dados, mas a instrução conclui a execução e a conexão com a instância do Mecanismo de Banco de Dados é mantida. O administrador de sistema deve ser informado sempre que uma mensagem com nível de severidade 18 ocorrer.
19 Indica que um limite do Mecanismo de Banco de Dados não configurável foi excedido e que o processo em lotes atual foi encerrado. Mensagens de erro com nível de severidade 19 ou maior pararam a execução do lote atual. Erros de severidade 19 são raros e devem ser corrigidos pelo administrador de sistema ou por seu principal provedor de suporte. Contate seu administrador de sistema quando uma mensagem com severidade de nível 19 ocorrer. Mensagens de erro com nível de severidade de 19 a 25 são gravadas no log de erros.
20-25 Indique problemas de sistema que são erros fatais, ou seja, a tarefa do Mecanismo de Banco de Dados que está executando uma instrução ou um lote que não está mais em execução. A tarefa registra informações sobre o que aconteceu e, depois, é encerrada. Na maioria dos casos, a conexão do aplicativo com a instância do Mecanismo de Banco de Dados também pode ser encerrada. Se isso acontecer, dependendo do problema, é possível que o aplicativo não consiga se reconectar.

Mensagens de erro nesse intervalo podem afetar todos os processos que acessam dados no mesmo banco de dados e indicar que um banco de dados ou objeto está danificado. Mensagens de erro com nível de severidade de 19 a 25 são gravadas no log de erros.

20 Indica que uma instrução encontrou um problema. Como o problema afetou apenas a tarefa atual, é improvável que o banco de dados tenha sido danificado.
21 Indica que foi encontrado um problema que afeta todas as tarefas no banco de dados atual, mas é improvável que o banco de dados tenha sido danificado.
22 Indica que a tabela ou o índice especificado na mensagem foi danificado por um problema de software ou hardware.

Erros de severidade de nível 22 raramente ocorrem. Se acontecer, execute o DBCC CHECKDB para determinar se outros objetos no banco de dados também foram danificados. O problema pode ser apenas no cache do buffer e não no próprio disco. Nesse caso, reiniciar a instância do Mecanismo de Banco de Dados corrige o problema. Para continuar trabalhando, você deve reconectar-se à instância do Mecanismo de Banco de Dados; caso contrário, use o DBCC para corrigir o problema. Em alguns casos, pode ser necessário restaurar o banco de dados.

Se a reinicialização da instância do Mecanismo de Banco de Dados não corrigir o problema, é porque o problema está no disco. Às vezes, destruir o objeto especificado na mensagem de erro pode resolver o problema. Por exemplo, se a mensagem informar que a instância do Mecanismo de Banco de Dados encontrou uma linha com comprimento 0 em um índice não-clusterizado, exclua o índice e crie-o novamente.

23 Indica que a integridade do banco de dados inteiro está em risco por um problema de software ou hardware.

Erros de severidade de nível 23 raramente ocorrem. Se um acontecer, execute o DBCC CHECKDB para determinar a extensão do dano. O problema pode ser apenas no cache e não no próprio disco. Nesse caso, reiniciar a instância do Mecanismo de Banco de Dados corrige o problema. Para continuar trabalhando, você deve reconectar-se à instância do Mecanismo de Banco de Dados; caso contrário, use o DBCC para corrigir o problema. Em alguns casos, pode ser necessário restaurar o banco de dados.

24 Indica uma falha de mídia. O administrador de sistema pode ter que restaurar o banco de dados. Também pode ser necessário contatar o seu fornecedor de hardware.

Tabela 1 – Relação de Níveis de Severidade existentes no Microsoft SQL Server.

Observações

  • Níveis de severidade de 19 a 25 enviam uma mensagem do SQL Server ao log de aplicativos do Microsoft Windows e disparam um alerta.
  • Eventos com níveis de severidade inferiores a 19 irão disparar alertas apenas se você tiver usado sp_altermessage, RAISERROR WITH LOG ou xp_logevent para obrigá-los a serem gravados no log de aplicativos do Windows.
  • Vale ressaltar que o Database Engine é responsável em identificar o tipo de erro e seu nível de severidade de acordo com a tabela de níveis existente no Microsoft SQL Server.

Se observarmos além do código de erro e nível de severidade, o Database Engine, nos retornou outra linha contendo mais informações técnicas identificadas pelo Query Processor, durante a execução deste bloco de código.

A seguir apresento a mensagem de erro:

SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x940faf8d; actual: 0x3c942ff4). It occurred during a read of page (1:153) in database ID 9 at offset 0x00000000132000 in file ‘C:\Bancos\MyDatabaseDesastre\MyDatabaseDesastre.mdf’.  Additional messages in the SQL Server error log or system event log may provide more detail.

 

This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

 

Olha só que legal, como o Microsoft SQL Server é uma ferramenta fantástica, moderna e inteligente e que não nos deixa na mão. Analisando de forma rápida a mensagem de erro, podemos ter um pouco mais de certeza do que esta acontecendo com nossa tabela Clientes. Para facilitar o entendimento desta mensagem e apresentar os pontos que devemos sempre analisar, vou dividir esta mensagem em algumas partes, sendo elas:

1)      SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x940faf8d; actual: 0x3c942ff4): Nesta parte da mensagem o Database Engine nos informa que estamos se deparando com uma falha lógica de consistência, que esta sendo gerada durante o processo de Input e Output.

 

2)      It occurred during a read of page (1:153) in database ID 9 at offset 0x00000000132000 in file ‘C:\Bancos\MyDatabaseDesastre\MyDatabaseDesastre.mdf’: Nesta parte da mensagem, podemos entender que o erro que ocorrendo durante a tentativa de leitura da página de dados 1:153, no offset 0x00000000132000, sendo este, offset justamente o qual foi alterado anteriormente.

 

3)      This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB): Neste trecho a mensagem informa que este tipo de erro poderá representar um risco para integridade dos nossos dados e deverá ser corrigido o quanto antes possível.

 

4)      This error can be caused by many factors; for more information, see SQL Server Books Online: Nesta última parte, o Database Engine, nos informa que esta falha pode ser ocasionada por diversas fatores. Além disso, indica que através do Books Online podemos obter mais informações sobre esta falha.

 

Muito bem, já temos um possível caminho para percorrer a fim de encontramos uma possível solução para este erro, nosso próximo passo é definirmos qual estratégia será seguida.

Como em nosso cenário temos especificamente uma única tabela que esta apresentando falhas em sua estrutura física e lógica, a alternativa inicial neste momento será utilizar o comando DBCC CheckTable.

 

Usando o comando DBCC CheckTable

Basicamente o comando DBCC CheckTable tem como finalidade avaliar e identificar a estrutura física de uma tabela ou visão indexada. Por padrão, após executar este comando, o Database Engine deverá apresentar quais são as unidades de alocação que formam as páginas de dados estão apresentando falhas.

Sendo assim, vamos fazer uso deste comando e verificar as partições que estão apresentando falhas, para isso utilizaremos o Código 2.

 

— Código 2 – Verificando as estruturas de alocação da tabela Clientes –

DBCC CheckTable (‘Clientes’) With All_Errormsgs

Após a execução do comando DBCC CheckTable, o Database Engine nos retornar o resultado do processamento realizado, com base, análise da estrutura física e lógica da nossa tabela, neste caso, teremos como resultado, informações referentes as Partition(Partições) e Alloc Unit(Unidades de Alocação) que estão apresentando falhas.

A seguir apresento o resultado gerado pelo DBCC CheckTable:

Msg 8928, Level 16, State 1, Line 1

Object ID 1061578820, index ID 1, partition ID 72057594039894016, alloc unit ID 72057594040811520 (type In-row data): Page (1:153) could not be processed.  See other errors for details.

 

Msg 8939, Level 16, State 98, Line 1

Table error: Object ID 1061578820, index ID 1, partition ID 72057594039894016, alloc unit ID 72057594040811520 (type In-row data), page (1:153). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -4.

 

Msg 8980, Level 16, State 1, Line 1

Table error: Object ID 1061578820, index ID 1, partition ID 72057594039894016, alloc unit ID 72057594040811520 (type In-row data). Index node page (1:155), slot 0 refers to child page (1:153) and previous child (0:0), but they were not encountered.

 

Msg 8978, Level 16, State 1, Line 1

Table error: Object ID 1061578820, index ID 1, partition ID 72057594039894016, alloc unit ID 72057594040811520 (type In-row data). Page (1:156) is missing a reference from previous page (1:153). Possible chain linkage problem.

There are 25 rows in 1 pages for object “Clientes”.

 

CHECKTABLE found 0 allocation errors and 4 consistency errors in table ‘Clientes’ (object ID 1061578820).

repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKTABLE (MyDatabaseDesastre.dbo.Clientes).

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

 

Observe que foram retornadas quarto códigos de mensagens de erros: 8928, 8939, 8980 e 8978, em cada uma destas mensagens são apresentadas as informações:

  • Object ID: 1061578820
  • Partition ID: 72057594039894016
  • Allocation Unit ID: 72057594040811520

Note que através destes dados teremos a capacidade de obter mais informações sobre os componentes internos que compõem e armazenam tanto a nossa tabela Clientes, como também, seus dados e índice.

 

Desta forma, para confrontar que estes elementos representam realmente a estrutura física e lógica da nossa tabela, vamos executar o código 3 apresentado abaixo.

— Código 3 – Confirmando os componentes que formam a estrutura da tabela Clientes —

Select SP.partition_id,

OBJECT_NAME(SP.object_id) As ObjectName,

SAU.type_desc,

SAU.total_pages,

SAU.data_pages,

SAU.used_pages,

SDS.name,

sds.type_desc,

SP.rows

from sys.partitions SP Inner Join sys.allocation_units SAU

On sp.partition_id = sau.container_id

Inner Join sys.data_spaces SDS

On sau.data_space_id = sds.data_space_id

Where  sp.partition_id = 72057594039894016 –> Código da PartitionID

 

Neste código estamos utilizando três visões de sistemas existentes desde a versão 2005 do Microsoft SQL Server, sendo elas:

ü  Sys.partitions: Contem uma linha para cada partição de cada tabela e índice existente no banco de dados acessado.

 

ü  Sys.Allocation_Units: Contem uma linha para cada unidade de alocação que compõem o banco de dados acessado.

 

ü  Sys.Data_Spaces: Contem uma linha para cada data space utilizado por Filegroup, Partition Schema ou FileStream que formam o banco de dados acessado.

 

Podemos observar e identificar com base resultado apresentado que nossa tabela clientes possui neste momento a seguinte estrutura:

ü  Total de Páginas da Tabela: 4;

ü  Total de Página de Dados: 2;

ü  Páginas de dados utilizadas: 4; e

ü  Quantidade de linhas da tabela: 150 (anote este valor).

Perfeito, já temos todas as confirmações, mas uma pergunta ainda esta sem resposta.

Como podemos resolver a estrutura da nossa tabela Clientes?

Se observamos as mensagens apresentadas após a execução do comando DBCC CheckTable, podemos observar que o Database Engine, através do seu mecanismo de identificação de erro nos da uma possível sugestão: “repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKTABLE (MyDatabaseDesastre.dbo.Clientes).”

Show de bola, o Database Engine, realmente é fantástico, ele esta dando uma possível sugestão, utilizando o comando DBCC CheckTable com a opção Repair_Allow_Data_Loss, como uma alternativa básica para corrigir a estrutura física e lógica da nossa tabela Clientes, para isso vamos executar o Código 4.

 

Importante: Para podermos utilizar a opção Repair_Allow_Data_Loss é necessário que nosso banco de dados tenha o seu acesso restrito, por isso, se faz necessário alterar a opção Restrict Access para Single_User.

 

— Código 4 – Corrigindo a estrutura física e lógica da tabela Clientes –

Alter Database MyDatabaseDesastre

Set Single_User

Go

 

DBCC CheckTable (‘Clientes’, Repair_Allow_Data_Loss)

Go

Importante: Ao executar a opção Repair_Allow_Data_Loss, estamos dizendo para o SQL Server que aceitamos correr o risco de perder dados durante o processo de reconstrução da estrutura física e lógica da tabela Clientes.

 

Execumos o comando DBCC CheckTable, e o Database Engine nos apresentou o seguinte resultado:

DBCC results for ‘Clientes’.

There are 25 rows in 1 pages for object “Clientes”.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Nossa tabela teve sua estrutura reconstruída de forma correta e agora já podmeos fazer uso da mesma, observe que a mensagem apresentada acima informa que nossa tabela possui 25 linhas de registros em uma página de dados, valor muito diferente do que tinhamos antes de executar o comando DBCC CheckTable. Para concluirmos vamos novamente executar o Código 3 apresentando anteriormente:

 

Select SP.partition_id,

OBJECT_NAME(SP.object_id) As ObjectName,

SAU.type_desc,

SAU.total_pages,

SAU.data_pages,

SAU.used_pages,

SDS.name,

sds.type_desc,

SP.rows

from sys.partitions SP Inner Join sys.allocation_units SAU

On sp.partition_id = sau.container_id

Inner Join sys.data_spaces SDS

On sau.data_space_id = sds.data_space_id

Where  sp.partition_id = 72057594039894016 –> Código da PartitionID

 

Observe que o Database Engine não encontrar nenhuma informação após a execução do nosso código.

 

Mas porque será que isso aconteceu?

 

A resposta é muito simples, durante a execução do comando DBCC CheckTable todas as unidades de alocação, partições e páginas de dados foram reconstruídas ou até mesmo, novas áres de alocação física e lógica foram criadas e atribuídas para nossa tabela.

Com isso, torna-se possível acessar a tabela Clientes, como também, a sua contagem de linhas de registro agora esta correta, para confirmarmos este novo cenário, vamos executar o Código 5 apresentado na sequência:

 

— Código 5 – Confirmando os componentes que formam a nova estrutura da tabela Clientes —

Select SP.partition_id,

OBJECT_NAME(SP.object_id) As ObjectName,

SAU.type_desc,

SAU.total_pages,

SAU.data_pages,

SAU.used_pages,

SDS.name,

sds.type_desc,

SP.rows

from sys.partitions SP Inner Join sys.allocation_units SAU

On sp.partition_id = sau.container_id

Inner Join sys.data_spaces SDS

On sau.data_space_id = sds.data_space_id

Where  sp.object_id = OBJECT_ID(‘Clientes’)

Agora sim, o Database Engine conseguiu identificar e retornar os dados sobre a nova estrutura de componentes que estão neste momento formando a nossa tabela Clientes, conforme apresenta a Figura 3.

Recuperar-Parte4-Figura 3

Figura 3 – Nova estrutura física e lógica que forma a tabela Clientes.

 

 

 

Agora esta tudo resolvido, nossa tabela esta corrigida, integra e pronta para ser utilizada, sendo assim, já podemos alterar a opção Restrict Access do nosso banco de dados, para Multi_User, para isso execute o Código 6 apresentado na sequência:

 

 

 

— Código 6 – Alterando o Restrict Acess para Multi_User —

 

 

 

Alter Database MyDatabaseDesastre

 

Set Multi_User

 

Go

 

 

 

Com isso, vou concluir mais este artigo e começar a pensar na última parte, dedicada exclusivamente ao arquivo de log do nosso banco de dados, como podemos corromper esta estrutura física e realizar o seu processo de restauração de dados.

 

Espero que você tenha gostado, estamos na reta final desta série, retorno em breve.

 

Mais uma vez obrigado por sua atenção e visita.

 

Até mais.

 

 

Autor: Junior Galvão - MVP

Profissional com vasta experiência na área de Tecnologia da Informação e soluções Microsoft. Graduado no Curso Superior em Gestão da Tecnologia de Sistemas de Informação pela Uninove - Campus São Roque. Pós-Graduado no Curso de Gestão e Engenharia de Processos para Desenvolvimento de Software com RUP na Faculdade FIAP - Faculdade de Informática e Administração Paulista de São Paulo. Pós-Graduado em Gestão da Tecnologia da Informação Faculdade - ESAMC Sorocaba. Cursando Mestrado em Ciências da Computação - UFSCar - Campus - Sorocaba. Formação MCDBA Microsoft, autor de artigos acadêmicos e profissionais postados em Revistas, Instituições de Ensino e WebSistes. Meu primeiro contato com tecnologia ocorreu em 1995 após meus pais comprarem nosso primeiro computador, ano em que as portas para este fantástico mundo se abriram. Neste mesmo ano, comecei o de Processamento de Dados, naquele momento a palavra TI não existia, na verdade a Tecnologia da Informação era conhecida como Computação ou Informática, foi assim que tudo começou e desde então não parei mais, continuando nesta longa estrada até hoje. Desde 2001 tenho atuado como Database Administrator - Administrador de Banco de Dados - SQL Server em tarefas de Administração, Gerenciamento, Migração de Servidores e Bancos de Dados, Estratégias de Backup/Restauração, Replicação, LogShipping, Implantação de ERPs que utilizam bancos SQL Server, Desenvolvimento de Funções, Stored Procedure, Triggers. Experiência na Coordenação de Projetos de Alta Disponibilidade de Dados, utilizando Database Mirroring, Replicação Transacional e Merge, Log Shipping, para versões: 2000, 2005, 2008, 2008 R2, 2012 e 2014. Atualmente trabalho como Administrador de Banco de Dados no FIT - Instituto de Tecnologia da Flextronics, como também, Consultor em Projetos de Tunnig e Performance para clientes. Desde 2008 exerço a função de Professor Universitário, para as disciplinas de Banco de Dados, Administração, Modelagem de Banco de Dados, Programação em Banco de Dados, Sistemas Operacionais, Análise e Projetos de Sistemas, entre outras. Possuo titulação Oficial Microsoft MVP - SQL Server renovada desde 2007.

5 comentários em “Simulando – Desastre e Recuperação de Bancos de Dados – Microsoft SQL Server 2008 R2 e 2012 – Parte IV.”

  1. Fiz seguindo o seu passo a passo, fui copiando e colando todo o conteúdo no query editor e funcionou tudo. Imprimi seu material, salvei toda a query e agora é só praticar.
    Mas uma vez gostaria de registrar meus agradecimento e dizer o quanto foi importante seus ensinamentos.

    Curtir

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s