/***************************************************************************
*
*  Name: RunningJobs.sql
*  Desc: This script returns a list of currently running SQL Server Agent
*        jobs.
*       
*        State column values:
*           0 = Not idle or suspended,
*           1 = Executing,
*           2 = Waiting For Thread,
*           3 = Between Retries,
*           4 = Idle,
*           5 = Suspended,
*           6 = WaitingForStepToFinish,
*           7 = PerformingCompletionActions
*
****************************************************************************/
BEGIN -- Code block
   SET NOCOUNT ON
   SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
   SET DEADLOCK_PRIORITY LOW
   --
   CREATE TABLE #enum_jobs (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)
   --
   INSERT INTO #enum_jobs  
   EXEC master.dbo.xp_sqlagent_enum_jobs 1, 'stuff'  
   --
   SELECT CAST(@@SERVERNAME AS VARCHAR(30))                 AS [Instance],
          CAST(REPLACE(sj.name, ',', ' ') AS VARCHAR(120))  AS [JobName],
          CAST(REPLACE(sc.name, ',', ' ') AS VARCHAR(30))   AS [Category],            
          ej.Next_Run_Date                                  AS [NextRunDate],
          ej.Next_Run_Time                                  AS [NextRunTime],
          ej.Running                                        AS [Running],
          ej.Current_Step                                   AS [Step],
          ej.Current_Retry_Attempt                          AS [RetryAttempt],
          ej.State                                          AS [State],
          CAST(GETDATE() AS SMALLDATETIME)                  AS [DateChecked],
          '@@@@@'                                           AS [Cookie]
   FROM #enum_jobs                ej
   INNER JOIN msdb..sysjobs       sj ON sj.job_id       = ej.job_id
   INNER JOIN msdb..syscategories sc ON sc.category_id  = sj.category_id
   WHERE ej.State != 4
   --
   DROP TABLE #enum_jobs
END -- Code block

Add comment


Security code
Refresh

0
0
0
s2sdefault