What SQL JOb's are running on 2005 Server

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Sreedhar Kantae » What SQL JOb's are running on 2005 Server

What SQL JOb's are running on 2005 Server

Rate Topic

Display Mode

Topic Options

Author

Message

sreedhar-141534

Posted Friday, April 10, 2009 7:25 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, April 12, 2013 11:35 AM
Points: 0, Visits: 169

Comments posted to this topic are about the item What SQL JOb's are running on 2005 Server

Post #694805

Chris Hirst

Posted Friday, May 01, 2009 9:13 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, August 24, 2009 6:03 AM
Points: 6, Visits: 19

Thanks for this offering.

Error messages are:

Msg 102, Level 15, State 1, Line 8
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Line 32
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Line 36
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Line 37
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Line 46
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Line 54
Incorrect syntax near ' '.

Post #708517

Joe Wagner

Posted Friday, May 01, 2009 9:53 AM

Old Hand

Group: General Forum Members
Last Login: 2 days ago @ 9:27 AM
Points: 354, Visits: 359

To get rid of the errors:
1) Paste into new query window
2) Select the whole query
3) From the toolbar, choose Edit\Advanced\Delete Horizontal White Space

Post #708560

Chris Hirst

Posted Friday, May 01, 2009 11:05 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, August 24, 2009 6:03 AM
Points: 6, Visits: 19

Thanks - that worked.

Post #708604

Ninja's_RGR'us

Posted Monday, December 13, 2010 6:20 AM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 3:18 AM
Points: 21,359, Visits: 9,541

Here's a version that works on case sensitive servers. I've also refformated with SQL Refactor.

IF OBJECT_ID('tempdb..#enum_job') > 0 
    DROP TABLE #enum_job

declare
    @MxCnt INT
  , @Cnt INT 
declare @JobID varbinary(max)
declare @RunnableJobs int 
declare @Owner varchar(20)

/****Pass the parameters for viewing the job lists***********************************/
SET @RunnableJobs = null 
		--> 1: All runnable jobs 
		--> 0: All Non Runnable jobs 
		--> Null: All enabled jobs 
/************************************************************************************/
SET @Owner = system_user
create table #enum_job
(
  RowID INT IDENTITY(1 , 1)
, Job_ID uniqueidentifier
, Last_Run_Date int
, Last_Run_Time int
, Next_Run_Date int
, Next_Run_Time int
, Next_Run_Schedule_ID int
, Requested_To_Run int
, Request_Source int
, Request_Source_ID varchar(100)
, Running int
, Current_Step int
, Current_Retry_Attempt int
, State int
, JobID_var varchar(max)
) 
insert into
    #enum_job
    (
      Job_ID
    , Last_Run_Date
    , Last_Run_Time
    , Next_Run_Date
    , Next_Run_Time
    , Next_Run_Schedule_ID
    , Requested_To_Run
    , Request_Source
    , Request_Source_ID
    , Running
    , Current_Step
    , Current_Retry_Attempt
    , State
    )
    execute master.dbo.xp_sqlagent_enum_jobs 1 , @Owner
 --<1 -para -->sysadmin> --<2 -para -->owner>    

set @MxCnt = @@identity
set @Cnt = 1
while ( @Cnt <= @MxCnt )
    BEGIN
        set @JobID = (
                       select
                        Job_ID
                       from
                        #enum_job
                       where
                        RowID = @Cnt
                     )
        print @JobID

        update
            #enum_job
        set 
            JobID_var = (
                          select
                            cast('' as xml).value('xs:hexBinary(sql:variable("@JobID") )' ,
                                                  'varchar(max)')
                        )
        where
            RowID = @Cnt

        set @Cnt = @Cnt + 1
    end

SELECT
    j.name as JobName
  ,
