October 6, 2011 at 7:05 am
Hello,
We are having a lot of backup server issues. I think a lot of backups are scheduled at the same time and hitting all at once. I want to stagger the backups from hitting all at once.
I am trying to write a query to show me all the backups maintenance plan jobs and their repective schedules. The end result would be:
servera userdatabase backup m-sat. 3:00 a.m.
servera sysdatabase backup m-sat. 4:30 a.m.
servera trans log backup m-sat. every half an hour from 9-9.
I could then put a script in an SSIS package and have the schedules for all production servers that I back up to the backup server. I would then create a report in SSRS to show all the server's and their backup schedule.
I've been searching through the forums for a script that will show me the schedules.
I see that I can query the job history and get when it is executed and next to run. Not quite the same, but I can use that if I can't find a way to query to get the schedule itself.
Does anyone know how to query for a job and its respective schedule?
Thanks
Tony
Things will work out. Get back up, change some parameters and recode.
October 6, 2011 at 7:41 am
Tony,
have a look here:
http://www.mssqltips.com/sqlservertip/1622/generate-sql-agent-job-schedule-report/
I think this is pretty much what you want.
[font="Verdana"]Markus Bohse[/font]
October 6, 2011 at 6:31 pm
and, for what it's worth, a similar posting on SSC from a few months before that other site...
http://www.sqlservercentral.com/scripts/Maintenance+and+Management/62222/
A caveat: as with any software, purchased or freely shared, do test what you get before depending your servers (and your job) on it. You'll see that the SSC script was altered to include a small fix I hacked for it. If you're building an automated scheduling application, you'll want to be sure it can handle edge conditions (like times near midnight) and other rare but possible conditions.
October 7, 2011 at 7:25 am
http://www.sqlsoft.co.uk/sqljobvis.php should do the trick.
It's free and easy to use.
-- Gianluca Sartori
October 13, 2011 at 1:34 pm
Markus,
Thanks for replying. It worked well.
Unfortunately, my folks here do not want to have something loaded on each production server. This script works, but you first have to create a function on each server.
I am supposed to keep the servers as vanilla as possible. Creating a SSIS package that runs some code on each server on the fly is the best solution for us here.
Thanks anyway. I appreciate your responding an offering an idea.
Tony
Things will work out. Get back up, change some parameters and recode.
October 13, 2011 at 1:44 pm
John,
It worked perfectly. I just have to make a note that you can only run this on servers that are 2005 and above.
We have just a few servers still running 2000. I got this error message:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'msdb.dbo.sysschedules'.
But I only have a few servers running 2000. We have some high profile applications that have not upgrade the server that they use. So to not break support contract, we are stuck with an older version.
But for the rest....thanks. This is what I was looking for.
Tony
Things will work out. Get back up, change some parameters and recode.
October 13, 2011 at 1:46 pm
Gianluca,
Thanks for the response. Looks interesting.
Now what is a spaghetti DBA?
Tony
Things will work out. Get back up, change some parameters and recode.
October 14, 2011 at 2:29 am
WebTechie38 (10/13/2011)
Gianluca,Thanks for the response. Looks interesting.
Now what is a spaghetti DBA?
Tony
It's me! 🙂
That's the name I gave to my blog.
-- Gianluca Sartori
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply