Need help in T sql script

Question

I need to pull the list of maintenance jobs( like index optimise and integrity check) running every week at certain time frame from the CMS folders servers. Can someone help me with the script.

Answer 1

SELECT *
FROM   msdb.dbo.sysjobs AS j
INNER JOIN msdb.dbo.syscategories AS c ON c.category_id = j.category_id
INNER JOIN msdb.dbo.sysjobschedules AS js ON js.job_id = j.job_id
INNER JOIN msdb.dbo.sysschedules AS s ON s.schedule_id = js.schedule_id
WHERE j.enabled = 1 
  AND s.enabled = 1
  AND s.freq_type = 8       -- Weekly
  AND s.freq_interval = 1   -- Sunday
  AND s.active_start_time = 80000   -- 8:00am for example
  AND c.name = 'Database Maintenance';

Answer 2

See msdb.dbo.sysjobs...

SELECT *  
FROM   msdb.dbo.sysjobs S;  

Answer 3

This should do the trick, change filters as needed.
WITH JobHist AS
(
SELECT
C.name AS CategoryName
, J.name AS JobName
, msdb.dbo.agent_datetime(run_date, run_time) AS ExecutionStart
, RIGHT(CONCAT('000000', HH.run_duration), 6) AS Duration
FROM msdb.dbo.sysjobhistory AS HH
INNER JOIN msdb.dbo.sysjobs AS J
ON J.job_id = HH.job_id
INNER JOIN msdb.dbo.syscategories AS C
ON C.category_id = J.category_id
WHERE HH.step_id = 0 --Filters To Job Outcome Step
AND C.name = 'Database Maintenance' -- Filters to a particular Category
)
SELECT Final.CategoryName
, Final.JobName
, Final.ExecutionDate
, Final.ExecutionStartTime
, Final.ExecutionEndTime
FROM (
SELECT
JobHist.CategoryName
, JobHist.JobName
, CAST(JobHist.ExecutionStart AS DATE) AS ExecutionDate
, CAST(JobHist.ExecutionStart AS TIME) AS ExecutionStartTime
, CAST(DATEADD(
ss
, (CAST(LEFT(JobHist.Duration, 2) AS INT) * 3600)

• (CAST(SUBSTRING(JobHist.Duration, 3, 2) AS INT) * 60) + (RIGHT(JobHist.Duration, 2))
  , JobHist.ExecutionStart
  ) AS TIME) AS ExecutionEndTime
  FROM JobHist
  ) AS Final
  WHERE DATEPART(WEEKDAY, Final.ExecutionDate) = 1 --Filter To Sunday
  AND CAST(GETDATE() AS TIME) --Filters to a specific Time change to match the time you want
  BETWEEN Final.ExecutionStartTime AND Final.ExecutionEndTime

Answer 4

Hi @Narender Reddy ，

Please refer to:dbo.sysjobs、dbo.syscategories (Transact-SQL)、dbo.sysjobschedules (Transact-SQL)

The document contains all types of jobs.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.