How many databases in a maintenance plan???
-
Hi All,
We have twenty very small databases (largest DB 100MB) that reside under one SQL 2000 installation. I would like to setup maintenance plans for these databases and was wondering whether I should create individual daily/weekly plans for each database or could I get away with one plan covering all databases.
Your thoughts would be very much appreciated as I have been unable to find a definitive answer elsewhere.
Mark
-
Individual Plans for each databases will be a efficient method. Also include the database name in the maintanence Plan name for better documentation.
-
It Depends!
Actually it depends on application owner preference and application DRP (Disaster recovery Plan) requirements for each database. And also on recovery models of databases. And where to put backups. In case of Full recovery models you need Transaction log backup plans.
Examples:
- application developer for a server with many databases objected to individual plans I created because he did not want to see many jobs in the Agent.
- another application developer for another server with many databases requested one plan for ALL Databases because he adds databases and does not want to call me each time he creates a new database.
- several application developers on a shared server wanted their backups at different times based on how users are working, means different plans or individual backup jobs.
- I myself have my own preferences for where to put backups and may create individual backup jobs. Also I really don't need other maintenance on the training and test databases but need it for the production database.
Yelena
Regards,Yelena Varsha
-
Thanks to you both for your advice.
-
If this is a production system, I would recommend individual maintenance plans for each databases. You do not want a problem with one database to affect the backups/maintenance of the other production databases. If it is a development system, I agree with Yelena in saying that it is very much up to preference.
-
I had an elegant solution once (not mine) when a DBA (THE DBA) created ONE job with MANY steps, each step corresponded each database. This job contained ActiveX script that checked for older backup file, each backup step contained code that created the backup file name based on the database name and the current date.
This solution is good for the troubleshooting because each step will show you its own messages and you can workflow steps based on the previous step result.
Regards,Yelena Varsha
-
Thanks again to you all. It is a production server and I was swaying towards setting up individual plans for each database.
Cheers.
-
I think maintaining a separate plan for each database is overkill. That would be way too much trouble to maintain IMHO. I can't imagine having to juggle the scheduled time for each backup so they don't all try to run at the same time.
I create separate single-task maintenance plans. One only does full backups, one does log backups, one does optimizations, etc. Then I have another job that runs just before the full backup and checks the plan assignments for each database. If a new database has been created, it automatically goes into the full backup plan. Any databases that were dropped, detached, set to read-only, or marked suspect are removed from all plans. Any full or bulk recovery dbs missing from the log backup plan are added, any dbs that were changed to simple recovery are removed.
The most complicated version I ever had was for a server with some very large databases, that could not all be in a nightly full backup plan. One monster database was in its own plan for a full backup Friday night and differentials on Monday and Wednesday. (The differential backups were a custom job, but it could still use the sysdbmaintplan_databases table to tell it what dbs to work on.) Another job for the next few largest databases did a full backup Saturday and differentials on Tuesday and Thursday. Everthing else went into the standard full backup every night plan. I managed the databases in these plans manually, and the automated maint plan membership job only looked for databases that were not in any backup plan to be included in the nightly full backup plan.
Once it is set up this way I can create and drop databases and alter database options without worrying about maintenance plans, they take care of themselves.
-
Scott does bring up a good point regarding scheduled backup times. I still don't like the idea of having a problem with one database that affects all of the others in a production environment. You could run seperate plans for your larger databases and one plan that covers your smaller databases. This way, if there is a problem with one of the small databases that prevents the others from getting backed up, you can still get your backups run in a timely fashion when you get in in the morning and notice that your backups failed.
-
The situations I've seen where one database can screw up a maintenance plan and affect other databases I think are covered by automatically removing databases from plans where they don't belong. Suspect databases, simple recovery db in the log backup plan, etc. get removed from the plans before they run. There could be other situations I haven't forseen, but I haven't had any failures since I started doing it this way.
I do see failures sometimes, such as no room on the backup drive, that would be a problem no matter how many plans you use. Sometimes I have a problem with one db or another, but nothing serious enough to kill xp_sqlmaint and prevent the other dbs from getting backed up.
-
Scott...I agree. I have a single maintenance plan for my production databases and I have not had a situation where the failure of a db backup caused the entire maintenance plan to quit. I have had failures within the db maint job...but the job just reports the error for that db and continues on to the next db. I agree that keeping up with separate maint plans for many dbs can get very complicated. My suggestion would be to reduce it down to the least number as possible by logical selection. For example, I have seperate plans for prod dbs, system dbs, infrastructure dbs.
Just a thought....
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply