SQL Server – O histórico de execução dos jobs do SQL Agent está sumindo?

Fala pessoal,
Tudo bem com vocês?

Hoje recebi uma dúvida bem comum que as pessoas tem ao utilizar os jobs do SQL Server Agent, que é quando o histórico de execução dos jobs do SQL Agent está sumindo após algum tempo. Você tem vários jobs executando e quando precisa analisar o histórico de um job específico, não mostra nada no histórico de execução. Isso já aconteceu com você?

A boa notícia é que entender e resolver esse problema é bem fácil.

Os parâmetros de retenção do histórico de jobs do SQL Server Agent

Para visualizar os parâmetros de retenção do histórico de jobs do SQL Server Agent, acesse as propriedades do SQL Server Agent.

E agora selecione a opção “History” no painel à esquerda.

Você verá algumas opções para gerenciamento do histórico. Confira se a opção “Limit size of job history log” está habilitada. Se não estiver, o banco irá armazenar o histórico dos jobs indefinidamente, exceto se a opção “Remove agent history” esteja habilitada. Neste caso, o histórico será apagado no intervalo definido quando você clicar em OK nesta tela.

Caso a opção “Limit size of job history log” esteja habilitada, que é o padrão, você deve observar os valores dos parâmetros “Maximum job history log size (in rows)” e “Maximum job history log per job”, cujos valores padrão são 1000 e 100, respectivamente.

E aqui que está possivelmente o seu problema, especialmente se você não alterou as configurações padrão.

Por quê o histórico de execução dos jobs do SQL Agent está sumindo?

Imagine que você tenha 20 jobs rodando na instância, e 10 deles sejam executados a cada 1 minuto e o restante sejam executados 1x por dia.

Em um intervalo de apenas 100 minutos (1h e 40 minutos), todos os jobs que são executados 1x por dia terão seu histórico de execução apagados, porque, embora o Agent esteja armazenando até 100 registros por job, devido ao parâmetro “Maximum job history log per job” = 100, o limite total de linhas de histórico “Maximum job history log size (in rows)” está definido como 1.000 registros, e esse segundo limite pode se sobrepor ao limite por job caso a quantidade total de linhas seja atingida.

Ou seja, se 10 jobs executam a cada 1 minuto, em 100 minutos eles já irão atingir o limite de 1.000 linhas de histórico e vão começar a sobrescrever os registros anteriores, e com isso, os jobs que executam só 1x por dia, acabam perdendo o seu histórico, porque eles serão sobrescritos pelos outros jobs, que são executados com maior frequência.

O cálculo é simples:
(quantidade de jobs na instância) x (limite de linhas por jobs) < (limite total de linhas do histórico)

Com as configurações padrão, num ambiente com 20 jobs:
20 x 100 teria que ser menor que 1000, mas isso é falso, logo, a configuração de 1000 linhas totais não é suficiente para um ambiente com 20 jobs e 100 linhas por job, pois irá ocorrer sobreposição do histórico dos jobs em algum momento.

Por mais que a configuração do agent limite que cada job possa armazenar até 100 linhas, esse limite não é garantido quando a quantidade de linhas total do histórico ultrapasse o limite geral do Agent (“Maximum job history log size (in rows)”)

Como aumentar a retenção do histórico de execução dos jobs do SQL Agent

Para resolver esse problema, é bem fácil: Basta alterar os parâmetros de retenção do histórico. O que eu costumo implementar nos ambientes, é utilizar o valor máximo permitido no parâmetro “Maximum job history log size (in rows)”, que é de 999999, e limito a quantidade de linhas por job em 1000.

Isso fará com que cada job consiga armazenar até 1.000 execuções por job e o limite máximo do Agent não irá sobrepor o valor deste parâmetro (exceto caso você tenha mais de 999 jobs na instância – nesse caso, reduza o limite máximo por job conforme a quantidade de jobs).

Quais as consequências de aumentar o tamanho do histórico dos jobs?

Agora que você aprendeu como aumentar a quantidade de linhas de histórico armazenadas, precisamos entender as consequências disso.

Tamanho em disco
O primeiro ponto que deve ser considerado é o espaço em disco que será necessário para armazenar esses dados, já que, como vocês devem saber, esse histórico fica armazenado no banco de dados msdb. Para uma quantidade de 1.000 registros na tabela de histórico, o tamanho médio ocupado é de cerca de 1 MB. Para o tamanho máximo permitido (999.999), deve ser necessário algo em torno de 1 GB de espaço em disco, o que não costuma ser algo muito significativo.