/*****************************************/ CASE WHEN lr.last_run_date = 0
                                                 THEN 'Never Ran'
                                                 ELSE convert(varchar(100) , convert(datetime , substring(cast(lr.last_run_date as varchar(100)) , 1 , 4)
                                                      + '-'
                                                      + substring(cast(lr.last_run_date as varchar(100)) , 5 , 2)
                                                      + '-'
                                                      + substring(cast(lr.last_run_date as varchar(100)) , 7 , 8)) , 6)
                                            END AS LastRunDate
  , CASE when len(lr.last_run_time) >= 5
              and len(lr.last_run_time) <= 6
         then substring(cast(lr.last_run_time as varchar(10)) , 1 ,
                        ( case when len(lr.last_run_time) = 5 then 1
                               else 2
                          end )) + '-'
              + substring(cast(lr.last_run_time as varchar(10)) ,
                          ( case when len(lr.last_run_time) = 5 then 2
                                 else 3
                            end ) , 2) + '-'
              + substring(cast(lr.last_run_time as varchar(10)) ,
                          ( case when len(lr.last_run_time) = 5 then 4
                                 else 5
                            end ) , 2)
         when len(lr.last_run_time) = 4
         then '12:'
              + ( CASE WHEN LEN(substring(cast(lr.last_run_time as varchar(10)) ,
                                          1 ,
                                          ( case when len(lr.last_run_time) = 3
                                                 then 1
                                                 else 2
                                            end ))) > 1
                       THEN substring(cast(lr.last_run_time as varchar(10)) ,
                                      1 ,
                                      ( case when len(lr.last_run_time) = 3
                                             then 1
                                             else 2
                                        end ))
                       ELSE '0'
                            + substring(cast(lr.last_run_time as varchar(10)) ,
                                        1 ,
                                        ( case when len(lr.last_run_time) = 3
                                               then 1
                                               else 2
                                          end ))
                  END ) + ':'
              + ( CASE WHEN LEN(substring(cast(lr.last_run_time as varchar(10)) ,
                                          ( case when len(lr.last_run_time) = 3
                                                 then 2
                                                 else 3
                                            end ) , len(lr.last_run_time))) > 1
                       THEN substring(cast(lr.last_run_time as varchar(10)) ,
                                      ( case when len(lr.last_run_time) = 3
                                             then 2
                                             else 3
                                        end ) , len(lr.last_run_time))
                       ELSE '0'
                            + substring(cast(lr.last_run_time as varchar(10)) ,
                                        ( case when len(lr.last_run_time) = 3
                                               then 2
                                               else 3
                                          end ) , len(lr.last_run_time))
                  END )
         else '12:00:00'
    end AS LastRunTime
  , case when lr.last_run_outcome = 1 then 'Success'
         when lr.last_run_outcome = 0 then 'Failed'
         else 'Cancel'
    End AS LastRunStatus
  , case when len(lr.last_run_duration) <= 2
         then '00 hrs : 00 min : ' + ' '
              + cast(lr.last_run_duration as varchar(100)) + 'sec'
         when len(lr.last_run_duration) > 2
              and len(lr.last_run_duration) <= 4
         then '00 hrs : '
              + substring(cast(lr.last_run_duration as varchar(100)) , 1 ,
                          ( len(lr.last_run_duration) - 2 )) + ' min : ' + ' '
              + substring(cast(lr.last_run_duration as varchar(100)) ,
                          ( len(lr.last_run_duration) - 1 ) ,
                          len(lr.last_run_duration)) + 'sec'
         when len(lr.last_run_duration) >= 5
              and len(last_run_duration) <= 6
         then substring(cast(lr.last_run_duration as varchar(100)) , 1 ,
                        ( case when len(lr.last_run_duration) = 5 then 1
                               else 2
                          end )) + ' hrs :'
              + substring(cast(lr.last_run_duration as varchar(100)) ,
                          ( case when len(lr.last_run_duration) = 5 then 2
                                 else 3
                            end ) ,
                          ( case when len(lr.last_run_duration) = 5 then 2
                                 else 2
                            end )) + ' min : '
              + substring(cast(lr.last_run_duration as varchar(100)) ,
                          ( case when len(lr.last_run_duration) = 5 then 4
                                 else 5
                            end ) ,
                          ( case when len(lr.last_run_duration) = 5 then 2
                                 else 2
                            end )) + ' sec : '
         else cast(substring(cast(lr.last_run_duration as varchar(10)) , 1 , 3)
              / 24 as varchar(10)) + '' + 'days'
    end AS 'LastRunDuration'
  ,

