quote:

---

Originally posted by djj55

Look at the msdb..sysjobhistory and msdb..sysjobs tables.

---

quote:

---

Originally posted by visakh16

Also running packages folder in MSDB and also on executions report in SSMS

---

quote:

---

Originally posted by visakh16

Also running packages folder in MSDB and also on executions report in SSMS

---

-- copy jobs into temporary table (not a #temp)
use msdb;
GO
SELECT	job_id, enabled
INTO	tmpJobs;
GO

-- disable all jobs --
UPDATE	sysjobs
SET	enabled = 0;
GO

-- Do whatever work you need to do --

-- enable jobs --
UPDATE	j
SET	enables = 1
FROM	sysjobs j
JOIN	tmpJobs t
On	t.job_id = j.job_id
WHERE	t.enabled = 1;
GO

-- clean up --
DROP TABLE tmpJobs;
GO

quote:

---

Originally posted by russell

When I need to temporarily disable all jobs I just disable SQL Agent. But seems this may be useful for you in some instances:

-- copy jobs into temporary table (not a #temp)
use msdb;
GO
SELECT	job_id, enabled
INTO	tmpJobs;
GO

-- disable all jobs --
UPDATE	sysjobs
SET	enabled = 0;
GO

-- Do whatever work you need to do --

-- enable jobs --
UPDATE	j
SET	enables = 1
FROM	sysjobs j
JOIN	tmpJobs t
On	t.job_id = j.job_id
WHERE	t.enabled = 1;
GO

-- clean up --
DROP TABLE tmpJobs;
GO

---

-- Jobs that are running NOW! ...
SELECT	A.run_requested_Date, 
	[Elapsed (Sec)] = DATEDIFF(SECOND, A.run_requested_Date, GetDate()),
	J.Name
-- 	, J.Originating_Server, 
-- 	J.job_ID
FROM	msdb.dbo.sysjobs_view AS J
	JOIN msdb.dbo.sysjobactivity AS A
		 ON A.job_id = J.job_id
	JOIN msdb.dbo.syssessions AS S
		 ON S.session_id = A.session_id
	JOIN
	(
		SELECT	[max_agent_start_date] = MAX(agent_start_date)
		FROM	msdb.dbo.syssessions
	) AS SMax
		 ON SMax.max_agent_start_date = S.agent_start_date
WHERE	    run_Requested_date IS NOT NULL
	AND stop_execution_date IS NULL
--
-- ... and jobs that are Starting soon!!
SELECT	[JobEnabled] = JOB.enabled,
--TODO These show OVERDUE for a while after job completes. I don't know what changes them to next-actual-scheduled-time
	[next_run_in (mins)] = DATEDIFF(Minute, GetDate(), msdb.dbo.agent_datetime(SCH.next_run_date, SCH.next_run_time)),
	[next_run_date] = CONVERT(varchar(17), msdb.dbo.agent_datetime(SCH.next_run_date, SCH.next_run_time), 113),
	[JobName] = JOB.name
-- 	, JOB.job_id,
-- 	[CategoryName] = CAT.name,
-- 	SRV.last_run_outcome,
-- 	SRV.last_outcome_message,
-- 	[last_run_date] = msdb.dbo.agent_datetime(SRV.last_run_date, SRV.last_run_time),
-- 	SRV.last_run_duration,
-- 	[NotifyOperatorName] = OP.name,
-- 	OP.email_address,
-- 	[ScheduleName] = SSCH.name,
-- 	[ScheduleEnabled] = SSCH.enabled,
-- 	SSCH.freq_type,
-- 	SSCH.freq_interval,
-- 	SSCH.freq_subday_interval,
-- 	SSCH.freq_subday_type,
-- 	SSCH.freq_relative_interval,
-- 	SSCH.freq_recurrence_factor,
-- 	SSCH.active_start_date,
-- 	SSCH.active_end_date,
-- 	SSCH.active_start_time,
-- 	SSCH.active_end_time
FROM	msdb.dbo.sysjobs AS job
	LEFT OUTER JOIN msdb.dbo.syscategories AS cat
		 ON CAT.category_id = JOB.category_id
	LEFT OUTER JOIN msdb.dbo.sysoperators AS op
		 ON OP.id = JOB.notify_page_operator_id
	LEFT OUTER JOIN msdb.dbo.sysjobservers AS srv
		 ON SRV.job_id = JOB.job_id
	LEFT OUTER JOIN msdb.dbo.sysjobschedules AS sch
		 ON SCH.job_id = JOB.job_id
	LEFT OUTER JOIN msdb.dbo.sysschedules AS ssch
		 ON SSCH.schedule_id = SCH.schedule_id
WHERE	    JOB.enabled = 1
	AND msdb.dbo.agent_datetime(SCH.next_run_date, SCH.next_run_time) < DATEADD(Minute, 60, GetDate())
ORDER BY SCH.next_run_date, SCH.next_run_time
	, JobName

quote:

---

Originally posted by russell

Tara, sysjobs isn't "protected" as a system table.

---