January 2, 2014 at 9:16 am
I've got a SQL Job that I wrote back in 2009, which is meant to run once a quarter, at the beginning of the quarter. It should have run on January 1, but it didn't. We've migrated our database to VM hosted by our parent company. This happened in October, which was after the start of the last quarter. Could that move have changed the schedule?
Kindest Regards, Rod Connect with me on LinkedIn.
January 2, 2014 at 10:01 am
Double check that your job has a schedule attached to it. Make sure you didn't just move the database and forget to move the job, or moved the job and didn't setup a schedule with it as well.
I'm curious of the results.
.
January 2, 2014 at 10:06 am
Hi Bill,
I think it's been moved. I'm looking at it now in SSMS. The schedule is there, with the name that I gave it ("Beginning of Quarter"). It's in the Schedules tab for the job. I guess it's possible that the schedule "Beginning of Quarter" may be empty, but I don't know how to check that. How do I check to see what the definition of "Beginning of Quarter" schedule is?
Kindest Regards, Rod Connect with me on LinkedIn.
January 2, 2014 at 10:19 am
I would just right click the job, go to schedules, select the schedule, and hit edit.
See if the schedule type is listed as recurring, see if the Enabled box is checked, verify the frequency, verify there's no hard set end date that will mess it up....
If all else fails, remove the schedule and make a new one.
You've verified that the job didn't run at all correct and not that it didn't just run and fail.
.
January 2, 2014 at 11:21 am
Bill (DBAOnTheGo) (1/2/2014)
I would just right click the job, go to schedules, select the schedule, and hit edit.See if the schedule type is listed as recurring, see if the Enabled box is checked, verify the frequency, verify there's no hard set end date that will mess it up....
If all else fails, remove the schedule and make a new one.
You've verified that the job didn't run at all correct and not that it didn't just run and fail.
Yes, it looks fine to me. There's no end date, it runs monthly, on day 1 of each 3 months, at 12:05 AM. The start date was 1/1/2009, when I created it 5 years ago. The schedule type is recurring. Now, I don't know how to tell when SQL Server will run it again. Is there some way I can find that out? It should run it next on April 1, 2014 at 12:05 AM.
As far as knowing whether or not it ran, I've got another job which runs daily to track our draw down of funds. This quarterly job should have added new funds to a table, but it didn't. Furthermore, there's no history in SQL Server Agent | Jobs for the "Add Quarterly Allotment" SQL Job.
Kindest Regards, Rod Connect with me on LinkedIn.
January 2, 2014 at 11:47 am
Try this,
It will tell you the current status and last Execution and future execution times.
USE [msdb]
sp_help_job
January 2, 2014 at 12:58 pm
JackIntheBox (1/2/2014)
Try this,It will tell you the current status and last Execution and future execution times.
USE [msdb]
sp_help_job
Thank you, that's very helpful! I've just run the sp_help_job SP in msdb per your suggestion. Since I ran my SQL Job manually earlier, it indicated that, so no surprise there. It indicates that the next scheduled run job is 2/1/2014 (US time designation) at 500, which I take to mean 5 minutes past midnight. That date is certainly not the date I want it to run at. Does it start at the time, and then determine that it's the wrong date, and then goes back to sleep until 3/1/2014? Or is that really the next date that it will run the SQL Job to completion?
Addendum: I've already got this SQL Job set up to send me an email if it fails (I put that into the notifications), and that's the way I want it, but is there also a way to have it send me an email on it completes, too?
Kindest Regards, Rod Connect with me on LinkedIn.
January 2, 2014 at 1:27 pm
Someone help me out here.
If you set it to run every 3 months, it will run every 3 months from the date of the start time.
can you do a quick script to new window and give us the addjobschedule list?
ex:
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Daily Statistics 630PM',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20131021,
@active_end_date=99991231,
@active_start_time=183000,
@active_end_time=235959,
@schedule_uid=N'xxxx'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
.
January 2, 2014 at 2:52 pm
Edit the schedule on the job - you should see a section for Duration. In that section, you will have a Start date - for this job, that start date must be in one of the months where you expect the job to run.
Please let us know what that start date is.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 2, 2014 at 2:54 pm
You can also find the next scheduled run from the Job Activity Monitor. Open that - and you will see a column for 'Last Run' and 'Next Run'.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 2, 2014 at 3:04 pm
also, make sure job is enabled.
January 3, 2014 at 7:41 am
Jeffrey Williams 3188 (1/2/2014)
Edit the schedule on the job - you should see a section for Duration. In that section, you will have a Start date - for this job, that start date must be in one of the months where you expect the job to run.Please let us know what that start date is.
The start date is 1/1/2009, with no end date specified.
Kindest Regards, Rod Connect with me on LinkedIn.
January 3, 2014 at 7:44 am
Jeffrey Williams 3188 (1/2/2014)
You can also find the next scheduled run from the Job Activity Monitor. Open that - and you will see a column for 'Last Run' and 'Next Run'.
I didn't know that the Job Activity Monitor would show me that information. According to that the Next Run is 2/1/2014 at 12:05 AM. That is not the date I want it to run next! I want the next run date to be 4/1/2014.
Kindest Regards, Rod Connect with me on LinkedIn.
January 3, 2014 at 9:51 am
In addition to Enable Job, there is also a check box for Enable Schedule. It is on the screen where you Edit the schedule. Perhaps the schedule got disabled for some reason when the job was moved?
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply