SQL Server

Principais dicas para uma manutenção eficiente do banco de dados

Paul S. Randal

Visão geral:

• Gerenciando os arquivos de dados e os arquivos de log de transações
• Eliminando a fragmentação do índice
• Garantindo estatísticas precisas e atualizadas
• Detectando páginas de banco de dados corrompidas
• Estabelecendo uma estratégia de backup efetiva

Sumário

Gerenciamento de arquivos de dados e de arquivos de log
Fragmentação do índice
Estatísticas
Detecção de danos
Backups
Conclusão

Várias vezes por semana me pedem ajuda quanto à forma de manutenção eficiente de um banco de dados de produção. Às vezes, as perguntas vêm dos DBAs que estão implementando novas soluções e precisam de

práticas de manutenção de ajuste fino adequadas às novas características dos bancos de dados. No entanto, as perguntas costumam vir mais de pessoas que não são DBAs profissionais, mas que, por uma razão ou outra, receberam a propriedade e a responsabilidade de um banco de dados. Gosto de chamar essa função de "DBA involuntário". O objetivo deste artigo é apresentar uma última palavra quanto às práticas recomendadas da manutenção de banco de dados aos DBAs involuntários que existem.

Assim como acontece com a maioria das tarefas e dos procedimentos no mundo da TI, não existe uma solução de tamanho único, simples, para uma manutenção efetiva de banco de dados, mas há algumas áreas principais que quase sempre precisam ser abordadas. As minhas cinco áreas de preocupação principais são (sem nenhuma ordem de importância específica):

• Gerenciamento de arquivos de dados e de arquivos de log
• Fragmentação do índice
• Estatísticas
• Detecção de danos
• Backups

Um banco de dados sem manutenção (ou com manutenção ruim) pode desenvolver problemas em uma ou mais dessas áreas, o que pode acabar resultando em mau desempenho do aplicativo ou até em tempo de inatividade e perda de dados.

Neste artigo, explicarei por que essas questões são importantes e mostrarei algumas formas simples de atenuar os problemas. Basearei as minhas explicações no SQL Server® 2005, mas também realçarei as principais diferenças encontradas no SQL Server 2000 e no futuro SQL Server 2008.

Gerenciamento de arquivos de dados e de arquivos de log

A primeira área que sempre recomendo verificar assim que alguém assume um banco de dados diz respeito às configurações relacionadas ao gerenciamento dos arquivos de dados e dos arquivos de log (de transações). Mais especificamente, você deve verificar se:

• Os arquivos de dados e os arquivos de log estão separados uns dos outros e isolados em relação a todo o resto
• O aumento automático está configurado corretamente
• A inicialização instantânea de arquivo está configurada
• A redução automática não está habilitada e se a redução não faz parte do plano de manutenção

Quando os arquivos de dados e os arquivos de log (que, em condições ideais, devem estar em volumes totalmente separados) compartilham um volume com outro aplicativo que cria ou expande arquivos, existe o potencial de fragmentação do arquivo. Em arquivos de dados, a fragmentação em excesso pode ser um pequeno fator de contribuição em consultas de mau desempenho (mais especificamente aquelas que examinam grandes quantidades de dados). Em arquivos de log, isso poderá ter um impacto muito mais significativo em termos de desempenho, em especial se o aumento automático estiver definido para aumentar muito pouco o tamanho de cada arquivo sempre que for necessário.

Internamente, os arquivos de log são divididos em seções chamadas VLFs (arquivos de log virtuais), e quanto mais fragmentação houver no arquivo de log (uso o singular aqui porque não há nenhum ganho em ter vários arquivos de log – deve haver apenas um por banco de dados), mais VLFs haverá. Dado um arquivo de log com mais de, digamos, 200 VLFs, o desempenho pode ser afetado negativamente em operações relacionadas a log como leituras de log (em replicações/reversões transacionais, por exemplo), backups de log e até mesmo gatilhos no SQL Server 2000 (a implementação dos gatilhos foi alterada no SQL Server 2005 para a estrutura do controle de versão em seqüência, no lugar do log de transações).

A prática recomendada em relação ao dimensionamento dos arquivos de dados e dos arquivos de log é criá-los com um tamanho inicial apropriado. Em arquivos de dados, o tamanho inicial deve levar em conta o potencial de adição de mais dados ao banco a curto prazo. Por exemplo, se o tamanho inicial dos dados for igual a 50 GB, mas você souber que nos próximos seis meses serão adicionados mais 50 GB de dados, faz sentido criar o arquivo de dados já com 100 GB, em vez de precisar aumentá-lo várias vezes até atingir esse tamanho.

