Data

27 mar, 2017

Quando o MSDB cresce demais

Publicidade

No SQL Server, as bases de sistema MASTER, MSDB, MODEL costumam ser bem pequenas, na maioria das vezes, menores que 10 Gb. O TEMPDB é uma exceção, porque, como todos sabem, ele é compartilhado entre todas as bases da instância para execução de várias operações.

Mas, às vezes, uma falha no planejamento dos jobs da instância causa um problema que pode se tornar sério e derrubar a base MSDB! Este é um caso que já vivenciei com instâncias legado e que precisou ser resolvido com rapidez.

Problema

O ambiente daquela empresa era monitorado para alertar sobre datafiles que estivessem próximos do seu tamanho máximo. E o alerta apontava para o MSDB, onde os datafiles estavam perigosamente próximos do limite.

Por segurança, adotei de imediato uma solução paliativa, aumentando o limite de crescimento dos datafiles do MSDB. Assim, tive tempo de analisar o problema com mais calma.

Para iniciar a pesquisa, gerei um relatório padrão do SQL Server para identificar quais tabelas estavam ocupando tanto espaço. Para isso, basta abrir o SQL Management Studio, clicar com botão direito sobre o nome do banco, escolher as opções:

…RELATÓRIOS

…RELATÓRIOS PADRÃO

…USO DO DISCO POR TABELAS PRINCIPAIS

A tela a seguir mostra essa seleção:

Como suspeitava, o relatório mostrou que a tabela que havia crescido absurdamente era  dbo.sysjobhistory. Essa tabela de sistema registra o histórico das execuções de cada job da instância e traz informações muito importantes para avaliação de sua operação. Através dela podemos calcular, por exemplo, qual o tempo médio de execução de cada passo do job!

Em seguida, fiz uma consulta nessa tabela para verificar quais jobs apresentavam mais registros, ou seja, aqueles que estavam forçando o crescimento da tabela e da base de dados. Isso geralmente está ligado à frequência de execução do job e/ou ao seu tamanho, isto é, quantas operações ou passos eram executados.

Com estas informações, concluí que aconteceu nessa instância a combinação de dois eventos conflitantes:

  1. Foram criados vários jobs que rodavam com uma frequência muito grande. Observe que um único job que seja agendado para rodar a cada 10 segundos vai gerar 8640 registros por dia, ou mais de 3 milhões de registros em um ano.
  2. Ninguém se lembrou de agendar uma operação para limpar o histórico de jobs.

Uma avaliação mais detalhada mostrou que os principais vilões eram 3 objetos, os quais rodavam com intervalos de segundos entre cada execução.

Solução

A solução definitiva claramente exigia resposta para três perguntas:

  • Era necessário manter a frequência de execução destes jobs em questão?
  • Qual o período de tempo que precisaria ser mantido no histórico do jobs?
  • Qual o tamanho máximo para os datafiles do MSDB considerando as duas respostas acima?

Decidimos que não alteraríamos a frequência de execução. Portanto nem precisamos envolver os proprietários dos jobs neste trabalho.

Em segundo lugar, decidimos que iríamos manter um histórico de 180 dias de operação. Nunca havia ocorrido na empresa uma solicitação que precisasse informação para período superior a 90 dias, mas optamos por um período maior por questão de segurança.

Por fim, resolvemos ampliar o tamanho máximo dos datafiles do MSDB para 20 Gb, o que seria suficiente para atender todos os requisitos mencionados e ter folga suficiente para a implementação de outros jobs de alta frequência.

Implementação

Cabia, então, implementar duas melhorias:

  • Aumentar o tamanho do datafile do MSDB
  • Criar o job de limpeza de histórico de execução de jobs

Aumentar limite de datafiles é simples. Uma simples instrução resolve o problema.

ALTER DATABASE [MSDB] MODIFY FILE (NAME=N'MSDBDATA' , MAXSIZE=20GB)

Para a segunda ação, usei o procedimento sp_purge_jobhistory. Ela usa 2 argumentos:

  • @job_id ou @job_name para especificar o objeto a ser tratado; se o valor for NULL, então todos os jobs serão incluídos no expurgo.
  • @oldest_date informa a data limite para manter no histórico. Ou seja, serão excluídos registros com data menor do que @oldest_date. Se o valor for NULL, todo histórico será eliminado.

Portanto, a operação, no caso, usava o seguinte script:

DECLARE @dtLimite DATE
SET @dtLimite = DATEADD(D, -180, GETDATE())
EXEC sp_purge_jobhistory @oldest_date = @dtLimite

Finalmente, criei um job de um único passo que executava o script acima, agendado para execução diária.

Comentários finais

É comum encontrar empresas que se esquecem de implementar as rotinas apropriadas de manutenção de bases de dados em ambiente de produção.

Essas rotinas são tão importantes que estão, em sua grande maioria, disponíveis como tarefas padrão dos Planos de Manutenção do SQL Server. A tarefa descrita acima, por exemplo, está incluída na operação TAREFA LIMPEZA DE HISTÓRICO.

Por sinal, esta tarefa executa também a limpeza do histórico de backups. Esta é uma manutenção importante, especialmente no caso de empresas em que usam backups de log com muita frequência.

Caso prefira fazer o processo através de T-SQL, basta adaptar o processo descrito para criação do job de limpeza do histórico de jobs, passando a usar o procedimento apropriado (que neste caso é procedimento MSDB.dbo.sp_delete_backuphistory).

De todo modo, o problema de crescimento exagerado do MSDB é comum em ambientes de produção e não deve ser negligenciado. Como eu comentei, o caso que descrevi foi causado por uma combinação de fatores. Porém é importante saber como detectar o problema e corrigi-lo.

Quando isso acontecer, espero que o processo descrito aqui possa ajudá-lo.