March 4, 2008 at 12:46 pm
On my SQL2K5 server have a job that runs a DTS package every 20 minutes. The DTS moves data from a number of tables from an origin design server to the QA preview servers. Every once in a while, the job gets stuck running. There really isn't a rhyme or reason to it, and there isn't anything logged...and the job finishes. It just won't stop.
I'm trying to find out why this is happening, but I'm not finding much on this.
In the meantime, I would like to create a job that will look and see if this job is running, and if it is, stop it. This job will run each hour on a schedule where they will not collide, and so if the job is still running, it will be a situation where it should be stopped.
I know i could simply create a job to run sp_stop_job, but I want to make it smart enough to not do anything if the job is not running.
However, I cannot figure out how to get the results that I need. I've tried sp_get_composite_job_info, but I can't get the results exported to a temp table to query (because the INSERT EXECUTE is nested...it's a really complicated proc.
Has anyone has any experience with this?
March 4, 2008 at 1:17 pm
This should help ...
DECLARE
@JobIDUNIQUEIDENTIFIER
SET @JobID = 'jobid from sysjobs for your job in question'
IF OBJECT_ID('tempdb.dbo.#JobStatus') IS NOT NULL
DROP TABLE #JobStatus
CREATE TABLE #JobStatus
(
Job_IDUNIQUEIDENTIFIER,
Last_Run_DateINT,
Last_Run_TimeINT,
Next_Run_DateINT,
Next_Run_TimeINT,
Next_Run_Schedule_IDINT,
Requested_To_RunINT,
Request_SourceINT,
Request_Source_IDVARCHAR(100),
RunningINT,
Current_StepINT,
Current_Retry_AttemptINT,
StateINT
)
-- Retrieve results of last job run
INSERT INTO #JobStatus
EXEC master.dbo.xp_sqlagent_enum_jobs 0,sa,@JobID
-- Check to see if job is running
IF
(
SELECT COUNT(*) FROM #JobStatus
WHERE Running = 1
) > 0
BEGIN
-- Do your work here
END
ELSE
BEGIN
-- Job is not running
END
March 4, 2008 at 2:20 pm
Thanks very much, Adam! Looks like I was looking at the wrong table.
Much appreciated.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply