SQL Server How to stop a SQL job showing 'In Progress' status?

mrwjdhj3 于 2024-01-05 发布在其他

关注(0)|答案(2)|浏览(156)

I am using SQL Server 2014 and I have a job (called ITB) which has been scheduled to run at 4.00am on a daily basis. The SQL job has 11 steps in it. At 09.00 am this morning, I noticed that the job was showing 'In Progress' in the job history. It seems to be stuck at Step 8 of 11.

I have tried to stop the job by right clicking the job name in SSMS but I am getting the following message:

"Stop failed for Job 'ITB' (Microsoft.SqlServer.Smo)

I have also tried running the following T-SQL command in SSMS to stop the job:

USE msdb ;  
GO  

EXEC dbo.sp_stop_job  
N'ITB' ;  
GO

I am getting the following message is SSMS:

Msg 22022, Level 16, State 1, Line 2
SQLServerAgent Error: Request to stop job ITB (from User sa) refused because the job is not currently running.

What is happening here and how can I deal with this?

sql-server

来源：https://stackoverflow.com/questions/50248582/how-to-stop-a-sql-job-showing-in-progress-status

2条答案

按热度按时间

kuarbcqp1#

Restart SQL Server Agent

In Object Explorer, connect to the instance of the Database Engine, right-click SQL Server Agent, and then click Start, Stop, or Restart.

If the User Account Control dialog box appears, click Yes.

When prompted if you want to perform the action, click Yes.

赞(0）回复(0）举报 2024-01-05

k4ymrczo2#

You need to add a check to see if job is running. Run your command only if it's running

IF EXISTS(

    SELECT * FROM
        msdb.dbo.sysjobactivity AS sja
        INNER JOIN msdb.dbo.sysjobs AS sj
        ON  sja.job_id = sj.job_id
        WHERE
        sja.start_execution_date IS NOT NULL AND
        sja.stop_execution_date IS NULL AND
        sj.name =  N'RamTest'
    )
    
    BEGIN
    
        EXEC msdb..sp_stop_job @job_name = N'RamTest;
    
    END

赞(0）回复(0）举报 2024-01-05

我来回答

SQL Server How to stop a SQL job showing 'In Progress' status?

2条答案

Restart SQL Server Agent

相关问题

热门标签

最新问答