by lunarg on May 13th 2019, at 13:48

It may happen (on badly configured SQL Servers) that the event log for maintenance plans fills up your storage and that it is no longer possible (because the volume is full) to use a task to clear the history. In that case, you can use the script attached to manually clear out the data.

The maintenance plan history is stored in the system database MSDB. If maintenance plans are defined and there's no task to occassionally clear the history, it will continue to fill up the database (and the volume it is on). If there's insufficient disk space, manually running the clean up task will fail because it will temporarily require additional space. The attached script drops some specific constraints, truncates tables, then recreates the dropped constraints. Because it does not rely on transactions, little to no extra space is needed. After running the script, you can then use the shrink functionality to shrink the database files (MDF and LDF) to release the used storage and free up the disk space.

This script (found on Technet somewhere) is a crude method for clearing maintenance plan history and should not be used unless absolutely necessary! It is provided as-is without any warranty. The responsibility for any data loss which might result from running this script lies entirely with the user.
« July 2024»
« When a bird does poo poo in your eye, be happy elephants don't fly. »