孤儿工作.. [英] orphaned jobs..

问题描述

我正在寻找一种方法来向我展示工作。我最终找到了这个脚本....

 SELECT ja.session_id，jb.name，/ * jb.owner_sid，ja.job_id，* / 
 jb.originating_server服务器名，
 ja.run_requested_date JobStartTime，
 ja.stop_execution_date JobStopTime，
 DATEDIFF（分，ja.run_requested_date，GETDATE（））用作TimeElapsed_Minutes，
例如，当ja.last_executed_step_id为null 
然后'Step'+ Cast（start_step_id as varchar（20））+'['+ sstep.step_name +']执行'
 else'Step'+ Cast（last_executed_step_id + 1为varchar（20））+ '[' + lstep.step_name 
 + ']执行' 
端CurrentStepID，sstep.step_name JobStartedfromStep 
，案例ja.run_requested_source当1然后 'SCHEDULER' 
当2时'ALERTER'
时3然后'BOOT'
当4然后'USER'
当6'然后'ON_IDLE_SCHEDULE'否则'未知'结束JobRunSource 
来自msdb.dbo.sysjobs_view jb 
 INNER JOIN msdb。 dbo.sysjobactivity JA ON jb.job_id = ja.job_id 
 INNER JOIN msdb.dbo.syssessions SESS ON sess.session_id = ja.session_id 
 LEFT JOIN MSDB.DBO.sysjobsteps sstep ON sstep.step_id = start_step_id和sstep.job_id = jb.job_id 
 LEFT JOIN MSDB.DBO.sysjobsteps LSTEP ON lstep.step_id = last_executed_step_id + 1和lstep.job_id = jb.job_id 
其中stop_execution_date IS NULL 
和run_requested_date IS NOT NULL 
按JobStartTime desc排序

它显示了正在运行的作业，但我也看到了几个具有有效JobStartTime（abeit旧日期）的作业，以及JobStopTime为NULL。 在我看来这是孤儿的工作。 我该如何删除它们？ 我试过通过session_id杀了，
我甚至重新启动了服务器，他们仍然留在那里。

任何想法都是为什么会发生这种情况，我该如何解决？ / p>

解决方案

Hi LandLord325，

  ;

也许请重新启动SQL Server Agent，然后检查它。您也可以使用以下脚本停止并删除作业。

 

 -------停止工作
 USE msdb 
 EXEC sp_update_job @job_name ='Name'，
 @enabled = 0 
 
 -------删除
使用[msdb] 
声明@job_name varchar（100）
 set @job_name = N'jobName'
 -------删除日志中的计划
从sysmaintplan_subplans作为子规划
删除sysmaintplan_log内部联接sysjobs_view作为syjobs上subplans.job_id = syjobs.job_id 
内的子计划加入sysmaintplan_log .subplan_id = sysmaintplan_log.subplan_id 
 where（syjobs.name = @job_name）
 -------删除代理的工作
从sysjobs_view删除sysjobschedules v 
 inner join sysjobschedules o on v.job_id = o.job_id其中v.name=@job_name 
 -------删除子计划
从sysmaintplan_subplans中删除sysmaintplan_subplans作为subplans 
内部联接sysjobs_view作为suj上的syjobs bplans.job_id = syjobs.job_id 
 where（syjobs.name = @job_name）
 -------删除作业
从msdb.dbo.sysjobs_view删除其中name = @job_name 
 

 

希望它可以帮到你。

 

最好的问候，

拉结

I was looking for a way to show me jobs running. I ended up finding this script....

    SELECT  ja.session_id,jb.name, /*jb.owner_sid, ja.job_id,*/
            jb.originating_server ServerName,  
            ja.run_requested_date JobStartTime,
			ja.stop_execution_date JobStopTime, 
            DATEDIFF(MINUTE, ja.run_requested_date, GETDATE()) as TimeElapsed_Minutes,  
            case when ja.last_executed_step_id is null  
                 then 'Step '+Cast (start_step_id as varchar(20)) +' ['+sstep.step_name+'] executing'  
                 else 'Step ' +Cast ( last_executed_step_id+1 as varchar(20))+' ['+ lstep.step_name  
                      + '] executing'   
            end CurrentStepID , sstep.step_name JobStartedfromStep  
            , Case ja.run_requested_source when 1 then 'SCHEDULER'  
                                   when 2 then 'ALERTER'  
                                   when 3 then 'BOOT'  
                                   when 4 then 'USER'  
                                   when 6 then 'ON_IDLE_SCHEDULE' else 'Unknown' end JobRunSource  
    FROM    msdb.dbo.sysjobs_view jb  
            INNER JOIN msdb.dbo.sysjobactivity ja ON jb.job_id = ja.job_id  
            INNER JOIN msdb.dbo.syssessions sess ON sess.session_id = ja.session_id  
            LEFT JOIN MSDB.DBO.sysjobsteps sstep ON sstep.step_id =start_step_id and sstep.job_id=jb.job_id  
            LEFT JOIN MSDB.DBO.sysjobsteps lstep ON lstep.step_id =last_executed_step_id + 1 and lstep.job_id=jb.job_id   
    WHERE   stop_execution_date IS NULL   
            AND run_requested_date IS NOT NULL
		order by JobStartTime desc

it shows me the jobs running, but also i see several jobs that have a valid JobStartTime(abeit Older date), and a JobStopTime of NULL.  Seems to me this are orphaned jobs.  How can i delete them out?  I've tried killing via the session_id, I've even rebooted the server and they still remain there.

Any idea's why this happened and how do i fix it?

解决方案

Hi LandLord325,

 

Maybe please restarted SQL Server Agent and then check it. Also you can use following script stop and delete the job.

 

-------stop job
USE msdb
EXEC sp_update_job @job_name = 'Name',
 @enabled = 0

-------delete 
use [msdb]
declare @job_name varchar(100)
set @job_name = N'jobName'
-------Delete the log in the plan
delete sysmaintplan_log from sysmaintplan_subplans as subplans
inner join sysjobs_view as syjobs on subplans.job_id = syjobs.job_id
inner join sysmaintplan_log on subplans.subplan_id =sysmaintplan_log.subplan_id
where (syjobs.name = @job_name)
-------Delete the agent's job
delete sysjobschedules from sysjobs_view v
inner join sysjobschedules o on v.job_id=o.job_id where v.name=@job_name
-------Delete subplan
delete sysmaintplan_subplans from sysmaintplan_subplans as subplans
inner join sysjobs_view as syjobs on subplans.job_id = syjobs.job_id
where (syjobs.name = @job_name)
-------Delete Job
delete from msdb.dbo.sysjobs_view where name = @job_name

 

Hope it can help you.

 

Best Regards,

Rachel

这篇关于孤儿工作..的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！