/*****************************************/ CASE WHEN p.Next_Run_Date = 0
                                                 THEN 'Never Ran'
                                                 ELSE convert(varchar(100) , convert(datetime , substring(cast(p.Next_Run_Date as varchar(100)) , 1 , 4)
                                                      + '-'
                                                      + substring(cast(p.Next_Run_Date as varchar(100)) , 5 , 2)
                                                      + '-'
                                                      + substring(cast(p.Next_Run_Date as varchar(100)) , 7 , 8)) , 6)
                                            END AS NextRunDate
  , CASE WHEN LEN(p.Next_Run_Time) > 5
         THEN substring(cast(p.Next_Run_Time as varchar(100)) , 1 , 2) + '-'
              + substring(cast(p.Next_Run_Time as varchar(100)) , 3 , 2) + '-'
              + substring(cast(p.Next_Run_Time as varchar(100)) , 5 , 2)
         WHEN LEN(p.Next_Run_Time) <= 5
              and LEN(p.Next_Run_Time) > 1
         THEN '0' + substring(cast(p.Next_Run_Time as varchar(100)) , 1 , 1)
              + '-' + substring(cast(p.Next_Run_Time as varchar(100)) , 2 , 2)
              + '-' + substring(cast(p.Next_Run_Time as varchar(100)) , 4 , 2)
         ELSE '12:00:00'
    END AS NextRunTime
  ,

/*****************************************/ isnull(db_name(s.dbid) , '') DatabaseName
  , CASE when p.Running = 1 then 'Running'
         else 'Idle'
    end AS Current_Job_Status
  , CASE WHEN Current_Step > 0
         THEN 'Step:' + ' ' + cast(Current_Step as varchar(10))
              + ' execution under process.'
         ELSE 'Idle'
    END AS Current_Step_Execution
  , isnull(st.text , '--') AS CurrentQueryString
  , isnull(s.Spid , '') Spid
  , 'IsBlocked' = case when s.blocked <> 0
                       then 'Blocked By SPID: ' + ''
                            + cast(s.blocked as varchar(10))
                       else '0'
                  end
  , isnull(CPU , 0) CPU
  , isnull(Physical_IO , 0) Physical_IO
  , isnull(Memusage , 0) Memusage
  , 'spid status' = isnull(s.status , '--')
  , 'WaitResourceType' = isnull(s.LastWaitType , '--')
FROM
    #enum_job p
    JOIN msdb.dbo.sysjobs j
        on j.job_id = p.Job_ID
    LEFT JOIN master.sys.sysprocesses s
        on substring(s.program_name , 32 , 32) = p.JobID_var
    LEFT JOIN msdb.dbo.sysjobservers lr
        on lr.job_id = p.Job_ID
    LEFT JOIN sys.dm_exec_requests dm
        on dm.session_id = s.spid
    OUTER APPLY sys.dm_exec_sql_text(dm.sql_handle) AS st
WHERE
    j.enabled = 1
    and p.Running = isnull(@RunnableJobs , p.Running)
ORDER BY
    JobName


IF OBJECT_ID('tempdb..#enum_job') > 0 
    DROP TABLE #enum_job

Post #1033749

Ninja's_RGR'us

Posted Monday, December 13, 2010 8:11 AM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 3:18 AM
Points: 21,359, Visits: 9,541

Here's a version with the times format correct with jobs just barely past midnight

IF OBJECT_ID('tempdb..#enum_job') > 0 
    DROP TABLE #enum_job

DECLARE
    @MxCnt INT
  , @Cnt INT 
DECLARE @JobID VARBINARY(MAX)
DECLARE @RunnableJobs INT 
DECLARE @Owner VARCHAR(20)

/****Pass the parameters for viewing the job lists***********************************/
SET @RunnableJobs = NULL 
		--> 1: All runnable jobs 
		--> 0: All Non Runnable jobs 
		--> Null: All enabled jobs 