Infelizmente, isso é um pouco mais complicado em arquivos de log, e você precisa considerar fatores como o tamanho da transação (transações demoradas não podem ser excluídas do log até que sejam concluídas) e a freqüência de backup do log (porque é isso o que remove a parte inativa do log). Para obter mais informações, consulte "8 etapas para uma maior produtividade dos logs de transações", uma postagem em blog conhecida em SQLskills.com escrita pela minha esposa, Kimberly Tripp.

Uma vez configurado, o tamanho dos arquivos deve ser monitorado em horários diferentes e aumentados de forma pró-ativa e manual em uma determinada hora do dia. O aumento automático deve permanecer ativado como uma proteção eventual para que os arquivos possam continuar crescendo no caso de algum evento anormal. A lógica contra deixar o gerenciamento de arquivos apenas como aumento automático é que o aumento automático de pequenas quantidades leva à fragmentação de arquivo, além de ser um processo demorado que paralisa a carga de trabalho do aplicativo em momentos imprevisíveis.

O aumento automático do tamanho deve ser definido como um valor específico, e não uma porcentagem, para associar a hora e o espaço necessários à sua realização, caso ele ocorra. Por exemplo, você talvez queira definir um arquivo de dados de 100 GB para ter um aumento automático fixo de 5 GB, em vez de, digamos, 10 por cento. Isso significa que ele sempre aumentará em 5 GB, não importando o tamanho final do arquivo, e não em uma quantidade sempre crescente (10 GB, 11 GB, 12 GB e assim por diante) sempre que o arquivo ficar maior.

Quando aumenta (manualmente ou pelo aumento automático), um log de transações é sempre inicializado com zero. Os arquivos de dados têm o mesmo comportamento padrão do SQL Server 2000, mas, desde o SQL Server 2005, é possível habilitar a inicialização de arquivo instantânea, que ignora a inicialização dos arquivos com zero e, assim, torna o aumento e o aumento automático praticamente instantâneos. Ao contrário do que as pessoas acreditam, esse recurso está disponível em todas as edições do SQL Server. Para obter mais informações, digite "inicialização de arquivo instantânea" no índice dos Manuais Online do SQL Server 2005 ou do SQL Server 2008.

Por fim, tome cuidado para não habilitar a redução de maneira alguma. A redução pode ser usada para diminuir o tamanho de um arquivo de dados ou de um arquivo de log, mas é um processo muito intrusivo, que demanda muitos recursos e causa grandes quantidades de fragmentação da verificação lógica em arquivos de dados (veja mais detalhes abaixo), além de levar a um mau desempenho. Alterei a entrada referente à redução nos Manuais Online do SQL Server 2005 para incluir um aviso quanto a esse efeito. No entanto, a redução manual dos arquivos de dados e dos arquivos de log individuais pode ser aceitável em circunstâncias especiais.

A redução automática é a pior causa porque começa a cada 30 minutos no segundo plano e tenta reduzir bancos de dados nos quais a opção de redução automática do banco de dados está definida como verdadeira. Trata-se de um processo, em certa medida, imprevisível porque só reduz bancos de dados com mais de 25 por cento de espaço livre. Como a redução automática usa muitos recursos e causa a fragmentação por queda de desempenho, esse não é um bom plano em nenhuma circunstância. Você deve sempre desativar a redução automática com:

ALTER DATABASE MyDatabase SET AUTO_SHRINK OFF;

Um plano de manutenção regular que inclua um comando manual de redução do banco de dados quase sempre é tão ruim quanto. Caso você ache que o banco de dados continua crescendo depois de ter sido reduzido pelo plano de manutenção, isso é porque o banco de dados precisa desse espaço para execução.

A melhor coisa a fazer é permitir que o banco de dados aumente até um tamanho permanente e evitar executar a redução. É possível encontrar mais informações sobre as desvantagens de usar a redução, além de alguns comentários sobre os novos algoritmos do SQL Server 2005 no meu velho blog do MSDN® em blogs.msdn.com/sqlserverstorageengine/archive/tags/Shrink/default.aspx.

Fragmentação do índice

