Invoke Scheduled Job on demand in SQL Server by serverku

We are mostly scheduled the job for the any script, but I have prepared the SP which invoked Job on demand as required, below code which I have created by SP and called it from the UI.

USE MSDB
GO

DECLARE @JobName VARCHAR(100)  
SET @JobName = 'YourJobName'

EXEC MSDB.DBO.Sp_start_job @JobName  --You can also invoke with JobId
GO

When you run it gives message, "Job 'YourJobName' started successfully." On second run, if first request is in queue, it will return with the following message, "Msg 22022, Level 16, State 1, Line 0 SQLServerAgent Error: Request to run job YourJobName (from User sa) refused because the job already has a pending request from User sa."

List running Schedule jobs with elapsed time in SQL Server by serverku

A few months ago, I wrote some articles about SQL Server scheduled jobs and details for same which are listed below

1. When exactly scheduled jobs started and completed
2. Find running scheduled jobs in SQL Server - Alternate way
3. Another way to list out running scheduled jobs

Now I am sharing another one script which belongs to above relevant articles with capture all details of executing scheduled jobs with elapsed time. Below is a script which collects all user scheduled jobs which are running for 30 minutes,

USE msdb
GO

SELECT 
 job.Originating_Server as ServerName, 
 job.Name as JobName, 
 activity.run_requested_Date as StartDate,
 DATEDIFF(MINUTE, activity.run_requested_Date, GETDATE()) as ElapsedTime_Minute
FROM dbo.sysjobs_view job WITH (NOLOCK)
INNER JOIN dbo.sysjobactivity activity WITH (NOLOCK)
 ON (job.job_id = activity.job_id)
WHERE run_Requested_date is not null and stop_execution_date is null
AND activity.run_requested_Date > DATEADD(DD,0,DATEDIFF(DD,0,GETDATE()))
AND job.category_id = 0
AND DATEDIFF(MINUTE, activity.run_requested_Date, GETDATE()) >= 30
GO

I would like you to share other ways to capture the same information. Stay tuned for more.

Find SSIS package details scheduled in jobs - SQL Server by serverku

I would like to share the script to find out the scheduled jobs which call SSIS packages in SQL Server.

USE MSDB
GO
SELECT 
 sj.job_id as JobId,
 sj.name as JobName,
 sjs.step_name as StepName,
 sjs.Command as Command
FROM sysjobs sj 
INNER JOIN sysjobsteps sjs
 ON(sj.job_id = sjs.job_id)
 WHERE sjs.subsystem = 'SSIS'
GO

The script return job name and the SSIS package full path as a command like "/FILE " here SSIS package full path" /CHECKPOINTING OFF /REPORTING E"

@schedule_uid is not a parameter for procedure sp_add_jobschedule - Error when creating a schedule job from script for SQL Server 2005 by serverku

A few days back when I was working with server migration, it was needed to migrate scheduled jobs also. What I did wrong? What’s the solution?

I had to move all scheduled jobs for SQL server 2005 where the source SQL server version and destination SQL server version is same. I generated a script of all jobs from another instance of SQL server 2008 or higher version in different server by connecting said source SQL server 2005 instance. After it gave an error while applying that script for jobs at the destination which is following,

Msg 8145, Level 16, State 1, Procedure sp_add_jobschedule, Line 0
@schedule_uid is not a parameter for procedure sp_add_jobschedule.

It will work fine if generates a script from SQL server 2005 instance. With 2008 or higher version, it adds parameter in script “@schedule_uid” and it creates an issue. To apply the script at destination either you have to remove @schedule_uid and its value from script or apply setting for ‘Script for server version’ from options.
Go to Tools –> Options –> SQL Server Object Explorer  and change value to required version of Script for server version’.

I applied above settings and succeed for me. Please share your ideas and comments for such issue.
Have a nice day. Stay tuned for more!

Script to get failed scheduled jobs in SQL Server by serverku

As we were talking about to get start time and end time of schedule jobs a week before, We are going to use same information, but it will be with the scheduled jobs having last status is filed for any steps. So I request to go through that post. Also visit post to get running jobs and the way to get it. The DBA needs to know the scheduled job status on routine duty.

Some of the jobs having schedule occurrence one time and some of them have recursive occurrence. Need to list out those jobs having last status is Fail. It may be possible some jobs have multiple steps and among them some fail, so those jobs should be coming into the list. And have to exclude running jobs, even if are failed earlier.

Here I am sharing one script which will alert us with those jobs which failed today and have following criteria,

