SQL Server Job doesn't run

Question

Am new to SQL Server, and trying to learn how to do SQL Server Jobs. I executed this simple example from msdn in a SQL Server Express instance I have installed.

USE msdb ;
GO
EXEC dbo.sp_add_job
    @job_name = N'Weekly Backup' ;
GO
EXEC sp_add_jobstep
    @job_name = N'Weekly Backup',
    @step_name = N'Set database to read only',
    @subsystem = N'TSQL',
    @command = N'ALTER DATABASE SALES SET READ_ONLY', 
    @retry_attempts = 5,
    @retry_interval = 5 ;
GO
EXEC dbo.sp_add_schedule
    @schedule_name = N'ROce',
    @freq_type = 1,
    @active_start_time = 212600 ;
USE msdb ;
GO
EXEC sp_attach_schedule
   @job_name = N'Weekly Backup',
   @schedule_name = N'ROce';
GO
EXEC dbo.sp_add_jobserver
    @job_name = N'Weekly Backup';
GO
-- Start the job
USE msdb;
GO
EXEC dbo.sp_start_job N'Weekly Backup';
GO

I have set the @active_start_time = 212600 When I executed this script, the system time was 21hrs23minutes. So, the job should have executed in 3 minutes from then. I waited till 21.30hrs and then checked the jobs executed history, but don't see it : I used this script to see jobs'-executed history:

SELECT  sj.name, 
sja.run_requested_date, 
CONVERT(VARCHAR(12), sja.stop_execution_date-    sja.start_execution_date, 114) Duration
 FROM   msdb.dbo.sysjobactivity sja
 INNER  JOIN msdb.dbo.sysjobs sj
ON   sja.job_id = sj.job_id
WHERE   sja.run_requested_date IS NOT NULL
ORDER   BY sja.run_requested_date desc

I also tried another script I got from the web:

select 
 j.name as 'JobName',
 msdb.dbo.agent_datetime(run_date, run_time) as 'RunDateTime',
 ((run_duration/10000*3600 + (run_duration/100)%100*60 + run_duration%100 + 31 ) / 60) 
     as 'RunDurationMinutes'
From msdb.dbo.sysjobs j 
INNER JOIN msdb.dbo.sysjobhistory h 
 ON j.job_id = h.job_id 
where j.enabled = 1   --Only Enabled Jobs
--and j.name = 'TestJob' --Uncomment to search for a single job
order by JobName, RunDateTime desc

But none of the jobs are listed. Is it that the job is not even executing? I tried renaming the job and restarting it, but no...doesn't work. I don't have SQL Server Agent, as it doesn't come with the Express edition. Please help.

As you said, you don't have SQL Server Agent so why do you expect the jobs to run? — Pondlife, May 06 '13 at 16:42
Yes, SQL Server Agent jobs are very useful and have decent error behavior and logging, but you do need SQL Server Agent. — criticalfix, May 06 '13 at 18:18

score 3 · Accepted Answer · edited May 23 '17 at 10:25

3

@Pondlife and @criticalfix (in comments, above) are right. SQL Agent is the scheduler for SQL Server. Your scheduled job won't run without SQL Agent.

One alternative to consider is to use the AT scheduler for Windows. Here is a SO article that talks about a nice command line call to do your backup: What is a simple command line program or script to backup SQL server databases?

edited May 23 '17 at 10:25

Community

1
1

answered May 06 '13 at 18:39

tgolisch

6,549
3
24
42

+1 Thank you. I didn't know, I must admit my ignorance/stupidity, that jobs will not run without SQL Server Agent. – Jean May 07 '13 at 07:07

score 1 · Answer 2 · answered May 06 '13 at 18:39

1

"Express" is the free edition. "Jobs" are a feature you get when you buy a non-free version.

So, if you're a developer and want to code jobs, what can you do?

You can get "Developer Edition" for a modest price, but you cannot DEPLOY onto Developer Edition.

But you would be able to create jobs with Developer Edition.

Here is an example:

LINK

answered May 06 '13 at 18:39

granadaCoder

26,328
10
113
146

+1 I didn't know which to mark as the right answer, as both were right. Thank you. I didn't know, I must admit my ignorance/stupidity, that jobs will not run without SQL Server Agent. – Jean May 07 '13 at 07:07
Ignorance is ok, that's what the forums are for. Stupidity is not asking the question......so no stupidity involved, IMHO. – granadaCoder May 07 '13 at 15:02

SQL Server Job doesn't run

2 Answers2