July 3, 2007 at 1:36 am
I have a tricky question to Microsoft SQL Server 2000/2005. I have failed to find a solution to the problem yet and others have failed too. So I thought that I throw it around here, because the fact that a lot of knowledgeable DBAs hang around in this forum.
I am looking for a script/stored procedure that is able to show me upcoming job executions for a selected date/time range based on the current settings for the jobs configured on the Database server.
Example
Date/Time From: 2/16/2007 00:00 a.m. (Friday)
Date/Time To: 2/17/2007 12:00 p.m. (Saturday)
A. Job 1 / Schedule 1 = Week Days, 1:00 a.m.
B. Job 1 / Schedule 2 = Saturdays, 10:00 a.m.
C. Job 2 / Schedule 1 = hourly, between 10:00 a.m. and 1:00 p.m.
D. Job 3 / Schedule 1 = every 3rd Saturday of the month, 5:00 a.m.
E. Job 4 / Schedule 1 = every 1st Friday of the month, 8:00 a.m.
The routine I have in mind would not return Job E, because 2/16/2007 is the 3rd Friday of the month and not the first.
Job A: 2/16/2007 01:00 a.m.
Job B: 2/16/2007 10:00 a.m.
Job B: 2/16/2007 11:00 a.m.
Job B: 2/16/2007 12:00 p.m.
Job B: 2/16/2007 01:00 p.m.
Job D: 2/17/2007 05:00 a.m.
Job A: 2/17/2007 10:00 a.m.
Job B: 2/17/2007 10:00 a.m.
Job B: 2/17/2007 11:00 a.m.
Job B: 2/17/2007 12:00 p.m.
Output
Notes
The schedules of Job D and Job B overlap as you can see in my example above.
This happens only once per month though. I have over 20 jobs with sometimes very frequent execution times, like every 5 minutes or every 20 minutes and jobs that run hourly, daily, weekdays only, weekends only, monthly once etc.
Purpose
I want to do two things.
I want to determine where jobs overlap, not just by start date/time, but also by average run time and maximum run time.
I also want to be able to generate a report that shows me what should have been done and what was actually done by the jobs (note on the site, 6 of the jobs create new jobs on the fly for other database servers and this is sometimes not happening properly, without getting any error message. The volume of jobs makes the manual search like a search for a needle in a haystack.)
Findings so far
I did some digging myself and found following stored procedures that do some of the steps that I need and involved tables for the calculation.
Stored procedures:
- sp_get_schedule_description (in db: msdb) (undocumented stored procedure)
- sp_add_schedule (in db: msdb) http://msdn2.microsoft.com/en-us/library/ms187320.aspx
Tables:
- msdb.sysjobs
- msdb.sysjobschedules
- msdb.sysjobhistory
It is not a problem to determine the next execution of a job, but that is not what I need, anyway.
The problem is that it does not help you to determine all upcoming execution times, if the selected timeframe is long enough that SQL Server executes the job more than once.
There is no way around using the sysjobschedules table and calculate the execution dates and times based on the configured settings. See the Stored procedure: sp_get_schedule_description.
That one breaks down nicely the settings as documented for sp_add_schedule at
http://msdn2.microsoft.com/en-us/library/ms187320.aspx, but it does not allow the determination of the exact upcoming dates and times when the job is supposed to be executed.
Does anybody has a script that does that or several individual scripts that would have to be combined to do what I want to do?
I have a bunch of Database resources available here, just FYI.
http://www.cumbrowski.com/CarstenC/databasedevelopment.asp
Thanks. I appreciate it.
July 3, 2007 at 2:23 am
This script is for SQL Server 2000.
Use msdb
Create Table #job_listing(
row_num int identity(1,1),
Job_Name varchar(100),
Job_ID varchar(64),
Schedule_Name varchar(100),
Schedule_ID varchar(10),
Enabled varchar(3),
Command varchar(1000),
Description varchar(500)
)
Insert #job_listing (
Job_Name,
Job_ID,
Schedule_Name,
Schedule_ID,
Enabled,
Command,
Description
)
select
a.name,
a.job_id,
b.name,
b.schedule_id,
Case a.enabled when 1 then 'Yes' when 0 then 'No' end as Enabled,
Case when b.schedule_id IS NULL
then 'Update #job_listing Set Description = ''NO SCHEDULE DEFINED'' where
row_num = (select top 1 row_num from #job_listing where Description is
NULL)'
else 'Declare @schedule_description varchar(2000); exec
sp_get_schedule_description '
+ cast(b.freq_type as varchar(15)) + ', '
+ cast(b.freq_interval as varchar(15)) + ', '
+ cast(b.freq_subday_type as varchar(15)) + ', '
+ cast(b.freq_subday_interval as varchar(15)) + ', '
+ cast(b.freq_relative_interval as varchar(15)) + ', '
+ cast(b.freq_recurrence_factor as varchar(15)) + ', '
+ cast(b.active_start_date as varchar(15)) + ', '
+ cast(b.active_end_date as varchar(15)) + ', '
+ cast(b.active_start_time as varchar(15)) + ', '
+ cast(b.active_end_time as varchar(15))
+ ' , @schedule_description = @schedule_description OUT; Update
#job_listing set Description = @schedule_description where row_num =
(select top 1 row_num from #job_listing where Description is NULL)' end as
Command,
NULL
from sysjobs a
Left outer join sysjobschedules b
ON a.job_id = b.job_id
order by a.name
Declare @a int, @b-2 int, @cmd varchar(2000)
Set @a = 1
Select @b-2 = max(row_num) from #job_listing
Begin
Select @cmd = Command from #job_listing where @a = row_num
Exec( @cmd )
Print @cmd
End
SELECT * from #job_listing
Drop Table #job_listing
Courtesy:
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
July 3, 2007 at 8:48 am
If you need such comprenhesive set of features may I suggest you use SQL Server Sentry (http://www.sqlsentry.net/)
* Noel
July 3, 2007 at 10:03 am
Hi Sugesh,
Nope, that's not it. It does not consider the repititions.
Example
If there is only one job scheduled to run
1) every 5 minutes,
2) on every weekday
3) between 1:30pm and 2:00pm
You would get the following results
1) start date/time 7/7/2007 12:00pm, end date/time 7/8/2007 2:00pm
nothing, because the 7/7/2007 and 7/8/2007 are on the weekend
2) start date/time 7/5/2007 12:00pm, end date/time 7/5/2007 1:45pm
7/5/2007 1:30pm
7/5/2007 1:35pm
7/5/2007 1:40pm
7/5/2007 1:45pm
3) start date/time 7/5/2007 1:45pm, end date/time 7/6/2007 3:00pm
7/5/2007 1:45pm
7/5/2007 1:50pm
7/5/2007 1:55pm
7/5/2007 2:00pm
7/6/2007 1:30pm
7/6/2007 1:35pm
7/6/2007 1:40pm
7/6/2007 1:45pm
7/6/2007 1:50pm
7/6/2007 1:55pm
7/6/2007 2:00pm
Now SQL has a lot more configuration options for the scheduler.
Autom. when SQL starts | freq_type=64 | |
|
|
|
Starts when CPU idle | freq_type=128 | |
|
|
|
One Time On Date mm/dd/yyyy at time: hh:mm:ss am/pm | freq_type=1 | |
|
|
|
Recurring |
| |
----------------------------------------------- |
| |
Occurs |
| |
Daily | freq_type=4 | |
Every x day(s) | freq_interval=x | |
|
|
|
Weekly | freq_type=8 | |
Every x week(s) on | freq_recurrence_factor=x | |
Mo [ ], Tu [ ], We [ ], Th [ ], Fr [ ], Sa [ ], Su [ ], | ||
| 1 = Sunday, 2 = Monday, 4 = Tuesday, 8 = Wednesday, 16 = Thursday, 32 = Friday, 64 = Saturday. Examples: Su and Mo enabled = 3 (1 (Su) + 2 (Mo)), Mo, We and Fr enabled = 42 (2 (Mo) + 8 (We) + 32 (Fr))
| |
Monthly |
| |
Day X of every Y month(s) | freq_type=16 | |
|
| freq_interval=X |
| freq_recurrence_factor=Y | |
or |
| |
|
|
|
The 1st,2nd,3rd,4th,LAST WEEKDAY | freq_type=32 | |
of every Y month(s) ----------------------------------------------- |
freq_relative_interval=1,2,4 (3rd),8 (4th),16(last) | |
|
| freq_interval= 1=Su,2=Mo,3=Tu,4=We,5=Th,6=Fr,7=Sa, 8=Day,9=Weekday,10=Weekend day |
|
| freq_recurrence_factor=Y |
|
|
|
|
|
|
Occurs Once at hh:mm:ss AM/PM | freq_subday_type=0x1 | |
or |
| |
Occurs Every X Hours/Minutes Starting: hh:mm:ss A/PM | freq_subday_type=0x4 (minutes) or 0x8 (hours) | |
Ending: hh:mm:ss A/PM | freq_subday_interval=X | |
|
| active_start_time |
|
| active_end_time |
----------------------------------------------- |
| |
|
|
|
Start Date mm/dd/yyy End Date mm/dd/yyyy | active_start_date | |
or | active_end_date | |
No End Date | active_end_date=99991231 | |
And don't forget that you can have more than one schedule record for any single job, including no-schedule record (which would not interest me).
July 3, 2007 at 10:13 am
Crap, it ate the comment....
Hi Noel
SQL Sentry is fine and they did what I need, but there is no T-SQL Code, it's part of their windows software. I need it as SQL script to be able to use it for the various purposes of mine, some are unique, but some are not, like the mentioned.
I am surprised that nobody wrote a script over the years yet, at least none that can be easily found. I was searching for something that does that like crazy. It is not a SQL 2005 specific thing, but for SQL 2000 as well.
July 11, 2007 at 8:19 pm
mmh... same silence as everywhere else.
I can't believe that I am the only one who requires something like that.
There does not exist any t-sql script / stored procedure that does this?
I offer $25 to anybody who can point me to a stored procedure that does the described (it does not have to return exactly the table columns I mentioned, I can add that code myself, but it should do the determination of the individual job executions for any specified date/time range).
I offer $100 to anybody who writes and publishes a stored procedure that does the described. Please post it here at SQLServerCentral.com, accessible to anybody (public and for free) and I will send you the money (check or paypal).
SQL 2000 compatible would be preferred, but SQL 2005 code that can be altered to query a linked SQL 2000 server using DTC will work too (the production server in use where I need this is SQL 2000)
Thanks
July 11, 2007 at 9:39 pm
Generous but I'm curious... why would you pay someone $100 to write code and post it on a public/free forum? Hate to look a gift horse in the mouth, but what do you get out of it?
--Jeff Moden
Change is inevitable... Change for the better is not.
July 12, 2007 at 12:49 am
Jeff,
I don't mind your question, which is valid and deserves answers. Here we go:
1. The next poor guy might finds it quickly and does not have to spend the time I did in disbelieve that with all the thousands of scripts out there, none exists that seems to address this problem.
2. There is nothing "genius" about it or a reason to hide it from people.
3. Furthermore is it only right to have it published on the site where the forum resides and the request was made.
4. I don't think that it would be cool to offer a bounty, get what I need and then run with it, without sharing it.
5. Chances are that this thread will come up in search engines one day if somebody looks for the same solution. Speaking from personal experience. I hate forum threads that start with somebody asking exactly the same question I have without resulting in an answer and only leaving me with the knowledge that I am not alone in my quest for an answer.
6. If everybody would do it the way I do it, I will benefit from it as well, because I will actually find an answer to my question and will be thankful to the person who was going through the length of getting it.
7. Don't expect from others that you won't do yourself.
8. $100 tax and fee free is a lot of money for some guys out there. It is not much for me, if broken down to a hourly rate and what one hour of my time is worth to me.
I hope this makes sense and If this attempt results in an answer to my problem, I will only be mad that I didn't do it this way in the first place.
Eight good reasons in my opinion. Don't you think so too?
July 12, 2007 at 7:08 pm
Carsten,
Thanks for the detailed feedback. Hard to believe that someone has that particular attitude.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 13, 2007 at 9:19 am
great, it ate another comment of mine. I guess doing a post without prior preview does not work. Everything I entered was gone, except for the first few words.
Jeff, well, the short version of my intended response is that it is not very hard to have this kind of attitude, because it does have its very own egoistic benefits, but without the negative side effects. What goes around, comes around.
Cheers!
July 13, 2007 at 3:52 pm
There's some sort of nasy ol' timeout... I always do a select-all and copy before I try to post... just in case...
--Jeff Moden
Change is inevitable... Change for the better is not.
July 16, 2007 at 4:51 am
Carsten,
We have a job (which I cannot post because I did not write it) which monitors our job execution times. It tracks the average run time of each job in a table and then lets us know if a job runs longer than it should be running. The advice I can give you is this.
Create a table that tracks the job_ID, the job_state and the last / next run date & time. You should be able to break down the Last_run_time (with some conversions) to see how long the job ran when it last ran. Then, the next time the job runs, store it in a different field or record. Compare the two to see the difference in run times. You can use this method to find all your values (min, max and average), especially if you track this information in separate records each time.
You've already got the tables you need. BOL should be able to help you with the actual conversions. Sorry I can't post any code, but like I said, it belongs to someone else and I don't have his permission to share it.
July 16, 2007 at 5:58 am
I think the "hard part" isn't coming up with the fine code you guys already did, Brandi... the bugger is coming up with all future dates and times for a given future date range...
--Jeff Moden
Change is inevitable... Change for the better is not.
July 16, 2007 at 6:04 am
Hi Brandie,
I am not sure if that would work. The jobs I want to look at already write into a log file, if they run. The jobs create new jobs depending on the available databases on a number of servers. Each of those jobs also creates an entry in a log table.
The MSDB database exceeds 2.5 GB every other week because of the volume.
The problem is that some of the jobs created by the jobs don't seem to run from time to time without an error (at least none we capture). I wanted to determine which jobs should have been created and executed in a given time frame to check against the logs if entries are missing.
In a nutshell: The script would allow me to calculate all the occurrences that should have happened to then compare it to what actually did happen. It would also allow me to do predictions about the future executions and alerts could be triggered, if they do not happen.
This initial problem got me into the general problem, which I published here. The general problem helps solving a number of other problems as well and the reason why I am surprised that there does not seem to be a public solution available for it yet.
Does this make sense? Thanks for your offer though. I appreciate it.
Jeff: "the bugger is coming up with all future dates and times for a given future date range..."
You are right on the money (in parts). My example is a bit special due to the high volume. I can't add another tracking to what is already there, because that would overload the server. I want to do the analysis on a different box with past data.
p.s. I keep in mind the idea with saving the post in the clipboard. I usually do that with blogs. I never thought it to be necessary with online forums hehe.
Cheers!
November 19, 2008 at 1:15 am
Somebody posted a solution for this problem (source code of a very long UDF) at a different SQL Server forum at
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=96424
So, if you have the same issue and found this thread describing it perfectly, here is now also an answer and solution for it as well. 🙂
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply