Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


A SQL Job of mine isn't running. I don't know why not.


A SQL Job of mine isn't running. I don't know why not.

Author
Message
Rod
Rod
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1174 Visits: 1940
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.
Bill (DBAOnTheGo)
Bill (DBAOnTheGo)
SSChasing Mays
SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)

Group: General Forum Members
Points: 639 Visits: 585
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.

.
Rod
Rod
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1174 Visits: 1940
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.
Bill (DBAOnTheGo)
Bill (DBAOnTheGo)
SSChasing Mays
SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)

Group: General Forum Members
Points: 639 Visits: 585
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.

.
Rod
Rod
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1174 Visits: 1940
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.
JackIntheBox
JackIntheBox
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 1031
Try this,

It will tell you the current status and last Execution and future execution times.


USE [msdb]
sp_help_job
Rod
Rod
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1174 Visits: 1940
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.
Bill (DBAOnTheGo)
Bill (DBAOnTheGo)
SSChasing Mays
SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)

Group: General Forum Members
Points: 639 Visits: 585
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

.
Jeffrey Williams 3188
Jeffrey Williams 3188
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4468 Visits: 9836
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
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

Jeffrey Williams 3188
Jeffrey Williams 3188
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4468 Visits: 9836
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
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search