1

I am trying to schedule a job which executes stored procedure but it is failing.

DECLARE @return_value int
EXEC    @return_value = [dbo].[sp_WarehouseAttendance_New]
SELECT  'Return Value' = @return_value

If I try to run it manually, I am getting this error.

Manually Execute Job Error

At the moment I am trying to run EXECUTE storedproc but it takes long time to complete.

I checked Services.msc and it shows as below.

Services.msc

Kindly let me know how to avoid this.

Thanks, Ar

  • ,Make sure that the value of the Status column of the SQL Server Agent service is Running in services.msc . – Md Haidar Ali Khan Mar 22 '16 at 12:17
  • Haidar - Please look above I cannot change the Status :( – Aruna Raghunam Mar 22 '16 at 12:20
  • SQL Server Agent (and other SQL Server services) is different from most other services, you control it from SQL Server Configuration manager. I think SQL Server Agent is running, as it is accessible (green, with jobs listed below) in Management Studio, as shown in your original screenshot, if it were not running, it would not be accessible (red). I do not believe this is the problem. – Ian_H Mar 22 '16 at 12:38
  • @Aruna Raghunam,Have you try through properties->start the sql server agent services – Md Haidar Ali Khan Mar 22 '16 at 12:42
  • @Ian_H, As screen shot of OP there is two SQL server agent services. And one is disabled and second one is not running. That's means sql server agent service is not running of OP throng windows services. – Md Haidar Ali Khan Mar 22 '16 at 12:46
1

Looks like the problem is exactly in the description - the job is already running.

Try stopping the job first, then re-running it. Either with the GUI, or

USE msdb 
GO

EXEC dbo.sp_stop_job
    N'Job Name' 
GO

Other things to check: What is the job doing? Can you do that (run the script, for example) directly (not using Agent)?

Has the job got elements (e.g. a script) which can never actually finish, therefore the job is never going to stop (and therefore will not start again on schedule).

It's a bit hacky, but you can schedule a job-killing job to stop any jobs that are over-running (although it's usually better to try fix your jobs so they finish properly).

This script from SQL Server Central provides some handy info about currently running jobs:

SELECT
    ja.job_id,
    j.name AS job_name,
    ja.start_execution_date,      
    ISNULL(last_executed_step_id,0)+1 AS current_executed_step_id,
    Js.step_name
FROM msdb.dbo.sysjobactivity ja 
LEFT JOIN msdb.dbo.sysjobhistory jh 
    ON ja.job_history_id = jh.instance_id
JOIN msdb.dbo.sysjobs j 
ON ja.job_id = j.job_id
JOIN msdb.dbo.sysjobsteps js
    ON ja.job_id = js.job_id
    AND ISNULL(ja.last_executed_step_id,0)+1 = js.step_id
WHERE ja.session_id = (SELECT TOP 1 session_id FROM msdb.dbo.syssessions ORDER BY agent_start_date DESC)
AND start_execution_date is not null
AND stop_execution_date is null;

That should help you sort out your problem, if not, post some more details about what exactly the job is doing.

Edit- Code to check SQL server agent is running (run from Management studio). DECLARE @agent NVARCHAR(512);

SELECT @agent = COALESCE(N'SQLAgent$' + CONVERT(SYSNAME, SERVERPROPERTY('InstanceName')), 
  N'SQLServerAgent');

EXEC master.dbo.xp_servicecontrol 'QueryState', @agent;

You can manage Agent from SQL Server Configuration Manager.

Ian

| improve this answer | |
  • 1
    I ran the above SQL and it gives me SQLServerAgent Error: Request to stop job AttendanceWarehouse_New (from User EFC\araghunam) refused because the job is not currently running. I ran above script and it doesn't return anything. – Aruna Raghunam Mar 22 '16 at 13:30
  • @Ian_H,As OP screen shot sql server agent service is not running. Running status icon should be like that ( for your ref i am sending mssql tips link mssqltips.com/sqlservertip/2445/…) or sqlmatters.com/Articles/… – Md Haidar Ali Khan Mar 22 '16 at 13:36
  • @MdHaidarAliKhan Nothing actually indicates it's not running yet - and nothing that you said would help that. Those links are not relevant – Ian_H Mar 22 '16 at 13:51
  • @ArunaRaghunam to put this to bed, I'll add some code to the answer which will check whether agent is running on that server. If you want to check, find SQL Server Configuration manager in windows (not services), as that's where you start and stop Agent (and indeed SQL server). It probably wouldn't hurt to try restarting Agent (but you do it that way, NOT from windows services) – Ian_H Mar 22 '16 at 13:53
  • @Ian - I saw your link to start SQL Server Agent from SQL Configuration Manager but I do not see SQL Agent listed... – Aruna Raghunam Mar 22 '16 at 14:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.