Além da fragmentação no nível do sistema de arquivos e dentro do arquivo de log, também é possível que haja fragmentação nos arquivos de dados, nas estruturas que armazenam os dados de tabela e os dados de índice. Há dois tipos de fragmentação básicos que ocorrem dentro de um arquivo de dados:

• Fragmentação em páginas de dados e de índices individuais (às vezes, chamada de fragmentação interna)
• Fragmentação em estruturas de índice ou de tabela que consiste em páginas (chamada de fragmentação da verificação lógica e fragmentação da verificação de extensão)

A fragmentação interna é onde existe um grande espaço vazio em uma página. Como mostra a Figura 1, todas as páginas de um banco de dados têm 8 KB e um cabeçalho de 96 bytes. Dessa forma, uma página pode armazenar aproximadamente 8.096 bytes de dados de tabela ou dados de índice (as informações internas específicas do índice e da tabela relacionadas aos dados e às estruturas das linhas podem ser encontradas no meu blog em sqlskills.com/blogs/paul, na categoria Inside The Storage Engine). O espaço vazio pode ocorrer caso um dos registros da tabela ou do índice seja maior do que metade do tamanho de uma página, e apenas um registro possa ser armazenado por página. Isso pode ser bem difícil ou impossível de corrigir, pois exigiria uma alteração no esquema da tabela ou do índice, por exemplo, alterando uma chave de índice para algo que não causasse pontos de inserção aleatórios como faz o GUID.

Figura 1 A estrutura de uma página de banco de dados (clique na imagem para ampliá-la)

É mais comum que a fragmentação interna seja resultante de modificações feitas em dados como inserções, atualizações e exclusões, o que pode deixar um espaço vazio em uma página. Um fator de preenchimento mal gerenciado também pode contribuir para a fragmentação. Consulte os Manuais Online para obter mais detalhes. Dependendo do esquema de tabela/índice e das características do aplicativo, esse espaço vazio, depois de criado, talvez jamais seja reutilizado e pode levar a quantidades cada vez maiores de espaço inutilizável no banco de dados.

Considere, por exemplo, uma tabela com 100 milhões de linhas e um tamanho de registro médio de 400 bytes. Com o passar do tempo, o padrão de modificação dos dados do aplicativo deixa cada página com, em média, 2.800 bytes de espaço livre. O espaço total exigido pela tabela é de aproximadamente 59 GB, calculados como 8.096-2.800/400 = 13 registros por página de 8 KB e, em seguida, dividindo 100 milhões por 13 para obter o número de páginas. Se o espaço não estivesse sendo desperdiçado, caberiam 20 registros por página, reduzindo o espaço total obrigatório para 38 GB. É uma grande economia!

Assim, o espaço desperdiçado em páginas de dados/índice pode levar à necessidade de mais páginas para manter a mesma quantidade de dados. Isso não apenas exige mais espaço em disco, mas também significa que uma consulta precisa usar mais E/Ss para ler a mesma quantidade de dados. E todas essas páginas a mais ocupam mais espaço no cache de dados, o que consome mais memória do servidor.

A fragmentação da verificação lógica é causada por uma operação chamada divisão da página. Isso ocorre quando um registro precisa ser inserido em uma página de índice específica (de acordo com a definição da chave de índice), mas não há espaço suficiente na página para colocar os dados que estão sendo inseridos. A página é dividida ao meio e aproximadamente 50 por cento dos registros são movidos para uma página recém-alocada. Essa nova página não costuma ser fisicamente contígua à antiga e, por isso, é chamada de fragmentada. O conceito da fragmentação da verificação de extensão é parecido. A fragmentação nas estruturas de tabela/índice afeta a possibilidade de o SQL Server realizar verificações eficientes, independentemente de serem em tabela/índice completo ou associadas a uma cláusula WHERE de consulta (como SELECT * FROM MyTable WHERE Column1 > 100 AND Column1 < 4000).

A Figura 2 mostra páginas de índice recém-criadas com fator de preenchimento de 100 por cento e sem nenhuma fragmentação – as páginas estão cheias e a ordem física delas corresponde à ordem lógica. A Figura 3 mostra a fragmentação que pode ocorrer após inserções/atualizações/exclusões aleatórias.

Figura 2 Páginas de índice recém-criadas sem nenhuma fragmentação; páginas 100% cheias (clique na imagem para ampliá-la)

Figura 3 Páginas de índice que mostram as fragmentações da verificação interna e lógica após inserções, atualizações e exclusões aleatórias (clique na imagem para ampliá-la)

