Get Scheduled Jobs from Multiple Servers

,

I needed to be able to loop thru several SQL servers and get the active maintenance jobs and the next run times. I hobled together several different scripts and came up with the below list.

Thanks to Rahul Vairagi (http://sqlserver2005forum.blogspot.com/2011/02/script-to-know-jobs-scheduled.html) and Kishore (http://www.dev-exchange.com/cms_view_article.php?aid=27) for doing the heavy lifting.

Run this from a server which you have rights to the MSDB database and each linked server must have the same rights. The original article for the Job listing by Rahul Vairagi (link above), put this code in a stored procedure but here it can simply be pasted in to a query window in SSMS.

-- Use the to find "Maintenance" Jobs. Just put a "%" in it for all jobs. % is a wild card in TSQL
DECLARE @JobName VARCHAR(50)
SET @JobName = 'maintenance%'

-- Server List, ALLWAYS have a comma at end
-- This list MUST be linked servers from whatever SQL server you are running it on
DECLARE @ServerName VARCHAR(30) 
       ,@position INT 
       ,@ServerList varchar(8000); 
SET @position=0; 
SET @ServerList='<[SQLServer1]>,<[SQLServer2\ServerInstance]>,<[SQLServer3],'; 

-- Variable to hold dynamic SQL
DECLARE @sql VARCHAR(8000)     

-- Beginning of loop for servers
WHILE charindex(',',@ServerList)>0 
BEGIN 

-- Get the next server in the list
SET @ServerName = cast(substring(@ServerList,0, charindex(',',@ServerList)) as VARCHAR(30)) 

-- This gets all the jobs, part 2 below queries the results
SET @sql = 'WITH OurJobs AS (
    SELECT  job.[name]
     , CASE job.[description] WHEN ''No description available.'' THEN NULL ELSE job.description END AS Description
     , CASE sched.next_run_date
            WHEN 0 THEN ''Never''
            ELSE
              CONVERT(varchar(10), CONVERT(smalldatetime, CAST(sched.next_run_date as varchar), 120), 120)+'' ''+
              RIGHT(''0''+CAST((sched.next_run_time/10000) AS VARCHAR), 2)+'':''+
              RIGHT(''0''+CAST((sched.next_run_time-((sched.next_run_time/10000)*10000))/100 AS VARCHAR), 2)+'':''+
              RIGHT(''0''+CAST((sched.next_run_time-((sched.next_run_time/10000)*10000)-((sched.next_run_time-((sched.next_run_time/10000)*10000))/100*100)) AS VARCHAR), 2)
      END AS NextRunDateTime

      , (
        SELECT CASE last_run_date
            WHEN 0 THEN ''Never''
            ELSE
              CONVERT(varchar(10), CONVERT(smalldatetime, CAST(last_run_date as varchar), 120), 120)+'' ''+
              RIGHT(''0''+CAST((last_run_time/10000) AS VARCHAR), 2)+'':''+
              RIGHT(''0''+CAST((last_run_time-((last_run_time/10000)*10000))/100 AS VARCHAR), 2)+'':''+
              RIGHT(''0''+CAST((last_run_time-((last_run_time/10000)*10000)-((last_run_time-((last_run_time/10000)*10000))/100*100)) AS VARCHAR), 2)
          END AS LastRunDateTime
        FROM ' + @ServerName + '.msdb.dbo.sysjobsteps
        WHERE job_id = job.job_id AND step_id = (
          SELECT MAX(step_id)
          FROM ' + @ServerName + '.msdb.dbo.sysjobsteps
          WHERE job_id = job.job_id
        )
      ) as LastSuccessfulExecution
      , job.date_modified
   FROM ' + @ServerName + '.msdb.dbo.sysjobs job JOIN ' + @ServerName + '.msdb.dbo.sysjobschedules sched
        ON sched.job_id = job.job_id
    WHERE job.enabled = 1 -- remove this if you wish to return all jobs
        AND sched.next_run_date > 0 --GETDATE()
)

-- Part 2, queries table of jobs
SELECT * FROM OurJobs 
WHERE DATEDIFF(hh, GETDATE(), NextRunDateTime) <= ' + CAST(@HoursForward AS CHAR(3)) + '
AND NextRunDateTime > GETDATE()
AND name LIKE ''' + @JobName + '''
ORDER BY NextRunDateTime ASC'

--PRINT @sql
EXEC (@sql)

SET @ServerList = substring(@ServerList, charindex(',',@ServerList)+1, LEN(@ServerList) - @position);

END

Rate

4 (1)

Share

Share

Rate

4 (1)