SQL Job in idle state

Question

A created a new SQL Job in SQl Server Agent. I followed the steps mentioned in the article http://msdn.microsoft.com/en-us/library/ms187910.aspx

Still it is in idle state. PLease note that the schedular is prepared to execute every 10 seconds.

Below is the Drop and create script

IF  EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'test')
EXEC msdb.dbo.sp_delete_job @job_id=N'7ac1e514-fec2-4516-b716-f00107fb127e', @delete_unused_schedule=1
GO

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 01/26/2012 14:22:27 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'test', 
        @enabled=1, 
        @notify_level_eventlog=0, 
        @notify_level_email=0, 
        @notify_level_netsend=0, 
        @notify_level_page=0, 
        @delete_level=0, 
        @description=N'No description available.', 
        @category_name=N'[Uncategorized (Local)]', 
        @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [rr]    Script Date: 01/26/2012 14:22:31 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'rr', 
        @step_id=1, 
        @cmdexec_success_code=0, 
        @on_success_action=1, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'insert into [datet](dat) values(getdate())', 
        @database_name=N'master', 
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N's', 
        @enabled=1, 
        @freq_type=4, 
        @freq_interval=1, 
        @freq_subday_type=2, 
        @freq_subday_interval=10, 
        @freq_relative_interval=0, 
        @freq_recurrence_factor=0, 
        @active_start_date=20120125, 
        @active_end_date=99991231, 
        @active_start_time=203200, 
        @active_end_time=215959, 
        @schedule_uid=N'0ad45ad2-6d8f-4193-a687-f60bf7d5f0e5'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

enter image description here

The job will be idle unless it is actually running when you refresh the view in SSMS. What does the job history show (right-click the job, select "History")? — Ed Harper, Jan 26 '12 at 08:19
If the job isn't running, something is wrong with the schedule. I suggest you script out the job (right-click, select "script job as" > CREATE TO > New Query Editor window) and edit your question to include the resulting script. — Ed Harper, Jan 26 '12 at 08:33
Please update the question with the latest version of the job script — Ed Harper, Jan 26 '12 at 16:17
the schedule in the script still reflects the orignal start/end values — Ed Harper, Jan 26 '12 at 17:38

score 2 · Answer 1 · answered Feb 26 '12 at 16:27

2

As far as I can see your schedule is set to execute between midnight (12:00AM) to noon (12:00PM), i.e. your job will not execute in the afternoon and evening. Change your ending time to 11:59:59PM and it should run all day.

answered Feb 26 '12 at 16:27

Andreas Ågren

644
4
11

Ed Harper · Answer 2 · 2012-01-26T16:10:12.243

0

The job definition you have posted is configured only to execute between 20:32 and 21:59 in server local time. Check the local time of the server and confirm whether it's configured to be in the timezone you're expecting.

To remove this restriction, amend the schedule to run from 00:00:00 to 23:59:59.

EDIT

Try changing the schedule end time from 12:00:00 PM to 11:59:59 PM, just to be certain about the time frame

edited Jan 26 '12 at 16:10

answered Jan 26 '12 at 09:07

Ed Harper

452
6
11

Please check the schedule screenshot in my question. I hope it should be fine now ? – Pankaj Jan 26 '12 at 09:15
@StackOverflowUser - that looks correct. If you apply the change you'll soon be able to tell if the job is executing by monitoring the job history – Ed Harper Jan 26 '12 at 14:09
But i am still waiting to get a single record inserted in this table through the sql job scheduler – Pankaj Jan 26 '12 at 14:12
@StackOverflowUser - is job history being generated? – Ed Harper Jan 26 '12 at 16:03
no recordis coming up in history... – Pankaj Jan 26 '12 at 16:03
@StackOverflowUser - just to confirm, are you refreshing the view on the history page? It doesn't auto-refresh – Ed Harper Jan 26 '12 at 16:08
Yes i checked by doing this as well. Still waiting for records – Pankaj Jan 26 '12 at 16:10

SQL Job in idle state

2 Answers2

Linked