Às vezes, a fragmentação pode ser impedida pela alteração do esquema de tabela/índice, mas, como mencionei anteriormente, isso pode ser muito difícil ou impossível. Caso a prevenção não seja uma opção, há formas de remover a fragmentação depois que ela ocorreu – em especial, recompilando ou reorganizando um índice.

A recompilação de um índice envolve criar uma nova cópia do índice – bem compactado e o mais contíguo possível – e ignorar o antigo, fragmentado. Como o SQL Server cria uma nova cópia do índice antes de remover o antigo, ele exige um espaço livre nos arquivos de dados aproximadamente equivalente ao tamanho do índice. No SQL Server 2000, recompilar um índice sempre foi uma operação offline. No entanto, no SQL Server 2005 Enterprise Edition, a recompilação do índice pode acontecer online, com algumas restrições. Por outro lado, a reorganização usa um algoritmo in loco para compactar e desfragmentar o índice; ela exige apenas 8 KB de espaço adicional para ser executada – e a execução é sempre online. Na verdade, no SQL Server 2000, escrevi especificamente o código de reorganização do índice como uma alternativa online, que economiza espaço em disco, para recompilar um índice.

No SQL Server 2005, os comandos a serem analisados são ALTER INDEX … REBUILD para recompilar índices e ALTER INDEX … REORGANIZE para reorganizá-los. Essa sintaxe substitui os comandos DBCC DBREINDEX e DBCC INDEXDEFRAG do SQL Server 2000, respectivamente.

Existem muitas vantagens e desvantagens entre esses métodos, como a quantidade de registro em log de transações, a quantidade de espaço livre obrigatória no banco de dados e se o processo pode ser interrompido sem que haja perda do trabalho. Você encontrará um white paper que aborda esses fatores e muito mais em microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx. O documento se baseia no SQL Server 2000, mas os conceitos se aplicam facilmente às versões posteriores.

Algumas pessoas simplesmente optam por recompilar ou reorganizar todos os índices todas as noites ou todas as semanas (usando uma opção de plano de manutenção, por exemplo), em vez de imaginar quais são os índices fragmentados e se haverá algum benefício com a remoção da fragmentação. Embora essa possa ser uma boa solução para um DBA involuntário que só quer colocar as coisas em ordem com o mínimo de esforço, saiba que pode ser uma opção muito ruim para bancos de dados ou sistemas maiores nos quais os recursos são essenciais.

Uma abordagem mais sofisticada envolve usar a DMV sys.dm_db_index_physical_stats (ou DBCC SHOWCONTIG no SQL Server 2000) para determinar periodicamente quais são os índices fragmentados e, assim, escolher se e como trabalhar com eles. O white paper também aborda o uso dessas opções mais diretas. Além disso, é possível ver alguns códigos de exemplo sobre como fazer essa filtragem no Exemplo D da entrada nos Manuais Online referente à DMV sys.dm_db_index_physical_stats do SQL Server 2005 (msdn.microsoft.com/library/ms188917) ou no Exemplo E da entrada nos Manuais Online referente a DBCC SHOWCONTIG do SQL Server 2000 e versões posteriores (em msdn.microsoft.com/library/aa258803).

Independentemente do método que você usar, é altamente recomendável investigar e corrigir a fragmentação regularmente.

O Processador de Consultas faz parte do SQL Server que decide como uma consulta deve ser executada – mais especificamente, quais tabelas e índices usar e quais operações realizar neles para obter os resultados; isso se chama plano de consulta. Alguns dos dados mais importantes sobre esse processo de tomada de decisões são as estatísticas que descrevem a distribuição dos valores de dados das colunas dentro de uma tabela ou índice. Obviamente, as estatísticas precisam ser precisas e atualizadas para que sejam úteis ao Processador de Consultas; do contrário, podem ser escolhidos planos de consulta com mau desempenho.

As estatísticas são geradas com a leitura dos dados de tabela/índice e a determinação da distribuição dos dados para as colunas relevantes. As estatísticas podem ser criadas com a verificação de todos os valores de dados de uma determinada coluna (uma verificação completa), mas também podem se basear em uma porcentagem dos dados especificada pelo usuário (uma verificação de exemplo). Se a distribuição dos valores em uma coluna está bem equilibrada, uma verificação de exemplo deve ser o suficiente, e ela torna a criação e a atualização das estatísticas mais rápidas do que em uma verificação completa.

Observe que as estatísticas podem ser criadas e mantidas automaticamente ativando-se as opções de banco de dados AUTO_CREATE_STATISTICS e AUTO_UPDATE_STATISTICS, como mostra a Figura 4. Elas permanecem ativadas por padrão, mas se apenas herdou um banco de dados, você talvez queira vê-las apenas para ter certeza. Às vezes, as estatísticas podem ficar desatualizadas, caso em que é possível atualizá-las manualmente usando a operação UPDATE STATISTICS em determinados conjuntos de estatísticas. Como alternativa, pode ser usado o procedimento armazenado sp_updatestats, que atualiza todas as estatísticas desatualizadas (no SQL Server 2000, o sp_updatestats atualiza todas as estatísticas, não importa a idade).

Figura 4 Alterando as configurações do banco de dados por meio do SQL Server Management Studio (clique na imagem para ampliá-la)

Se você quiser atualizar as estatísticas como parte do plano de manutenção regular, existe um detalhe que deve ser observado. Tanto UPDATE STATISTICS quanto sp_updatestats usam como padrão o nível de amostragem especificado anteriormente (se houver) – e isso pode ser inferior a uma verificação completa. As recompilações de índice atualizam automaticamente as estatísticas com uma verificação completa. Caso você atualize manualmente as estatísticas após a recompilação de um índice, é possível acabar com estatísticas menos precisas! Isso pode acontecer caso uma verificação de exemplo da atualização manual substitua a verificação completa gerada pela recompilação do índice. Por outro lado, a reorganização de um índice não atualiza nenhuma estatística.

Mais uma vez, muitas pessoas têm um plano de manutenção que atualiza todas as estatísticas em um determinado ponto antes ou depois da recompilação de todos os índices – e, sem saber, acabam com estatísticas potencialmente menos precisas. Se você optar por apenas recompilar todos os índices com freqüência, isso também cuidará das estatísticas. Caso opte por uma rota mais complexa com remoção da fragmentação, você também deverá fazer isso para a manutenção de estatísticas. Sugiro o seguinte:

• Analise os índices e determine em quais deles trabalhar, além de como realizar a remoção da fragmentação.
• Em todos os índices que não foram recompilados, atualize as estatísticas.
• Atualize as estatísticas de todas as colunas não indexadas.

Para obter mais informações sobre as estatísticas, consulte o white paper "Estatísticas usadas pelo Otimizador de Consultas no Microsoft® SQL Server 2005" (microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx).

Detecção de danos

Já abordei a manutenção relacionada ao desempenho. Agora quero mudar a marcha e abordar a detecção e a atenuação de danos.

É muito improvável que o banco de dados que você está gerenciando contenha informações totalmente inúteis com as quais ninguém se preocupa. O que fazer então para garantir que os dados permaneçam perfeitos e recuperáveis no caso de um desastre? Os detalhes da elaboração de uma estratégia de recuperação de desastres completa e alta disponibilidade estão além do escopo deste artigo, mas você poderá começar com algumas medidas simples.

A esmagadora maioria dos danos é causada por "hardware". Por que entre aspas? Bem, hardware aqui é, de fato, uma abreviação de "algo no subsistema de E/S sob o SQL Server". O subsistema de E/S consiste em elementos como o sistema operacional, drivers do sistema de arquivos, drivers de dispositivo, controladores RAID, cabos, redes e as próprias unidades de disco. Há muitos lugares onde os problemas podem (e devem) ocorrer.

Um dos problemas mais comuns é quando ocorre uma falha de energia e uma unidade de disco está em meio à gravação de uma página do banco de dados. Se a unidade não conseguisse concluir a gravação antes de ficar sem energia (ou as operações fossem armazenadas em cache e não houvesse bateria reserva suficiente para liberar o cache da unidade), o resultado seria uma imagem de página incompleta no disco. Isso pode acontecer porque uma página de banco de dados com 8 KB é, na verdade, formada por 16 setores de disco contíguos de 512 bytes. Uma gravação incompleta poderá ter gravado em alguns dos setores da página nova, mas deixado alguns dos setores da imagem da página anterior. Essa situação é chamada de página interrompida. Como é possível detectar quando isso acontece?

