Large MSDB Database From sysmaintplan_logdetail Table

By Lori Brown | Miscellaneous

Jul 02

— By Lori Brown  @SQLSupahStah

I recently received a panicked call from a client who had a SQL instance go down because the server’s C drive was full. As the guy looked he found that the msdb database file was 31 GB and was consuming all of the free space on the OS drive causing SQL to shut down. He cleaned up some other old files so that SQL would work again but did not know what to do about msdb.

As we looked at it together I found that the sysmaintplan_logdetail table was taking all the space in the database. The SQL Agent had been set to only keep about 10000 rows of history but for some unknown reason the table never removed history. After consulting MSDN I found this code did the trick for truncating this table.

USE msdb

GO

ALTER TABLE [dbo].[sysmaintplan_log] DROP CONSTRAINT [FK_sysmaintplan_log_subplan_id];

GO

ALTER TABLE [dbo].[sysmaintplan_logdetail] DROP CONSTRAINT [FK_sysmaintplan_log_detail_task_id];

GO

TRUNCATE TABLE msdb.dbo.sysmaintplan_logdetail;

GO

TRUNCATE TABLE msdb.dbo.sysmaintplan_log;

GO

ALTER TABLE [dbo].[sysmaintplan_log] WITH CHECK ADD CONSTRAINT [FK_sysmaintplan_log_subplan_id] FOREIGN KEY([subplan_id])

REFERENCES [dbo].[sysmaintplan_subplans] ([subplan_id]);

GO

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;

GO

After the table was truncated we were able to shrink the database to about 1 GB. For the record – I hate, hate, hate to shrink databases but there were no other options left to us and we had to clear out some room on the drive.

Now with the crisis averted we checked the SQL Agent settings and found that the box to remove agent history was not checked.

Blog_20150702_1

We checked it, hit OK then opened the SQL Agent properties again only to find that the box was unchecked. After doing some research I found that this is a bug that has not been resolved even in SQL 2014.   https://connect.microsoft.com/SQLServer/feedback/details/172026/ssms-vs-sqlagent-automatically-remove-agent-history-bugs Awesome, huh?!

If you check the link there is a workaround posted. I have tested it and found that it takes a super long time to run sp_purge_jobhistory and my test server only has 2 jobs that would have any history at all. So, use the workaround if you feel brave. Hopefully Microsoft will actually fix this some time. Until then, keep an eye on your msdb database size.

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRxSupport@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. We love to talk tech with anyone in our SQL family!

Follow

About the Author

Lori is an avid runner, cross fitter and SQL enthusiast. She has been working for SQLRX for 15 years and has been working with SQL in general for 25 years. Yup...she is an old hand at this stuff.

  • Mike Beaton says:

    Thank you! 🙂

    I also found that my sysmail_mailitems table was huge (using this: https://stackoverflow.com/a/2094454/795690). Here is some code based on yours which clears that down too:

    “`
    ALTER TABLE [dbo].[sysmail_attachments] DROP CONSTRAINT [FK_sysmail_mailitems_mailitem_id];

    ALTER TABLE [dbo].[sysmail_send_retries] DROP CONSTRAINT [FK_mailitems_mailitem_id];

    TRUNCATE TABLE msdb.dbo.sysmail_attachments;

    TRUNCATE TABLE msdb.dbo.sysmail_send_retries;

    TRUNCATE TABLE msdb.dbo.sysmail_mailitems;

    ALTER TABLE [dbo].[sysmail_send_retries] WITH CHECK ADD CONSTRAINT [FK_mailitems_mailitem_id] FOREIGN KEY([mailitem_id])
    REFERENCES [dbo].[sysmail_mailitems] ([mailitem_id]) ON DELETE CASCADE;

    ALTER TABLE [dbo].[sysmail_attachments] WITH CHECK ADD CONSTRAINT [FK_sysmail_mailitems_mailitem_id] FOREIGN KEY([mailitem_id])
    REFERENCES [dbo].[sysmail_mailitems] ([mailitem_id]) ON DELETE CASCADE;
    “`

  • >