The MSDB database is one of the most important tools that SQL support services have at their fingertips. This is where server agents store system activities like maintenance plans, backup histories, as well as other similar plans. These systems are indispensable during emergencies, which makes their continuous operation a constant concern for the people in charge of the servers.
Generally, the amount of data that stays in the MSDB database remains well under a single gigabyte. But, there will be times when the storage starts getting a bit too crowded, and the database begins growing a bit out of control. This usually happens through neglect, which a simple cleaning history task can resolve. The real problem begins when even the clean history task gets overwhelmed and fails.
Expert Diagnosis and Remedies
This is where SQL server support agencies, such as DBA Services step in and fix everything, since they’re the only people who understand what’s going on. The process itself is simple enough to diagnose and remedy, as it’s a straightforward scripting issue.
The first thing a server agent will do is determine what exactly is using up all the space in the MSDB database. The chances are good that the item with the biggest slice of the pie is the one causing the clear history task to malfunction. More often than not the culprit will be the sysmaintplan_logdetail table, which has caused more than its fair share of server maintenance grief over the years.
The Slice of the Pie
Fortunately, there’s a step-by-step that will help truncate the problem, and it only uses a few ALTER TABLE commands. The plan is coded as follows:
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;
To Shrink or Not to Shrink
The following step is where many server agents disagree, whether it’s best to shrink the MSDB log file or not. There are several good arguments and reasons that back up the no shrinking camp, but there are times when these support agents have no choice. One such situation is when there isn’t a process on the server that can delete records from the tables used to log job information.
In such situations the appropriate code is as follows:
— SHRINK THE MSDB LOG FILE
DBCC SHRINKFILE (MSDBLog, 512)
— SHRINK THE MSDB Data File
DBCC SHRINKFILE (MSDBData, 1024)
We Must Rebuild
The final step is to rebuild the indexes to ensure that everything is working properly, which is a simple code that writes as follows.
— REBUILD ALL INDEXES
EXEC sp_MSforeachtable @command1=”print ‘?’ DBCC DBREINDEX (‘?’, ‘ ‘, 80)”
— UPDATE STATISTICS
EXEC sp_helpdb @dbname= ‘MSDB’
After completing all these steps, the MSDB should be back under your control and the clean history task should resume as scheduled.
It looks like a lot of work, but this is a basic process that shouldn’t take too much time to remedy, especially if the people running the scripts know what they’re doing. This only highlights the fact that only trained professionals should touch your server maintenance. It saves time, money, and lets companies get on with their business.