O SQL Server conta com um mecanismo para detectar essa situação. Isso envolve o armazenamento de alguns bits de cada setor da página e a gravação de um padrão específico em seu lugar (isso acontece pouco antes de a página ser gravada em disco). Se o padrão não for o mesmo quando a página for lida novamente, o SQL Server saberá que ela estava "interrompida" e irá gerar um erro.

No SQL Server 2005 e versões posteriores, está disponível um mecanismo muito mais abrangente chamado soma de verificação de página, capaz de detectar qualquer dano em uma página. Isso envolve a gravação de uma soma de verificação de página inteira na página um pouco antes de ela ser gravada e, em seguida, o teste quando a página é lida novamente, como acontece com a detecção de página interrompida. Após a habilitação das somas de verificação de página, uma página precisa ser lida no pool de buffers, alterada de alguma forma e, em seguida, gravada em disco novamente antes de ser protegida por uma soma de verificação da página.

Portanto, trata-se de uma prática recomendada manter as somas de verificação habilitadas no SQL Server 2005 em diante, com a detecção de página interrompida habilitada no SQL Server 2000. Para habilitar somas de verificação de página, use:

ALTER DATABASE MyDatabase SET PAGE_VERIFY CHECKSUM;

Para habilitar a detecção de página interrompida no SQL Server 2000, use:

ALTER DATABASE MyDatabase SET TORN_PAGE_DETECTION ON;

Esses mecanismos permitem detectar quando uma página apresenta um dano, mas somente quando ela é lida. De que forma é possível forçar facilmente a leitura de todas as páginas alocadas? O melhor método para fazer isso (e encontrar outros tipos de dano) é usar o comando DBCC CHECKDB. Independentemente das opções especificadas, esse comando sempre irá ler todas as páginas do banco de dados, o que faz com que todas as somas de verificação de página ou detecção de página interrompida sejam verificadas. Você também deve configurar alertas para saber quando os usuários enfrentam problemas de danos durante a execução de consultas. É possível ser notificado de todos os problemas descritos acima usando um alerta para erros de severidade 24 (Figura 5).

Figura 5 Configurando um alerta para todos os erros de severidade 24 (clique na imagem para ampliá-la)

Portanto, outra prática recomendada é executar regularmente DBCC CHECKDB nos bancos de dados para verificar sua integridade. Há muitas variações desse comando e dúvidas quanto à freqüência de sua execução. Infelizmente, não há nenhum white paper disponível que aborde isso. No entanto, como DBCC CHECKDB foi o principal trecho de código que escrevi para o SQL Server 2005, bloguei muito a respeito dele. Consulte a categoria "CHECKDB From Every Angle" no meu blog (sqlskills.com/blogs/paul) para obter vários artigos detalhados sobre verificação de consistência, práticas recomendadas e dicas práticas. Para DBAs involuntários, a regra geral é executar um DBCC CHECKDB sempre que você faz um backup completo do banco de dados (mais detalhes a seguir). Recomendo a execução do seguinte comando:

DBCC CHECKDB ('MyDatabase') WITH NO_INFOMSGS, 
  ALL_ERRORMSGS;

Se houver qualquer saída desse comando, o DBCC terá encontrado danos no banco de dados. Então a pergunta passa a ser o que fazer caso o DBCC CHECKDB encontre um dano. É onde surgem os backups.

Quando ocorre um dano ou outro desastre qualquer, a forma mais eficiente de recuperação é recuperar o banco de dados usando backups. Agora, isso inicialmente pressupõe que você tenha backups e que eles próprios não estejam corrompidos. É muito comum que as pessoas queiram saber como recolocar um banco de dados seriamente corrompido em execução quando não têm um backup. A resposta é simples: não é possível, não sem experimentar alguma forma de perda de dados que pudesse gerar confusão em relação à lógica de negócios e à integridade dos dados relacionais.

Portanto, existe uma ótima situação para fazer backups regulares. As complicações do uso do backup e da restauração estão muito além do escopo deste artigo, mas me permita dar uma palavra final quanto ao estabelecimento de uma estratégia de backup.

