Category Archives: Jobs Detail

Script to Remove Jobs History

Posted on by

Script to Remove Jobs History.

You can remove jobs history for particular job for a fixed time.

Below script will remove history data older then 30 days and where job name is “jobname”.

I have used procedure “SP_PURGE_JOBHISTORY” provided by SQL Server, you just need to pass @JobName and @Date as a parameter.


DECLARE @Date DATETIME
DECLARE @Jobname VARCHAR(256)

-- Keep Last 30 days
SET @Date = GETDATE() - 30
SET @Jobname = 'JobName'

EXEC MSDB.DBO.SP_PURGE_JOBHISTORY
@Job_name=@Jobname,
@Oldest_date=@Date

You can also pass JobID instead of JobName.


DECLARE @Date DATETIME
DECLARE @JobID VARCHAR(256)

-- Keep Last 30 days
SET @Date = GETDATE() - 30
SET @Job_Id = 'JobID'

EXEC MSDB.DBO.SP_PURGE_JOBHISTORY
@Job_ID=@Job_ID,
@Oldest_date=@Date

If you want to remove all history for particular job then remove @date from parameter.


DECLARE @JobID VARCHAR(256)
SET @Job_Id = 'JobID'

EXEC MSDB.DBO.SP_PURGE_JOBHISTORY
@Job_name=@Jobname

If you want to clear all history for all jobs just run the procedure “EXEC MSDB.DBO.SP_PURGE_JOBHISTORY”.
it will remove all history for you instance.

SQL Server has two ways to remove data older then 30 days for all jobs.
1) By Script.


DECLARE @Date DATETIME

-- Keep Last 30 days
SET @Date = GETDATE() - 30
SET @Jobname = 'JobName'

EXEC MSDB.DBO.SP_PURGE_JOBHISTORY
@Oldest_date=@Date

2) By SQL Server GUI.
Step1 : Right click on SQL Server Agent and click on property.
Script to remove job history 1

Step2: On the left pane select History and click check box remove agent history.
you have three options day(s), week(s) or month(s) select one of appropriate to remove agent history.

Script to remove job history 2

Regards,

Nirav gajjar

Script to get Jobs Execution Details

Posted on by

Script to get Jobs Execution Details.

This script will give you whole execution details with job started time, job execution time, job finish time, and job execution status.

Note: For multiple scheduled jobs it will fetche only last executed job details.

You need to create one function for that with name “CONV_DATETIME”.

USE [MSDB]
GO
CREATE FUNCTION [DBO].[Conv_datetime](@Id INT,
                                      @It INT)
RETURNS DATETIME
AS
  BEGIN
      DECLARE @Vt CHAR(6),
              @D  CHAR(8),
              @Dt DATETIME

      SELECT
        @D = CONVERT(VARCHAR, @Id)

      IF Len(@It) = 5
        SET @Vt = '0' + CONVERT(CHAR, @It)
      ELSE IF Len(@It) = 4
        SET @Vt = '00' + CONVERT(CHAR, @It)
      ELSE IF Len(@It) = 3
        SET @Vt = '000' + CONVERT(CHAR, @It)
      ELSE IF Len(@It) = 2
        SET @Vt = '0000' + CONVERT(CHAR, @It)
      ELSE IF Len(@It) = 1
        SET @Vt = '00000' + CONVERT(CHAR, @It)

      SELECT
        @Dt = LEFT (@D, 4) + '-' + Substring(@D, 5, 2) + '-'
              + RIGHT(@D, 2) + ' ' + LEFT(@Vt, 2) + ':'
              + Substring(@Vt, 3, 2) + ':' + RIGHT(@Vt, 2)

      RETURN ( @Dt )
  END

Execute below script after creating the function.

USE MSDB
GO
;WITH JOBSDETAIL
     AS (SELECT
           Row_number()
             OVER(
               PARTITION BY Sj.Name
               ORDER BY Run_time DESC) AS Rnk,
           Sj.Name,
           Run_date,
           Run_time,
           Run_duration                AS Runduration,
           CASE
             WHEN Run_status = 0 THEN 'Failed'
             WHEN Run_status = 1 THEN 'Successes'
             WHEN Run_status = 3 THEN 'Stopped'
           END                         AS Status
         FROM
           DBO.Sysjobhistory Sjh
         JOIN   DBO.Sysjobs Sj
           ON Sjh.Job_id = Sj.Job_id
         WHERE
          Sjh.Step_id = 0
          AND Run_date       = CONVERT(VARCHAR(8), Getdate(), 112))