1. List out failed jobs for today
2. List out jobs which have a last status as fail
3. Need Job Name, Start Time and End Time too
4. Excluding running jobs

USE msdb
GO

-- Creating a temp table
CREATE TABLE #RunningJobs 
( 
    job_id                UNIQUEIDENTIFIER NOT NULL,   
    last_run_date         INT              NOT NULL,   
    last_run_time         INT              NOT NULL,   
    next_run_date         INT              NOT NULL,   
    next_run_time         INT              NOT NULL,   
    next_run_schedule_id  INT              NOT NULL,   
    requested_to_run      INT              NOT NULL,   
    request_source        INT              NOT NULL,   
    request_source_id     sysname          COLLATE database_default NULL,   
    running               INT              NOT NULL,   
    current_step          INT              NOT NULL,   
    current_retry_attempt INT              NOT NULL,   
    job_state             INT              NOT NULL
)   

-- Insert the running jobs in this temp table
INSERT INTO  #RunningJobs  
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1,'sa'

SELECT 
 SERVERPROPERTY ('servername') as ServerName,  
 res.name as JobName,
 -- converting run_date and run_time to proper date time format for Start Time
 CAST(
  CONVERT(CHAR(8), run_date, 112) + ' ' + 
  STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, ':'), 3, 0, ':')
  AS DateTime)     AS StartedAt,  
 -- converting run_date,run_time and run_duration to proper date time format for Finish Time  
 (CONVERT (VARCHAR,
  (DATEADD(ss,
   (CASE LEN(run_duration)
    WHEN 1 
    THEN run_duration
    WHEN 2 
    THEN run_duration
    WHEN 3 
    THEN (CAST(Left(right(run_duration,3),1) as int)*60) 
     + (right(run_duration,2))
    WHEN 4 
    THEN (CAST(Left(right(run_duration,4),2) AS int)*60) 
     + (right(run_duration,2)) 
    WHEN 5 
    THEN (CAST(Left(right(run_duration,5),1) AS int)*3600) 
     + (CAST(Left(right(run_duration,4),2) AS int)*60) 
     + right(run_duration,2) 
    WHEN 6 
    THEN (CAST(Left(right(run_duration,6),2) AS int)*3600) 
     + (CAST(Left(right(run_duration,4),2) AS int)*60) 
     + right(run_duration,2)
     END ),
   CAST(
    CONVERT(CHAR(8), run_date, 112) + ' ' + 
    STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, ':'), 3, 0, ':')
   AS DateTime)))
  ,120))     AS FailedAt  
 FROM   (SELECT Row_number() 
      OVER( 
        partition BY sj.name 
        ORDER BY run_time DESC) AS rnk, 
       sj.job_id,    
       sj.name, 
       run_date, 
       run_time, 
       run_duration, 
       run_status 
   FROM   msdb.dbo.sysjobhistory sjh WITH (nolock) 
       INNER JOIN msdb.dbo.sysjobs sj WITH (nolock) 
         ON sjh.job_id = sj.job_id 
   WHERE  sjh.step_id <> 0 
       -- considering for today only
       AND run_date = CONVERT(VARCHAR(8), Getdate(), 112) 
       ) res 
 WHERE 
     -- Failed status
     res.run_status = 0
     -- Lastly failed 
     AND res.rnk = 1 
     -- excluding running jobs
     AND NOT EXISTS (SELECT rj.job_id 
         FROM   #runningjobs rj 
         WHERE  rj.job_id = res.job_id 
          AND rj.running = 1) 
-- Droping table
DROP TABLE #RunningJobs

We can also set an alert for those failed jobs in email. I would like you to share your thoughts and the way you are using to get failed job details.

Find running scheduled jobs in SQL Server - Alternate way by serverku

A few days before, I just posted one article to check running scheduled jobs and I used master. dbo. xp_sqlagent_enum_jobs system objects and joined with dbo.sysjobs object from the msdb database for the same. Please read that post before moving down. This was one way to get the list of running jobs and now here I am sharing another alternate way to find same details. For this I am using dbo. sp_help_job objects from msdb database and @execution_status parameter to get the details. Let me share a script here,

USE msdb
GO

–- 1- Executing
EXEC dbo.sp_help_job @execution_status = 1; 

And the result looks,

@execution_status parameter status values are following for which you list out the jobs with that passed status,

0 - Returns only those jobs that are not idle or suspended.
1 - Executing.
2 - Waiting for thread.
3 - Between retries.
4 - Idle.
5 - Suspended.
7 - Performing completion actions.

You may be using this script to get job status. Please put your comments if any other alternate way you have other than these.