I find that the only the checkdb maintenance plan is of any use and the other plans lack the necessary functionality. I use SQL stored procedure in the master database that have been marked as system procedures, which format the options for the xp_sqlmaint procedure.
Take and example of a single SQL Server with three user databases, one of 200Gb, 400Gb and 600Gb. There are tape backups (or SAN copies ) that complete by noon each day.
All three databases have a selective index rebuild followed by a full backup performed on different days of the week with differential backups on the other days. Since maintenance plans would not delete the full backup until after the next full backup succeeded, that would require 1.2 Tb of disk space, so instead, just before any backup runs, all *.bak files are deleted from disk.
Maintenance plans only allow log backups on a time scheduled basis. Instead, a log backup should be run whenever the log space used percent exceeds a threshold. Alerts cannot be used, because for small log files, a 39% used is reported even when the log is empty. Another requirement is that to reduce the exposure window, when the amount of time since the last log backup is exceeded, a log backup should be run.
For index rebuilds, there are two significant pieces of functionality that are lacking:
1. When in full recovery mode, the index rebuild will write to the transaction log faster than the log can be backed up causing the log to run out of space. Using maintenance plans, the transaction log needs to be the same size as the database or the maintenance plan will fail. Instead, a custom script that checks the log space percent used after after each index is rebuilt, and when a threshold is exceeded, a log backup should be performed.
2. Sometime there is a maintenance time window and you want to rebuild the most fragmented indexes until the end of the window is reached but this is not supported by maintenance plans.
Unfortunately, my client has insisted that these SQL scripts are proprietary and will not allow me to publish them.
SQL = Scarcely Qualifies as a Language