Excluding databases from a maintenance plan

Is it possible to include all (user) databases in a maintenance plan, except
a few designated ones?
The problem: we have a database server (SQL Server 2000 running on Windows
2000 Server) with about 50 databases. Databases are constantly added and
removed by multiple people without any clear policy (I know we should have a
policy, but we're just not that kind of an organization). The most important
thing is that these databases are all included in the maintenance plan,
which is why we have a plan that "includes all user databases". The problem
is that there are a few large read-only databases that we want to exclude
from the maintenance plan for two reasons. First, because of the disk space
(backup is done to the local disk and copied to tape in a seperate step) and
second because read-only databases cause the optimization step in the
maintenance plan display a failure result. Eventhough nothing actually went
wrong (optimizations on all other databases is performed normally), we are
still forced to look through the log periodically just to make sure of that
(we're a small organization always pressed for time, looking through logs is
not the best way for us to spend our time).
If this cannot be done through SQL Server itself, are there any inexpensive
third party tools that can help with this? Does SQL Server 2005 includes
this functionality?
PS If possible please CC any responses to "brakelm at chello dot nl".
Thanks!
best regards,
Marcel van Brakel
Hi
When you are creating the maintenance plan you select the databases for
which it is to apply. You may be better off writing your own plan and
applying it to your own list. As a starting point for your own plan you may
want to profile what the maintenance plan does.
John
"Marcel van Brakel" <brakelm@.newsgroup.nospam> wrote in message
news:uMOvyFzQFHA.904@.tk2msftngp13.phx.gbl...
> Is it possible to include all (user) databases in a maintenance plan,
> except a few designated ones?
> The problem: we have a database server (SQL Server 2000 running on Windows
> 2000 Server) with about 50 databases. Databases are constantly added and
> removed by multiple people without any clear policy (I know we should have
> a policy, but we're just not that kind of an organization). The most
> important thing is that these databases are all included in the
> maintenance plan, which is why we have a plan that "includes all user
> databases". The problem is that there are a few large read-only databases
> that we want to exclude from the maintenance plan for two reasons. First,
> because of the disk space (backup is done to the local disk and copied to
> tape in a seperate step) and second because read-only databases cause the
> optimization step in the maintenance plan display a failure result.
> Eventhough nothing actually went wrong (optimizations on all other
> databases is performed normally), we are still forced to look through the
> log periodically just to make sure of that (we're a small organization
> always pressed for time, looking through logs is not the best way for us
> to spend our time).
> If this cannot be done through SQL Server itself, are there any
> inexpensive third party tools that can help with this? Does SQL Server
> 2005 includes this functionality?
> PS If possible please CC any responses to "brakelm at chello dot nl".
> Thanks!
> best regards,
> Marcel van Brakel
>
|||John,
Thanks for the quick respons.

> When you are creating the maintenance plan you select the databases for
> which it is to apply. You may be better off writing your own plan and
> applying it to your own list.
What do you mean by "writing your own plan"?
I quess I could write a job that loops over the list of databases, invoking
the appropriate commands for each, but that sounds like an awfully complex
job to get right (especially dealing with failure conditions)..
As for the list, it basically consists of all user databases (even the ones
added after creation of the maintenance plan) except for database X, Y and Z
(known, static list of database).

> As a starting point for your own plan you may want to profile what the
> maintenance plan does.
The maintenance plan is you everyday standard plan. It includes
reorganization of indices, stats update, integrity checks, and data backups
(no log backups since these are "simple" databases).
Marcel
|||Hi
Profiling will show you exactly what is needed, maintenance plans tend
to be a bit of a black box!!
John