Primeiro, você deve fazer backups regulares completos do banco de dados. Isso proporciona um ponto único que você poderá restaurar posteriormente. É possível fazer um backup completo do banco de dados usando o comando BACKUP DATABASE. Examine os Manuais Online em busca de exemplos. Tendo em vista uma maior proteção, é possível usar a opção WITH CHECKSUM, que verifica as somas de verificação (caso estejam presentes) das páginas lidas e calcula a soma de verificação em relação ao backup inteiro. Você deve optar por uma freqüência que reflita a margem de perda de dados ou de trabalho com a qual a empresa se sente confortável. Por exemplo, fazer um backup completo do banco de dados uma vez por dia significa que você pode perder até um dia de dados em caso de desastre. Caso só esteja usando backups completos de bancos de dados, você deve estar no modelo de recuperação SIMPLES (normalmente chamado de modo de recuperação) para evitar as complicações relacionadas ao gerenciamento do aumento do log de transações.

Segundo, sempre mantenha os backups por alguns dias caso haja danos em um – um backup feito há alguns dias é melhor do que nada. Você também deve verificar a integridade dos backups usando o comando RESTORE WITH VERIFYONLY (novamente, consulte os Manuais Online). Se você estiver usado a opção WITH CHECKSUM quando o backup for criado, a execução do comando de verificação confirmará se a soma de verificação do backup continua válida, além de verificar novamente todas as somas de verificação das páginas do backup.

Terceiro, caso um backup completo diário do banco de dados não permita encontrar a perda máxima de dados/trabalho que a empresa pode suportar, você talvez precise analisar backups de banco de dados diferenciais. Um backup de banco de dados diferencial se baseia em um backup de banco de dados completo e contém um registro de todas as alterações feitas desde o último backup completo do banco de dados (um equívoco comum é achar que os bancos de dados diferenciais são incrementais; eles não são). Uma estratégia simples pode ser fazer o backup completo diário de um banco de dados com um backup diferencial a cada quatro horas. Um backup diferencial fornece uma única opção de recuperação pontual a mais. Caso só esteja usando backups completos de bancos de dados, você deve continuar usando o modelo de recuperação SIMPLES.

Por fim, a última palavra em termos de recuperação vem do uso dos backups de log. Eles só estão disponíveis nos modelos de recuperação COMPLETOS (ou BULK_LOGGED) e fornecem um backup de todos os registros em log gerados desde o backup do log anterior. A manutenção de um conjunto de backups de log com backups completos de banco de dados (e talvez do banco de dados diferencial) garante um número ilimitado de pontos de recuperação – inclusive a recuperação minuto a minuto. O dilema é que o log de transações continuará aumentando até ser "liberado" por um backup de log. Uma estratégia de exemplo aqui seria um backup completo do banco de dados todos os dias, um backup do banco de dados diferencial a cada quatro horas e um backup de log a cada meia hora.

Decidir uma estratégia de backup e configurá-la pode ser complicado. Na pior das hipóteses, você deve contar com um backup completo de banco de dados regular para garantir que tenha, pelo menos, um ponto de recuperação.

Como se pode ver, para garantir a integridade e a disponibilidade do banco de dados, existem algumas tarefas "a serem cumpridas". Esta é a minha lista de verificação final para um DBA involuntário assumindo um banco de dados:

• Remova a fragmentação excessiva do arquivo de log de transações.
• Defina corretamente o aumento automático.
• Desative todas as operações de redução agendadas.
• Ative a inicialização de arquivo instantânea.
• Estabeleça um processo regular para detectar e remover a fragmentação do índice.
• Ative AUTO_CREATE_STATISTICS e AUTO_UPDATE_STATISTICS, além de ter um processo regular para atualizar estatísticas.
• Ative as somas de verificação de página (ou pelo menos a detecção de página interrompida no SQL Server 2000).
• Mantenha um processo regular para executar DBCC CHECKDB.
• Mantenha um processo regular para fazer backups completos do banco de dados, além de backups de log e diferenciais para recuperação pontual.

Usei comandos T-SQL no artigo, mas é possível fazer muito com o Management Studio também. Espero ter apresentado alguns ponteiros úteis para uma manutenção eficiente de bancos de dados. Em caso de dúvidas ou comentários, me escreva – paul@sqlskills.com.

Paul S. Randal é diretor administrativo da SQLskills.com e MVP em SQL Server. Trabalhou na equipe do mecanismo de armazenamento do SQL Server da Microsoft de 1999 a 2007. Paul é especialista em recuperação de desastres, alta disponibilidade e manutenção de bancos de dados. Ele bloga em SQLskills.com/blogs/paul.

© 2008 Microsoft Corporation e CMP Media, LLC. Todos os direitos reservados; é proibida a reprodução total ou parcial sem permissão.