/************************************************************************************/
SET @Owner = SYSTEM_USER
CREATE TABLE #enum_job
(
  RowID INT IDENTITY(1 , 1)
, Job_ID UNIQUEIDENTIFIER
, Last_Run_Date INT
, Last_Run_Time VARCHAR(6)
, Next_Run_Date INT
, Next_Run_Time VARCHAR(6)
, Next_Run_Schedule_ID INT
, Requested_To_Run INT
, Request_Source INT
, Request_Source_ID VARCHAR(100)
, Running INT
, Current_Step INT
, Current_Retry_Attempt INT
, State INT
, JobID_var VARCHAR(MAX)
) 
INSERT INTO
    #enum_job
    (
      Job_ID
    , Last_Run_Date
    , Last_Run_Time
    , Next_Run_Date
    , Next_Run_Time
    , Next_Run_Schedule_ID
    , Requested_To_Run
    , Request_Source
    , Request_Source_ID
    , Running
    , Current_Step
    , Current_Retry_Attempt
    , State
    )
    EXECUTE master.dbo.xp_sqlagent_enum_jobs 1 , @Owner
 --<1 -para -->sysadmin> --<2 -para -->owner>    

SET @MxCnt = @@identity
SET @Cnt = 1
WHILE ( @Cnt <= @MxCnt )
    BEGIN
        SET @JobID = (
                       SELECT
                        Job_ID
                       FROM
                        #enum_job
                       WHERE
                        RowID = @Cnt
                     )
        PRINT @JobID

        UPDATE
            #enum_job
        SET 
            JobID_var = (
                          SELECT
                            CAST('' AS XML).value('xs:hexBinary(sql:variable("@JobID") )' ,
                                                  'varchar(max)')
                        )
        WHERE
            RowID = @Cnt

        SET @Cnt = @Cnt + 1
    END

UPDATE
    #enum_job
SET 
    Next_Run_Time = RIGHT('000000' + Next_Run_Time , 6)

SELECT
    j.name AS JobName
  , j.description AS JobDescription
  , c.[name] AS JobCategoryName
  ,
/*****************************************/ CASE WHEN lr.last_run_date = 0
                                                 THEN 'Never Ran'
                                                 ELSE CONVERT(VARCHAR(100) , CONVERT(DATETIME , SUBSTRING(CAST(lr.last_run_date AS VARCHAR(100)) , 1 , 4)
                                                      + '-'
                                                      + SUBSTRING(CAST(lr.last_run_date AS VARCHAR(100)) , 5 , 2)
                                                      + '-'
                                                      + SUBSTRING(CAST(lr.last_run_date AS VARCHAR(100)) , 7 , 8)) , 6)
                                            END AS LastRunDate
  , SUBSTRING(RIGHT('000000' + CAST(lr.last_run_time AS VARCHAR(100)) , 6) , 1 ,
              2) + ':' + SUBSTRING(RIGHT('000000'
                                         + CAST(lr.last_run_time AS VARCHAR(100)) ,
                                         6) , 3 , 2) + ':'
    + SUBSTRING(RIGHT('000000' + CAST(lr.last_run_time AS VARCHAR(100)) , 6) ,
                5 , 2) AS LastRunTime
  , CASE WHEN lr.last_run_outcome = 1 THEN 'Success'
         WHEN lr.last_run_outcome = 0 THEN 'Failed'
         ELSE 'Cancel'
    END AS LastRunStatus
  , CASE WHEN LEN(lr.last_run_duration) <= 2
         THEN '00 hrs : 00 min : ' + ' '
              + CAST(lr.last_run_duration AS VARCHAR(100)) + 'sec'
         WHEN LEN(lr.last_run_duration) > 2
              AND LEN(lr.last_run_duration) <= 4
         THEN '00 hrs : '
              + SUBSTRING(CAST(lr.last_run_duration AS VARCHAR(100)) , 1 ,
                          ( LEN(lr.last_run_duration) - 2 )) + ' min : ' + ' '
              + SUBSTRING(CAST(lr.last_run_duration AS VARCHAR(100)) ,
                          ( LEN(lr.last_run_duration) - 1 ) ,
                          LEN(lr.last_run_duration)) + 'sec'
         WHEN LEN(lr.last_run_duration) >= 5
              AND LEN(last_run_duration) <= 6
         THEN SUBSTRING(CAST(lr.last_run_duration AS VARCHAR(100)) , 1 ,
                        ( CASE WHEN LEN(lr.last_run_duration) = 5 THEN 1
                               ELSE 2
                          END )) + ' hrs :'
              + SUBSTRING(CAST(lr.last_run_duration AS VARCHAR(100)) ,
                          ( CASE WHEN LEN(lr.last_run_duration) = 5 THEN 2
                                 ELSE 3
                            END ) ,
                          ( CASE WHEN LEN(lr.last_run_duration) = 5 THEN 2
                                 ELSE 2
                            END )) + ' min : '
              + SUBSTRING(CAST(lr.last_run_duration AS VARCHAR(100)) ,
                          ( CASE WHEN LEN(lr.last_run_duration) = 5 THEN 4
                                 ELSE 5
                            END ) ,
                          ( CASE WHEN LEN(lr.last_run_duration) = 5 THEN 2
                                 ELSE 2
                            END )) + ' sec : '
         ELSE CAST(SUBSTRING(CAST(lr.last_run_duration AS VARCHAR(10)) , 1 , 3)
              / 24 AS VARCHAR(10)) + '' + 'days'
    END AS 'LastRunDuration'
  ,

