Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

A SQL Job of mine isn't running. I don't know why not. Expand / Collapse
Author
Message
Posted Thursday, January 2, 2014 9:16 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Sunday, November 9, 2014 7:49 AM
Points: 738, Visits: 1,306
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.
Post #1527182
Posted Thursday, January 2, 2014 10:01 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 7:43 AM
Points: 611, Visits: 514
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.


.
Post #1527206
Posted Thursday, January 2, 2014 10:06 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Sunday, November 9, 2014 7:49 AM
Points: 738, Visits: 1,306
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.
Post #1527208
Posted Thursday, January 2, 2014 10:19 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 7:43 AM
Points: 611, Visits: 514
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.


.
Post #1527213
Posted Thursday, January 2, 2014 11:21 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Sunday, November 9, 2014 7:49 AM
Points: 738, Visits: 1,306
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.
Post #1527231
Posted Thursday, January 2, 2014 11:47 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, October 24, 2014 10:31 AM
Points: 54, Visits: 1,016
Try this,

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


USE [msdb]
sp_help_job
Post #1527241
Posted Thursday, January 2, 2014 12:58 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Sunday, November 9, 2014 7:49 AM
Points: 738, Visits: 1,306
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.
Post #1527261
Posted Thursday, January 2, 2014 1:27 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 7:43 AM
Points: 611, Visits: 514
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


.
Post #1527270
Posted Thursday, January 2, 2014 2:52 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 12:19 PM
Points: 4,360, Visits: 9,543
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 opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #1527293
Posted Thursday, January 2, 2014 2:54 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 12:19 PM
Points: 4,360, Visits: 9,543
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 opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #1527295
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse