How To Fix Very Large MSDB Database

The MSDB database houses your maintenance plan information and job histories (amongst a few other things). For the most part this should generally remain well under 1GB in size (normally only a few hundred MB)… but there are certain conditions where the history clean up task can get a bit overwhelmed and start failing, and leave you with an MSDB database that’s starting to grow a bit out of control.

Generally running a clean up history task will resolve this, however in the event that this is failing also, you can try the following scripts to fix the issue.

How to check and fix a large MSDB database – first check what’s using the space;

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

SELECT object_name(i.object_id) as objectName, i.[name] as indexName, sum(a.total_pages) as totalPages, sum(a.used_pages) as usedPages, sum(a.data_pages) as dataPages, (sum(a.total_pages) * 8) / 1024 as totalSpaceMB, (sum(a.used_pages) * 8) / 1024 as usedSpaceMB, (sum(a.data_pages) * 8) / 1024 as dataSpaceMB FROM sys.indexes i INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id GROUP BY i.object_id, i.index_id, i.[name] ORDER BY sum(a.total_pages) DESC, object_name(i.object_id) GO

Chances are it will be the sysmaintplan_logdetail table that’s blown out, so to resolve this;

1 2 3 4 5 6 7 8

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;

Then we shrink it down;

1 2 3 4 5 6 7 8 9 10

--- SHRINK THE MSDB LOG FILE USE MSDB GO DBCC SHRINKFILE(MSDBLog, 512) GO -- SHRINK THE MSDB Data File USE MSDB GO DBCC SHRINKFILE(MSDBData, 1024) GO

… rebuild the indexes to make sure it’s all performing nicely;

1 2 3 4

USE MSDB GO EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)" GO

and BOOM! MSDB should be back under control, and your scheduled history clean-up task capable of completing successfully again!