SELECT
  Name                                                                                                             AS Jobname,
  CONVERT(VARCHAR(5), MSDB.DBO.Conv_datetime(Run_date, Run_time), 108)                                             AS Startdate,
  CONVERT(VARCHAR(5), Stuff(Stuff(RIGHT('000000' + CONVERT(VARCHAR, Runduration), 6), 5, 0, ':'), 3, 0, ':'), 108) AS Runduration,
  CONVERT(VARCHAR(5), Dateadd(SS, ( CASE Len(Runduration)
                                      WHEN 1 THEN Runduration
                                      WHEN 2 THEN Runduration
                                      WHEN 3 THEN ( Cast(LEFT(RIGHT(Runduration, 3), 1) AS INT) * 60 ) + ( RIGHT(Runduration, 2) )
                                      -- min,sec
                                      WHEN 4 THEN ( Cast(LEFT(RIGHT(Runduration, 4), 2) AS INT) * 60 ) + ( RIGHT(Runduration, 2) )
                                      -- min,sec
                                      WHEN 5 THEN ( Cast(LEFT(RIGHT(Runduration, 5), 1) AS INT) * 3600 ) + ( Cast(LEFT(RIGHT(Runduration, 4), 2) AS INT) * 60 ) + RIGHT(Runduration, 2)
                                      WHEN 6 THEN ( Cast(LEFT(RIGHT(Runduration, 6), 2) AS INT) * 3600 ) + ( Cast(LEFT(RIGHT(Runduration, 4), 2) AS INT) * 60 ) + RIGHT(Runduration, 2)
                                    END ), MSDB.DBO.Conv_datetime(Run_date, Run_time)), 108)                       AS Completedat,
  Status,
  Run_date
FROM
  JOBSDETAIL
WHERE
  Rnk = 1

Regards,
Nirav Gajjar

Script to get SSIS Jobs

Posted on by

Script to get SSIS Jobs.

This script will help you out to get Job Details by category like SSIS,TSQL,etc..


USE MSDB

GO

SELECT Sj.Job_id   AS Jobid,
       Sj.Name     AS Jobname,
       Sjs.Command AS Command
FROM   Sysjobs Sj
       INNER JOIN Sysjobsteps Sjs
               ON( Sj.Job_id = Sjs.Job_id )
WHERE  Sjs.Subsystem = 'SSIS'-- other types (LogReader,PowerShell,Snapshot,TSQL)
       AND Sj.Enabled = 1

GO 

Script to get SSIS Jobs

 

Regards,

Nirav Gajjar

Script to get Job Name from Command

Posted on by

Script to get Job Name from Command

This script helps to get a job name from command or job script.

If you remembered only one word that you have used in your job script and you want to get a job name, this script helps you.

Just paste your word instead of “AnyOneWordFromJobScript”.


USE MSDB
GO

SELECT Sj.Name As JobName,
       St.Command As JobScript
FROM   Sysjobs Sj
       JOIN Sysjobsteps St
         ON Sj.Job_id = St.Job_id
WHERE  St.Command LIKE '%AnyOneWordFromJobScript%' 

Script to get job name from command

 

Regards,

Nirav Gajjar

Script to get failed jobs

Posted on by 
Script to get failed jobs
Below is a TSQL query to get failed jobs last hour.
if you want failed jobs for whole day then comment "AND Run_time >= @Ctime" line

DECLARE @Cdate INT
DECLARE @Ctime INT

SET @Cdate = CONVERT(CHAR(8), (SELECT
                                 Dateadd (DAY, ( 0 ), Getdate())), 112)
SET @Ctime = Cast(Replace(CONVERT(CHAR(8), (SELECT
                                              Dateadd (HOUR, -1, Getdate())), 114), ':', '') AS INT);

WITH cte
     AS (SELECT DISTINCT
           Row_number()
             OVER(
               partition BY T2.name
               ORDER BY run_time DESC) AS rnk,
           Substring(T2.Name, 1, 40)   AS 'Job Name',
           T1.Step_id                  AS 'Step_id',
           T1.Step_name                AS 'Step Name',
           T1.Message                  AS 'Error Message',
           Run_time
         FROM
           MSDB.DBO.Sysjobhistory T1
         JOIN   MSDB.DBO.Sysjobs T2
           ON T1.Job_id = T2.Job_id
         WHERE
          T1.Run_status NOT IN ( 1, 4 )
          AND T1.Step_id != 0
          AND Run_date     = @Cdate
          AND Run_time >= @Ctime)
SELECT
  *
FROM
  cte
WHERE
  rnk = 1 



Regards, Nirav gajjar