Уменьшаем размер MSDB

10 марта 2023 mssql

Размер системной базы msdb стал больше 11 гб, настало время ее почистить.

Для начала, посмотрим какая таблица сколько места занимает:

USE MSDB
select t.name as TableName,Min(t.create_date) as CreateDate,ds.name as FileGroupName, SUM(u.total_pages)*8/1024 as SizeMB
from sys.tables as t
inner join sys.partitions as p on t.object_id=p.object_id
inner join sys.allocation_units as u on p.partition_id=u.container_id
inner join sys.data_spaces as ds on u.data_space_id=ds.data_space_id
group by t.name, ds.name
order by sizemb desc

В моем случае это была таблица sysmaintplan_logdetail. Около 10 гб.

Чистим:

USE MSDB
ALTER TABLE [dbo].[sysmaintplan_log] DROP CONSTRAINT [FK_sysmaintplan_log_subplan_id];
ALTER TABLE [dbo].[sysmaintplan_logdetail] DROP CONSTRAINT [FK_sysmaintplan_log_detail_task_id];
truncate table msdb.dbo.sysmaintplan_logdetail;
truncate table msdb.dbo.sysmaintplan_log;
ALTER TABLE [dbo].[sysmaintplan_log] WITH CHECK ADD CONSTRAINT [FK_sysmaintplan_log_subplan_id] FOREIGN KEY([subplan_id])
REFERENCES [dbo].[sysmaintplan_subplans] ([subplan_id]);
ALTER TABLE [dbo].[sysmaintplan_logdetail] WITH CHECK ADD CONSTRAINT [FK_sysmaintplan_log_detail_task_id] FOREIGN KEY([task_detail_id])
REFERENCES [dbo].[sysmaintplan_log] ([task_detail_id]) ON DELETE CASCADE;

После этого обязательно сжимаем базу:

DBCC SHRINKFILE (MSDBData, 512)

Проверяем результат:

SELECT name, size = size * 8. / 1024, space_used = FILEPROPERTY(name, 'SpaceUsed') * 8. / 1024
FROM sys.database_files

И добавить еще очистку логов джобов и бекапов:

DECLARE @DateBefore DATETIME 
SET @DateBefore = DATEADD(DAY, -7, GETDATE())

EXEC msdb.dbo.sp_purge_jobhistory @oldest_date = @DateBefore

DECLARE @DateBefore DATETIME 
SET @DateBefore = DATEADD(DAY, -60, GETDATE())

EXEC msdb.dbo.sp_delete_backuphistory @oldest_date = @DateBefore