Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
CanadaDBA
583 Posts |
 Posted - 2005-05-21 : 11:44:26
|
| If I have 4 databases. Which of the following schedules are the best? Why?1. Set a maintenance plan to Optimize, Integrity check and database backup ALL Databases starting at 9:00pm2. Set 4 maintenance plan to Optimize, Integrity check and database backup for each database starting at 9:00pm for the first DB, 10:00pm for the second, and so on.Canada DBA |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-05-21 : 11:47:14
|
| It depends on how big the databases are, capacity of the server, maintenance window, etc. ????Normally, I would prefer to spread them out.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-05-21 : 12:51:03
|
| I wouldn't use the sql server maint plans. Code yourself whatever you need.I would normally run things one after the other but it depends on the server (particularly the disks) and how big the databases are.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-05-21 : 13:00:22
|
| You can find some good scripts here:http://weblogs.sqlteam.com/taradhttp://www.nigelrivett.net/--Gotta like that Nigel. lolMeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-05-21 : 17:45:03
|
quote:
Originally posted by farhadr
If I have 4 databases. Which of the following schedules are the best? Why?1. Set a maintenance plan to Optimize, Integrity check and database backup ALL Databases starting at 9:00pm2. Set 4 maintenance plan to Optimize, Integrity check and database backup for each database starting at 9:00pm for the first DB, 10:00pm for the second, and so on.
I would go with a modified option 2. If you schedule all your jobs to kick off at the same time, you might see some deadlock issues. Basically, you want all your full backups to start at one time and sequentially go through each database. then you want another job to kick off (integrity check, or optimization or whatever) to go next and sequentially go through each database. You do not want to run a full backup, an integrity job and an optimization job on the same database at the same time. That will lead to deadlocks.-ec |
|
|
|
|
|
|
|
|
Best way to schedule jobs