Perguntas e respostas sobre SQL
Dicas de recuperação de corrupção, conselhos para a redução de bancos de dados e muito mais
Paul S. Randal
Pergunta: Minha estratégia de backup é um backup completo diariamente à 1 h e um backup de log a cada hora. Um DBCC CHECKDB também executa todos os dias à 4 h Se eu receber trabalhar em 8: 00 e descubra que a consistência noite verifica encontrada corrupção abrangente, como eu possível recuperar sem perder uma grande quantidade de dados?
Resposta: Que depende de quando a corrupção ocorreu, o que está corrompido e que backups tiver. O ideal é que poderia ter um plano de recuperação do desastre resolvido antecipadamente seria orientá-lo durante as próximas etapas, mas considerarei que essa é uma pergunta hipotética.
No cenário que você descrever, a corrupção é descoberta, o DBCC CHECKDB será executado após o backup completo do banco de dados, mas não há uma maneira fácil de informar se a corrupção ocorreu antes do backup do banco de dados ou depois dela. Se a corrupção ocorreu algum tempo antes do backup do banco de dados, em seguida, o backup contém uma versão corrompida do banco de dados e recuperação será mais complicada.
A primeira coisa que faria é restaurar o backup do banco de dados mais recente para um local diferente e execute DBCC CHECKDB em relação a ele. Se nenhum dano for encontrado, você poderá restaurar sem perder os dados usando o procedimento a seguir:
- Um laço-de-a-log faça backup do banco de dados corrompido (para capturar as transações mais recentes).
- Restaure o backup completo do banco de dados mais recente, especificando WITH NORECOVERY.
- Restaure todos os backups do log de transações desde o backup completo do banco de dados e o backup do tail do log, na seqüência e com WITH NORECOVERY.
- Conclua a seqüência de restauração usando o comando RESTORE databasename WITH RECOVERY.
Finalmente, você deve executar outro DBCC CHECKDB para verificar a existência de nenhuma corrupção, executar algumas análises de causas básicas para descobrir o que causou a corrupção em primeiro lugar e, em seguida, executar etapas para corrigir o problema.
No caso de corrupção de existir após a seqüência de restauração acima, algo pode estar corrompido em um dos backups de log de transações ou possivelmente algo foi corrompido na memória e, em seguida, incluído no log de transação. Nesse caso, talvez seja necessário executar uma restauração point-in-time para localizar o horário em que a corrupção ocorreu e interromper a restauração antes dessa. O procedimento é além do escopo desta coluna, mas é abordada em detalhes em Manuais Online.
Se o backup mais recente do banco de dados contiver a corrupção, talvez seja necessário siga o procedimento acima, mas começando com o próximo backup completo do banco de dados mais recente. Isso pressupõe que você ainda terá-las disponíveis e todos os interveniente backups de log muito.
Uma estratégia alternativa (que você poderá ser forçado a usar para satisfazer um tempo de inatividade máximo permitido — ou RTO — restrição) pode ser a excluir os dados corrompidos, seja manualmente ou usando a funcionalidade de reparação em DBCC CHECKDB e tente recuperar algumas de um conjunto de backups mais antigo.
Recuperação de danos às vezes pode ser muito fácil e às vezes muito desafiadoras, dependendo o que aconteceu de errado e quais opções você ter disponível. Eu vai ser abrangendo isso em alguns artigos nos próximos meses alguns.
Pergunta: Nossa equipe de desenvolvimento irá criar uma solução que envolve o recurso de monitoramento de alterações do SQL Server 2008. Depois de ler a documentação, parece que deve habilitar o isolamento de instantâneo no banco de dados envolvido — mas estou preocupado com o impacto no desempenho. Você pode comentar sobre isso?
Resposta: Abordei em edição de novembro de 2008 de controle de alterações (“Controlando alterações no seu banco de dados empresarial"), mas sim, você precisa habilitar o controle de versão de linha. Isso ocorre porque o mecanismo para recuperar os dados alterados é geralmente o seguinte:
- Consulte o sistema de controle de alterações para encontrar qual tabela linhas foram alteradas.
- Consulte a tabela a mesmo para recuperar linhas alteradas.
Sem qualquer mecanismo de controle de versão de linha, a primeira consulta pode retornar resultados inválidos se o controle de alterações a tarefa de limpeza for executado enquanto a consulta está sendo executado. E a segunda consulta pode falhar se algumas linhas de tabela referenciadas nos resultados de consulta primeiro são excluídas antes de executa a segunda consulta.
É uma maneira de fornecer estabilização bloquear os dados de controle de alterações e as tabelas de usuário necessárias, mas isso leva a simultaneidade baixa (por meio de bloqueio) e o throughput de redução da carga de trabalho. A outra maneira de fornecer a estabilização é usar o isolamento de instantâneo.
Há dois tipos de isolamento de instantâneo — um que oferece consistência no nível de transação (opção do banco de dados: allow_snapshot_isolation) e o outro no nível de instrução T-SQL (opção do banco de dados: READ_COMMITTED_SNAPSHOT). A opção de nível de transação é aquele necessário para usar corretamente o controle de alterações e isso é simplesmente chamado de isolamento de instantâneo de .
Isolamento de instantâneo mantém versões de registros da tabela para que se, por exemplo, uma transação explícita for iniciado, a transação é garantida para ver uma visualização point-in-time consistente do banco de dados, como do ponto em que a transação começou. Para usar o controle de alterações, as duas consultas acima devem vir dentro de uma única transação explícita e o nível de isolamento deve ser definido como instantâneo; essa combinação garante consistência.
Isolamento de instantâneo é explicado em detalhes excelentes do minha esposa Kimberly white paper, “Isolamento de instantâneo do SQL Server 2005.”
Há dois problemas de desempenho possível com isolamento de instantâneo. A primeira é que todas as atualizações a todas as tabelas no banco de dados devem gerar versões de registros conforme eles alterá-los — mesmo se a versão nunca é usada. A versão pre-change do registro deve ser copiado para o armazenamento de versão e o novo registro tem um link ao que mais antigos — caso outra transação começa antes que isso for concluído e precisa a versão correta do registro. Isso adiciona algum processamento Comentário ouvido sobre a todas as operações de atualização.
O armazenamento de versão é dentro do banco de dados tempdb e este é o segundo problema de desempenho possível. Tempdb pode ser o banco de dados mais ocupado em algumas instâncias do SQL Server como ele é compartilhado entre todas as conexões e bancos de dados. Em geral, tempdb pode se tornar um gargalo de desempenho — mesmo sem controle de versão de linha. Adicionar controle de versão linha significa colocar ainda mais pressão em tempdb — em termos de espaço usado e as operações de I/O — que pode levar a degradação do throughput geral da carga de trabalho.
Você pode ler muito mais detalhes sobre este white paper, “Trabalhando com Tempdb no SQL Server 2005.” Embora ambos os white papers mencionados aqui foram escritos para o SQL Server 2005, eles se aplicam igualmente ao SQL Server 2008.
Pergunta: DBCC CHECKDB É uma verificação totalmente abrangente de todos os itens no banco de dados? Alguém me disse não é. Além disso, reparar pode corrigir tudo? Novamente, eu sido informou-não é possível. Há algo mais que posso fazer se o DBCC CHECKDB não é abrangente?
Resposta: A resposta é Sim e não! DBCC CHECKDB é um conjunto de verificações de consistência extremamente abrangente, e o conjunto de verificações realiza cresceu de versão para versão. Você está certo, no entanto, que existem algumas coisas que ele não valida.
Muito simplesmente, eis o que ele faz:
- Verificar catálogos de sistema para manter a consistência
- Verifique os metadados de alocação de consistência
- Verifique todas as tabelas de usuário para verificar a consistência
Uma descrição mais do que as verificações são executadas está além do escopo dessa resposta (mas você pode encontrar mais informações no meu blog ou no livro recente elementos internos do SQL Server 2008), mas todas as páginas do banco de dados que está sendo usada pelo menos é lido na memória e validadas. Isso irá capturar comuns corrupções causadas por falhas no subsistema de E/s (aproximadamente 99,99 % de todos os danos causados dessa maneira).
Os dois itens mais conhecidos que não são checked em qualquer versão do SQL Server são o conteúdo da coluna e índice principais estatísticas que estão armazenados no banco de dados, embora isso pode ser adicionado em uma versão futura e a validade das restrições (por exemplo, restrições de chave externa entre tabelas). Restrição de validade pode ser verificada usando o comando DBCC CHECKCONSTRAINTS separadamente do DBCC CHECKDB e na verdade, se você é obrigado a executar uma operação de reparo em um banco de dados que contém as restrições, é uma boa idéia de validar as restrições posteriormente reparos não têm restrições em consideração e podem invalidar inadvertidamente-los. Isso está documentado no Manuais Online.
O sistema de reparo não é possível corrigir tudo. Há alguns danos são impossíveis corrigir com uma garantia de precisão dentro de um tempo razoável. A lista de tais danos é muito pequena e é documentada na minha postagem de blog “CHECKDB From cada ângulo: CHECKDB pode reparar tudo?” Por exemplo, considere uma página em um catálogo de sistema corrompida — pode ser o reparo só é possível excluir a página. Mas e se a página é armazenar os metadados para algumas tabelas de usuário no banco de dados? Excluir página efetivamente excluiria dessas tabelas de usuário — portanto, essa reparação não pode ser executada.
Repara a maioria dos envolve a perda de dados de alguma maneira (pois esta é a única maneira de garantir a correção em um tempo razoável), portanto, o reparo deve ser visto somente como último recurso ao executar a recuperação de desastres. Usar os backups de uma estratégia abrangente de backup é a única maneira de evitar a perda de dados (a menos que algum tipo de síncrono cópia do banco de dados é mantido).
DBCC CHECKDB é abrangente para detectar danos prejudiciais e deve ser executado periodicamente como parte de uma estratégia de manutenção de banco de dados (consulte meu blog postar “Importância da execução de verificações de consistência regular"") para assegurar que os danos sejam encontrados assim que possível. Há nada que faz um trabalho mais abrangente, mas você pode aumentar a eficácia do DBCC CHECKDB, certificando-se de que possuir somas de verificação habilitadas em todos os bancos de dados. Isso permite que o SQL Server detectar quando algo foi alterado de uma página do banco de dados fora da memória do SQL Server.
Pergunta: Estou confuso sobre redução. Um artigo eu leia que diminuindo os arquivos de dados é bom e em outros lugares li que ele é inválido. A mesma coisa acontece ao tentar localizar informações sobre reduzindo os arquivos de log. Qual é a resposta?
Resposta: Encolhendo é uma operação muito mal compreendida e a diferença entre o arquivo de dados diminuindo e redução de arquivo de log é uma ótima causa confusão.
Uma operação de redução em um arquivo de dados tenta mover a página do banco de dados mais próximo do final do arquivo para baixo em direção ao início do arquivo. Isso cria espaço “ vazio ” no final do arquivo de dados que pode ser retornado para o sistema operacional. Em outras palavras, o arquivo de dados for diminuído fisicamente.
Uma operação de redução em um arquivo de log de transações, por outro lado, não move qualquer coisa — simplesmente remove a parte vazia do log de transações no final do arquivo, enquanto os registros de log de transação não estão sendo retidos por qualquer motivo. Se a operação for bem-sucedida, o arquivo de log for diminuído fisicamente.
Os efeitos colaterais de duas operações e quando deve ser executadas é proveniente de confusão.
Pessoas são aconselhadas (ou decidir) para reduzir os arquivos de dados para recuperar espaço. Pode ser que os arquivos de dados crescem, faz com que suas tarefas de manutenção de índice ou sua unidade está ficando cheia e a reação natural é recuperar alguns desse espaço “ desperdiçado ”. No entanto, é provável que esse espaço será necessário novamente e geralmente é melhor deixar o espaço livre restante no arquivo de dados em vez de reduzir repetidamente e crescimento automático de um arquivo.
Encolher um arquivo de dados deve ser uma operação muito rara porque tem um efeito colateral desagradável. Encolher um arquivo de dados faz com que a fragmentação do índice de massa, que pode afetar o desempenho da consulta. Minha postagem de blog “Por que você não deve reduzir seus arquivos de dados” inclui um script simples que mostra isso.
A postagem do blog também descreve quando é aceitável para encolher um arquivo de dados (quase nunca) e um método alternativo que evita o efeito colateral de fragmentação. Infelizmente já vi vários casos em que arquivo de dados diminuindo recomenda sem qualquer discussão dos efeitos colaterais.
Encolher um arquivo de log deve ser uma operação de redução de um arquivo de dados ainda mais rara. Normalmente, verá as pessoas que desejam reduzir um arquivo de log que ficou grande desproporcionalmente em comparação com os arquivos de dados por meio do gerenciamento de tamanho incorreto, ou porque eles verão ela crescer e quiser mantê-lo com o menor tamanho possível. É necessário que o arquivo de log para um banco de dados ativo seja um tamanho razoável, mas o log deve ser gerenciado para que não necessário ser reduzida e personalizados para acomodar a atividade no banco de dados.
Você pode descobrir mais sobre o log de transações no artigo Noções básicas sobre logs e recuperação que escrevi para edição de fevereiro de 2009 da revista. Também tenho uma postagem de blog que discute o gerenciamento do tamanho do log de transações — consulte “Importância do gerenciamento de tamanho de log de transações apropriado.”
O resultado final é que qualquer tipo de operação de redução deve ser excessivamente rara e apenas realizado quando as ramificações são totalmente entendidas.
Paul S. Randal I s Managing Director da SQLskills.com, diretor regional da Microsoft e um MVP do SQL Server. Ele trabalhou na equipe do mecanismo de armazenamento do SQL Server na Microsoft de 1999 a 2007. Paul escreveu o DBCC CHECKDB/repair para SQL Server 2005 e foi responsável pelo mecanismo de armazenamento principal durante o desenvolvimento do SQL Server 2008. Paul é especialista em recuperação de desastres, alta disponibilidade e manutenção de banco de dados e é apresentador regular em conferências em todo o mundo. He bloga em SQLskills.com/blogs/paul, e ele pode ser encontrado em Twitter no Twitter.com/PaulRandal.