/*****************************************/ CASE WHEN p.Next_Run_Date = 0
                                                 THEN 'Not scheduled'
                                                 ELSE CONVERT(VARCHAR(100) , CONVERT(DATETIME , SUBSTRING(CAST(p.Next_Run_Date AS VARCHAR(100)) , 1 , 4)
                                                      + '-'
                                                      + SUBSTRING(CAST(p.Next_Run_Date AS VARCHAR(100)) , 5 , 2)
                                                      + '-'
                                                      + SUBSTRING(CAST(p.Next_Run_Date AS VARCHAR(100)) , 7 , 8)) , 6)
                                            END AS NextRunDate
  , CASE WHEN p.Next_Run_Date <> 0
         THEN SUBSTRING(CAST(p.Next_Run_Time AS VARCHAR(100)) , 1 , 2) + ':'
              + SUBSTRING(CAST(p.Next_Run_Time AS VARCHAR(100)) , 3 , 2) + ':'
              + SUBSTRING(CAST(p.Next_Run_Time AS VARCHAR(100)) , 5 , 2)
         ELSE ''
    END AS NextRunTime
  ,
/*****************************************/ ISNULL(DB_NAME(s.dbid) , '') DatabaseName
  , CASE WHEN p.Running = 1 THEN 'Running'
         ELSE 'Idle'
    END AS Current_Job_Status
  , CASE WHEN Current_Step > 0
         THEN 'Step:' + ' ' + CAST(Current_Step AS VARCHAR(10))
              + ' execution under process.'
         ELSE 'Idle'
    END AS Current_Step_Execution
  , ISNULL(st.text , '--') AS CurrentQueryString
  , ISNULL(s.Spid , '') Spid
  , 'IsBlocked' = CASE WHEN s.blocked <> 0
                       THEN 'Blocked By SPID: ' + ''
                            + CAST(s.blocked AS VARCHAR(10))
                       ELSE '0'
                  END
  , ISNULL(CPU , 0) CPU
  , ISNULL(Physical_IO , 0) Physical_IO
  , ISNULL(Memusage , 0) Memusage
  , 'spid status' = ISNULL(s.status , '--')
  , 'WaitResourceType' = ISNULL(s.LastWaitType , '--')
  , CONVERT(SMALLDATETIME, STUFF(STUFF(lr.last_run_date, 5, 0, '-'), 8, 0, '-')) AS dtLastRunDate
  , CONVERT(SMALLDATETIME, STUFF(STUFF(p.Next_Run_Date, 5, 0, '-'), 8, 0, '-')) AS dtNextRunDate

FROM
    #enum_job p
    JOIN msdb.dbo.sysjobs j
        ON j.job_id = p.Job_ID
    LEFT JOIN master.sys.sysprocesses s
        ON SUBSTRING(s.program_name , 32 , 32) = p.JobID_var
    LEFT JOIN msdb.dbo.sysjobservers lr
        ON lr.job_id = p.Job_ID
    LEFT JOIN sys.dm_exec_requests dm
        ON dm.session_id = s.spid
    LEFT OUTER JOIN msdb.dbo.syscategories c
        ON j.category_id = c.category_id
    OUTER APPLY sys.dm_exec_sql_text(dm.sql_handle) AS st
WHERE
    j.enabled = 1
    AND p.Running = ISNULL(@RunnableJobs , p.Running)
ORDER BY
    JobName


IF OBJECT_ID('tempdb..#enum_job') > 0 
    DROP TABLE #enum_job

Post #1033829

« Prev Topic | Next Topic »

Permissions