Expurgando dados do SSISDB
Algumas semanas, tive uma nova situação que gostaria de compartilhar com vocês. É uma daquelas que pode começar com uma dorzinha de cabeça a curto-médio prazo e virar um dor futuramente, que é o alto consumo em disco gerado pelo SSISDB.
Para quem não conhece, o SSISDB é o banco de dados utilizado pela feature SQL Server , o Integration Services (SSIS) e este banco de dados do qual falo, aparece após a criação do catálogo de integrações (Integration Services Catalogs). Esse banco de dados armazena diversas informações e principalmente os logs de execução dos pacotes SSIS. Este banco pode crescer por vários motivos, seja pela quantidade de execuções e/ou quantidade de operações realizadas por execução + warnings que podem existir no(s) seu(s) pacote(s) nas execuções.
Vamos ao caso: Herdei uma instância de produção, já em funcionamento e o banco de dados estava com quase 1TB e com as configurações padrões (Clean Logs Periodically = False e Retention Period (days) = 365). Para estancar a sangria, precisei configurar uma instância de homologação, configurar todo o cenário para execução dos pacotes do SSIS (lembrando que o cenário pode variar de projeto para projeto), exportar o pacote da instância de produção, importar o pacote na instância de homologação, validar a execução do pacote, efetuar a “cirurgia” de deletar o catálogo e recriá-lo novamente, importar os pacotes, configurar as retenções do catálogo e validar pacote por pacote. O resultado foi de uma base que estava com quase 1TB, para 10GB.
Para iniciar o hands-on desse post e comentar mais algumas coisas que fiz, duas coisas são fundamentais para continuarmos:
- Definir um Retention Period para um período que você deseja armazenar de histórico (padrão é 365 dias);
- Habilitar a opção Clean logs Periodically;
Ou podemos executar o seguinte comando abaixo, informando a quantidade de dias desejado na variável @QtdDay e voilá, tudo resolvido!
DECLARE @QtdDay SMALLINT = 4;
EXECUTE [SSISDB].[catalog].[configure_catalog] @property_name = N’OPERATION_CLEANUP_ENABLED’, @property_value = N’TRUE’;
EXECUTE [SSISDB].[catalog].[configure_catalog] @property_name = N’RETENTION_WINDOW’, @property_value = @QtdDay;
Por padrão, ao criar o catálogo do SSIS, é criado automaticamente o job SSIS Server Maintenance Job para efetuar as manutenções do SSISDB.
Sobre este job, recomendo mantê-lo ativo e configurar a execução no agendamento para um período de menor uso da Feature. Somente usar esse job não resolveu muito a minha situação, infelizmente. Durante algumas pesquisas, me deparei com um post do Tim Mitchell que me deu um baita norte de como manter esta base controlada. Como o script utiliza de DELETES e dependendo do quão grande está o seu SSISDB, o script de housekeeping ou expurgo (como preferir chamar) pode demorar eternidades ou até você nem conseguirá executar, sem contar o quanto de transaction log ele pode consumir, que foi o que aconteceu durante os meus testes com o original. Diante disso, fiz uma versão com algumas alterações para atender a minha necessidade. Para acessar a versão que escrevi, clique aqui.
Sobre o script, ele continua com a ideia original do autor, utilizando o período de retenção configurado e a permissão de limpeza que falamos anteriormente. Para elaborar mais ele, adicionei tratamentos de erro, transações explícitas, whiles, tabela temporária global (##table) para acompanhamento do processo por execution_id e contadores de cada tabela envolvida, acompanhado de uma varíavel (@QtdDeletedRowToDeletePerCycle) com valor padrão de 1000 para o uso da cláusula TOP. Tudo isso dentro de uma procedure para que você possa efetuar os deletes de quantidade em quantidade que você desejar.
Pontos importantes e mais dicas:
- Busque um equilibrio de uma quantidade para deletar na variável. O quanto for parametrizado definirá o tempo que levará para concluir o processo, bem como o quanto do seu transaction log será sugado e o quão rápido acontecerá. Ou seja, teste;
- Caso este script esteja rodando e for executado algum pacote do SSIS, a execução do pacote falhará. Ou seja, execute em um horário de maior folga;
- O autor original cita que o desenvolvimento do script foi feito em um SQL Server 2017, mas executei no SQL Sever 2016 SP2 e obtive sucesso. Portanto, valide em um ambiente de testes seu antes de rodar ele em produção;
- Faça backups do banco SSISDB como qualquer outro banco de dados, ele é tão importante quanto os outros;
- É possível comprimir este banco de dados. Uma sugestão de script para tal, é o do Dirceu Resende. Uso e recomendo;
- Muito importante também, é a análise do(s) pacote(s) para entendimento do porque de gerar tanta infomação;
Espero que este post seja útil para você e obrigado pela leitura.
Referências: