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.

SSIS package to get windows scheduled task status by serverku

We talked lots about SQL Server, Now moving to post this article of windows combining with SQL. You all know we can schedule ssis packages with scheduled jobs in SQL Server and with windows task scheduler too. We can easily get information and statistics of scheduled jobs which I ported just recently. We should have the same statistics for the windows task scheduled like a scheduled job from SQL Server and we should have an alert for its failures.

In this article i am creating one ssis package to achieve the same thing which we discussed above. So let us start and first creating prerequisite objects needed in ssis package.

1.  Creating staging table where we will import all windows scheduled tasks statistics.

CREATE TABLE [dbo].[WindowsTasksStatus]
(
 [seq] [int] IDENTITY(1,1) NOT NULL,
 [HostName] [varchar](200) NULL,
 [TaskName] [varchar](1000) NULL,
 [NextRunTime] [varchar](100) NULL,
 [Status] [varchar](100) NULL,
 [LastRunTime] [varchar](100) NULL,
 [LastResult] [varchar](100) NULL,
 [Schedule] [varchar](4000) NULL,
 [TaskToRun] [varchar](100) NULL
) 

2.  Create a batch file named WindowsTaskStatus.bat with following code. Pass server name in place of <HostName> and provide a path where to export csv file.

SCHTASKS /Query /S HostName /FO csv /V > E:\WindowsTaskStatus\hhax4_WindowsTaskStatus.csv

After creating all prerequisites, create a ssis package as per image and the all steps following,

Let me elaborate each task used in above ssis package.

Step 1 :  Execute SQL Task
In the first step, we will truncate the table on every execution of this package, so this table have following code with connection of SQL server database where we imports staging data.

Truncate table dbo.WindowsTasksStatus

Step 2 : Execute Process Task
In this step, we will call batch files created above.

Step 3 : For Each Loop Container
We will each files created in the second step and pass file name in variable, We have used For Each Loop Container because we can schedule other servers in the same batch file to export windows task status.

Step 4 : Data Flow Task
This task will pickup file passed from variable and import file data (Flat File Source) into a table (OLE DB Destination) which we created earlier. We also provide expression of connection string as FileName through property of the connection created for Flat File Source.

Step 5 : File System Task
After processing the files, it will be deleted by this task.

Step 6 : Execute SQL Task
Now we have staging table and also data imported from csv files for windows tasks statistics. So this final step to get the status of windows task's status. Here we will fetch the data from table for those windows tasks which have a last run status is not successful with last failed date time, next run time and excluding tasks which are currently executing. Following is the code used in this step which will fire an alert through an email.

DECLARE @Subject VARCHAR (1000) 
DECLARE @Body VARCHAR(max) 
DECLARE @hpart VARCHAR(400) 
DECLARE @MailProfile VARCHAR(50) 

-- Use your mail profile here
SET @MailProfile = '<Mail Profile>' 

CREATE TABLE #failedtasks 
  ( 
     hostname    VARCHAR(100), 
     taskname    VARCHAR(100), 
     lastruntime VARCHAR(100), 
     nextruntime VARCHAR(100) 
  ) 

SET @Subject = 'Failed windows tasks alert' 
-- Preparing column header 
SET @hpart= '<tr><td>HostName</td><td>TaskName</td><td>LastRunTime</td><td>NextRunTime</td></tr>' 

; WITH ctetasks 
     AS (SELECT hostname, 
                taskname, 
                lastruntime, 
                Cast (Substring(lastruntime, Charindex(',', lastruntime)+1, Len( 
                      lastruntime)) 
                      + ' ' 
                      + Substring(lastruntime, 0, Charindex(',', lastruntime)) 
                      AS 
                      DATETIME) AS 
                LastRunDateTime, 
                nextruntime 
         FROM   dbo.windowstasksstatus 
         WHERE  lastresult <> 0 
    -- Excuding task with disabled status
                AND nextruntime <> 'Disabled' 
                AND lastruntime <> 'Disabled' 
                AND lastruntime <> 'N/A' 
                AND nextruntime <> 'N/A' 
                AND hostname <> 'HostName' 
    --  Excuding task which are running
                AND status <> 'Running'
  )

-- Importing above result in temp  table
INSERT INTO #failedtasks 
SELECT hostname, 
       taskname, 
       lastruntime, 
       nextruntime 
FROM   ctetasks 
WHERE  Cast(lastrundatetime AS DATETIME) >= Dateadd(dd, 0, Datediff(dd, 0, 
                                                           Getdate())) 

-- creating a mail body from temp table data
SET @Body = Cast ( ( SELECT td = hostname, '', td = taskname, '', td = 
            lastruntime, '', td = 
            nextruntime FROM #failedtasks FOR xml path('tr'), type ) AS VARCHAR( 
            max) ) + N'' 
SET @Body = '<table border="1">' + @hpart + @Body 
            + '</table>' + '<br />' 

-- Firing an email
IF ( @Body IS NOT NULL ) 
  BEGIN 
      EXEC msdb.dbo.Sp_send_dbmail 
        @from_address = 'Windows Tasks Alert <alert@hhaexchange.com>', 
        @recipients = 'prajapatipareshm@gmail.com', 
        @subject = @subject, 
        @body = @Body, 
        @profile_name = @MailProfile, 
        @body_format = 'HTML'; 
  END 

-- Dropping temp table
DROP TABLE #failedtasks 

You may be using this or any other way to get windows scheduled tasks statistics, Please share your valuable inputs here.

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.

Script to get running schedule jobs - SQL Server by serverku

A few days back, while I was working with Alerts for failed scheduled jobs and missing scheduled jobs, I was needed to exclude running jobs. So we can exactly know which jobs are actually failed excluding running jobs and sane case for missing jobs. I applied some additional in the existing script to fetch the records of failed scheduled jobs or missing jobs. Let me share a script here to get all running scheduled jobs,

USE msdb
GO

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 INTO  #RunningJobs  
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1,'sa'

SELECT sj.name, 
       rj.* 
FROM   #runningjobs rj 
       INNER JOIN dbo.sysjobs sj 
               ON ( sj.job_id = rj.job_id ) 
WHERE  rj.running = 1 

DROP TABLE #RunningJobs

Above script use dbo.xp_sqlagent_enum_jobs undocumented object from the master database. It is also useful to get all other state values of jobs like following which received from forums,

0 = Not idle or suspended,
1 = Executing,
2 = Waiting For Thread,
3 = Between Retries,
4 = Idle,
5 = Suspended,
6 = WaitingForStepToFinish,
7 = PerformingCompletionActions

I am using this script in failed jobs and missing jobs list. You might be using it somewhere, Please share your comments. I will publish further posts to get failed jobs and missing jobs list.