I can't think of any benefits, but I am also biased towards using my own jobs for maintenance.
I think that MPs are very good for new or accidental DBAs, as they allow for some maintenance activities to be done (which is better than having none at all). But as most DBAs progress in their experience they tend to start developing their own custom scripts instead.
HTH
Josh,
This is a very common task for all DBAs and the right answer is NOT the same for every one and for each server. As lot of other things, it depends on what you need.
Most definitely you don't want to run "Shrink Database" as already suggested. Its EVIL to performance and the below ref will show you why. It causes disk and as well as index fragmentation and this can lead to performance issues. You are better off by pre-allocationg a big size for the data and log files so that autogrowth will NOT kick-in.
I didn't understand your #2. selected tables full backup. Can you elaborate more on this?
Coming to Index reorganize, update statistics and index rebuilds, you need to be careful on how you do this otherwise you will end up using more resources and also end up with performance issues.
When you rebuild indexes the statistics of the indexes are updated with fullscan but if you do update statistics after that, then those will be updated again with a default sample (which depends on several factors, usually 5% of the table when table size > 8 MB) which may lead to performance issues. Depending on the edition you have, you may be able to do online index rebuilds. The right way of doing this activity is check the amount of fragmentation and depending on that either do index rebuild or index reorganize + update statistics. And also you may want to identify which tables need to update stats more frequently and try to update stats more often.
Maintenance Plans are OK but its hard to get the best out of them doing these customizations unless you can login to SSIS and tweak the MP's. that's why I prefer NOT to use them and use Ola Hallengren's free scripts that are more robust than MP's. Also, I would recommend to catch up on the referenced article by Paul Randal on this topic.
Ref: http://technet.microsoft.com/en-us/magazine/2008.08.database.aspx
This is NOT a comprehensive answer to your question but a good starting point. HTH and let us know if you have any additional questions/comments.
Best Answer
If you detach a database from an instance, you will need to perform an OS-level delete of the file. The safer approach is to drop the database instead.
What I suggest is taking a final backup of the database after you put it into Read Only mode (as this will ensure no activity is occurring during the backup), after which remove it from you system by way of a Drop Database command.
The full set of commands would look similar to the following:
After this, you'll want to look for any Jobs that ran scripts against the database. I would suggest you just wait to see what fails (after which you can script out/delete the job) as there are numerous ways a job can reference a database (not all of which are easy to identify).
Finally, you'll want to remove any users from the instance that only had access to this database. This script should identify whom those users are, though Max's version is much cleaner (I didn't realize he posted an approach until after I edited my answer to include this):