Locks
Outro ponto que deve ser considerado, é com relação a locks e potencial de queda de desempenho devido ao aumento do tamanho da base msdb. Para ambientes com muitos jobs, isso pode acabar vir a ser um problema, principalmente porque a cada execução de job, a procedure dbo.sp_agent_log_job_history é executada pelo SQL Agent para armazenar o log da execução, que internamente executa a msdb.dbo.sp_sqlagent_log_jobhistory e essa chama a msdb.dbo.sp_jobhistory_row_limiter.

A procedure sp_jobhistory_row_limiter possui uma verificação em que ela só é executada a cada execução de job na instância, caso aquele checkbox de limitar a quantidade de linhas (Limit size of job history log) esteja ativado:

Caso o checkbox esteja habilitado, a procedure sp_jobhistory_row_limiter será executada a cada execução de job, e ela inicia uma transação para contar quantas linhas de histórico na tabela msdb.dbo.sysjobhistory aquele job possui, utilizando um hint de WITH(TABLOCKX), travando a tabela inteira de modo exclusivo durante essa verificação e deleção dos registros que ultrapassaram o limite máximo total ou o limite máximo por job.

Por mais que isso seja bem rápido, se a instância tiver muitos jobs sendo executados ao mesmo tempo, e com a tabela de histórico maior (porque aumentou o limite de linhas totais), isso pode acabar gerando um problema de lock no gerenciamento do SQL Server Agent.

Jobs não sendo executados
Mais um problema que pode ocorrer, em ambientes com muitos jobs sendo executados ao mesmo tempo, é que pode acontecer de terem jobs não sendo executados no horário agendado devido ao problema anterior (locks), onde o Agent fica esperando por muito tempo para conseguir alocar a tabela e gravar os dados de execução do job, ocorre timeout e a execução do job retorna erro.

Uma alternativa melhor para apagar histórico de execução de jobs

Caso você prefira uma alternativa definitiva para esse problema, chegou ao lugar certo 🙂

• Se você tentar limitar o histórico de jobs por quantidade de linhas, usando a interface do SQL Agent, vai cair no problema de, ao habilitar a limitação por linhas, cada execução de job vai chamar a stored procedure sp_jobhistory_row_limiter, que pode gerar problemas de locks na tabela msdb.dbo.sysjobhistory devido ao uso do hint WITH(TABLOCKX) e, por isso, pode também prejudicar a execução dos jobs no horário correto em ambientes com muita execução de job simultaneamente.
• Se você marcar o checkbox na interface para remover o histórico de jobs mais antigos que um intervalo definido, esse processo é manual e vai ter que ficar voltando toda vez nessa tela para limpar os dados.
• E se você não ativar nenhum dos 2 checkboxes, os dados vão crescer indefinidamente, o que vai aumentar o espaço usado para armazenar esses dados e deixar as consultas na MSDB relacionadas à histórico de jobs mais lentas.

O que fazer para resolver isso?

Para resolver todos esses problemas de uma só vez, a melhor alternativa é DESATIVAR os 2 checkboxes na tela de retenção do histórico (isso mesmo, remover a limitação do histórico) e criar um job diário, que faça a limpeza dos logs de execução utilizando a stored procedure msdb.dbo.sp_purge_jobhistory, que irá apagar todo o histórico de execução, de todos os jobs, anteriores a uma data específica.

Exemplo de utilização:

O exemplo acima irá manter apenas o histórico de execução dos jobs dos últimos 180 dias e apagar os registros mais antigos que isso. Utilizando esse comando T-SQL, você não vai precisar se preocupar com quantidade de linhas para manter (o que nem faz muito sentido), se preocupando apenas com a quantidade de dias de histórico que vai manter.

Agora o SQL Server não irá executar a stored procedure sp_jobhistory_row_limiter a cada execução de job, evitando possíveis problemas de lock e você ainda terá controle sobre o crescimento do tamanho da tabela de histórico de jobs.

Você precisa fazer isso para todos os ambientes? Seria o ideal e o recomendável, mas se o seu ambiente não tem muitos jobs e nem muita concorrência, pode continuar deixando essa responsabilidade para o limitador do próprio SQL Server Agent, mas caso comece a dar problemas, não se esqueça de seguir esta dica, ok?

Espero que tenham gostado dessa dica rápida e até mais!