SQL Server: Reduzindo o tamanho do banco de dados "msdb"

Artigo
17/01/2024

Introdução

Este artigo apresenta um procedimento para limpeza das tabelas de log, utilizadas para armazenar informações de Jobs em execução no SQL Agent e dos resultados das tarefas processadas nos Planos de Manutenção ativas na instância SQL Server.

Entendendo o problema

No caso apresentado neste artigo, a instância SQL Server possui uma pequena quantidade de Jobs ativos, porém com uma frequencia de execução muito elevada. Em alguns casos, estes Jobs estão agendados com intervalos de "minutos" de execução, aumentando considerávelmente o tamanho do banco de dados de sistema "msdb".

O crescimento do banco msdb acabou se tornando um transtorno, uma vez que ele utilizou todo o espaço disponível no volume onde estava armazenado, impedindo a manipulação de dados neste banco e nos outros bancos de dados de sistema, que também compartilham o mesmo local físico. Neste momento, não há espaço livre no banco de dados, tando no arquivo .MDF como no .LDF e a alternativa neste caso é identificar o que está mais consumindo o espaço deste banco de dados e se é possível efetuar um expurgo destas informações.

Após um breve levantamento, identificamos duas tabelas que são as mais afetadas com este processo: sysmaintplan_log e sysmaintplan_logdetail, respectivamente como o próprio nome indica, uma tabela complementa às informações da outra com detalhes sobre o que foi executado.

Veja abaixo a quantidade de registros das tabelas do banco de dados "msdb":

Executando a limpeza do Log

A limpeza destas tabelas não interfece no bom funcionamento da instância SQL e a única restrição fica por conta dos seus relacionamentos que "enforcam" a manipulação de dados para preservar a integridade do banco de dados "msdb" .

Neste contexto, vamos remover dentro de uma transação as constraint´s que impossibilitam a limpeza destas tabelas e, após limpar o conteúdo, recriar as contraint's.

Atenção
Antes de iniciar este procedimento no banco de dados "msdb" para modificar qualquer um de seus objetos, faça um backup FULL como medida de segurança. Se possível, efetue antes a execução deste procedimento em um ambiente semelhante à sua instância SQL Server que apresenta o problema.

Veja a imagem desta execução:

Segue abaixo o script T-SQL para efetuar a limpeza do Log:

USE msdb
GO

--Criando uma transação para executar esta tarefa
BEGIN TRAN

--Remove temporariamente a "constraint" para limpeza das tabelas
ALTER TABLE sysmaintplan_log DROP CONSTRAINT FK_sysmaintplan_log_subplan_id;
ALTER TABLE sysmaintplan_logdetail DROP CONSTRAINT FK_sysmaintplan_log_detail_task_id;
GO

--Limpando as tabelas
TRUNCATE TABLE sysmaintplan_logdetail;
TRUNCATE TABLE sysmaintplan_log;
GO

--Recriando as "constraint's" para preservar a integridade dos dados
ALTER TABLE sysmaintplan_log WITH CHECK
ADD CONSTRAINT FK_sysmaintplan_log_subplan_id FOREIGN KEY(subplan_id)
REFERENCES sysmaintplan_subplans (subplan_id);

ALTER TABLE sysmaintplan_logdetail WITH CHECK
ADD CONSTRAINT FK_sysmaintplan_log_detail_task_id FOREIGN KEY(task_detail_id)
REFERENCES sysmaintplan_log (task_detail_id) ON DELETE CASCADE;
GO

IF @@ERROR = 0
COMMIT TRAN
ELSE
ROLLBACK TRAN
GO

Após a execução deste script T-SQL, as tabelas são reduzidas e disponibilizam o espaço necessário para compactar e liberar o espaço para retomar a execução dos Jobs em Produção.

Na imagem abaixo, é possível ver que em poucos segundos a tabela já possui novos registros:

Compactando o msdb

A liberação do espaço deste volume de volta para uso do sistema operacional, agora é possível com a execução do comando DBCC SHRINKDATABASE ou SHRINKFILE normalmente para reestabelecer a operação dos bancos de dados de sistema nesta instância SQL Server.

Segue abaixo o script T-SQL para esta compactação:

USE msdb

DBCC SHRINKFILE(MSDBLog, 512)

DBCC SHRINKFILE(MSDBData, 1024)

Após a execução o banco de dados "msdb" é compactado.

É importante lembrar que pode ser necessário efetuar a manutenção de índices neste banco, evitando lentidão na manipulação dos seus dados.

Medida Preventiva

Para evitar que ocorra um caso semelhante em servidores de Produção com alta incidência de execuções de Jobs e Planos de Manutenção, é altamente recomendável que seja controlado o conteúdo de Log que deve ser armazenado.

Uma forma de controlar este Log é definindo nas propriedades do SQL Agent, na aba "History", onde podemos definir um limite de registros à ser armazenado através da opção "Limit size of job history log" e/ou a quantidade de "dias" ou "semanas" ou "meses" que o log deve permanecer antes de ser descartado, definido na opção "Remove agent history". É possível realizar uma combinação de ambas as opções, deixando o "ajuste fino" dos dados, somente permanecendo o que realmente é ideal para o monitoramento do fluxo das informações.

Veja na imagem abaixo:

Conclusão

É importante efetuar um levantamento de como cada Job poderá atuar e seu consequente crescimento, para evitar lentidão ou até a "parada" de uma instância SQL.

Definir os procedimentos para gerenciar uma instância SQL é uma Boa Prática, mas é importante definir os limites adequados para armazenamento de Logs, com intuito de evitar transtornos por um aumento elevado na carga de execução dos seus Jobs, de forma inesperada.

Compartilhar via