April 25, 2008 at 7:19 am
We currently have 5 databases on a server which have some maintenance jobs, though they're a hodgepodge of overlapping maintenance and jobs that just plain fail.
I'd like to change the maintenance plans to make more sense, but I'm not dead sure the best way to go about it.
I need to set up the following: Optimization, Integrity Check, Backup, Transaction Log Backup.
There seems to be 3 jobs per database, optimization, integrity check and then another job for backup & transaction log backup. The times of which all seem to overlap
It would seem to me that it would make more sense to instead make 4 jobs, one of each of the maintenance functions I'd like to perform, and just select multiple databases for each job.. does this seem reasonable, or should I keep them split out on a per database level and just fix the times the jobs run? My assumption here is that performance may take a hit if they're all doing stuff at the same time?
Also, if I were to put multiple databases into a single job, does SQL server run all of them at the same time, or would it queue them and do them one at a time?
Thanks for your help!
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂
April 28, 2008 at 2:01 am
Hi
What i would suggest you keep your jobs per database and keep them at different times so they dont overlap. Set up the jobs to run at a time u believe will have the least server activity. If you database size is mall you can have a single plan to take your backups of all database, i prefer diff maint plans for diff databases. The main thing is schedule your jobs to run when there is little server activity (nighttime) so that server does not get bogged down.
"Keep Trying"
April 28, 2008 at 7:12 am
thanks chirag.. makes sense
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂
April 28, 2008 at 8:40 am
Actually, unless there is a good reason, and I'd look for one, I'd make four jobs and check multiple databases for each.
April 28, 2008 at 8:45 am
Steve Jones - Editor (4/28/2008)
Actually, unless there is a good reason, and I'd look for one, I'd make four jobs and check multiple databases for each.
This is really what the jist of my question was Steve. What would be the benefits/issues with each circumstance. Why would it be better to run 4 jobs and check multiple databases as opposed to on a per database basis?
I'm just trying to get a basic understanding of not only what the best practice is, but why?
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂
April 28, 2008 at 9:10 am
I think the best practice is KISS. Especially the second S because you never know who's coming behind you.
If you had a good way to manage the load and track usage, then you can set up separate (20) jobs, however for most people that's not a good use of administrative time and management. Perhaps if your systems are really busy or you buy something like sqlSentry to help you.
If not, the standard practice is to have one job for each task and let that roll through the databases. It's also the default, so I'm not sure it's best, but it's simple.
Someone might have had issues at some point where they needed to stop some jobs because of interference with activity. If you have separate jobs, then you can easily stop one and not prevent others from running later.
Or you could stop the job, and reschedule a one-time run later that day.
April 28, 2008 at 11:25 am
Steve,
Thanks for your help on this.. I may do as you suggest and create the 4 jobs.. my only worry on this is, as the databases increase in size and activity, what happens if the maintenance goes over what I allot for the time.. so say i schedule a database backup at 3am, and schedule the next one for 3:15am, but the first one eventually runs 20 mins instead of 15,.. all of a sudden I have 2 running at the same time.. is there a way to just queue them up?
also, you mentioned sqlSentry. I googled it and found the page, it looks pretty nifty, though its got a pretty nifty pricetag to go along with it.. have you used this? If so, is it something that you'd recommend? Do I need a lot more experience with sql server before I should think of getting something like this, or would it help me with maintenance and administration and whatnot until I reach a point where I'm more proficient?
There is a trial version of the software, but I'm loath to install it and play with it straight away without knowing if its worthwhile, as I wont have too much time to play.. and I'm not gonna be able to talk my boss into putting his hand in his pocket to buy a license without doing the trial and working out if it'll save us time in the long run.
Thanks!
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply