Skip to content

Commit c03033c

Browse files
ktaranovktaranov
committed
Add Disabling_Jobs_Schedules script
1 parent 87997c0 commit c03033c

1 file changed

Lines changed: 220 additions & 0 deletions

File tree

‎Scripts/Disabling_Jobs_Schedules.sql‎

Lines changed: 220 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,220 @@
1+
/*
2+
<documentation>
3+
<summary>Disabling Jobs Shedules.</summary>
4+
<returns>None.</returns>
5+
<created>2018-12-11 by Jon Shaulis</created>
6+
<modified>2020-04-18 by Konstantin Taranov</modified>
7+
<version>1.0</version>
8+
<sourceLink>https://github.com/ktaranov/sqlserver-kit/blob/master/Scripts/Disabling_Jobs_Schedules.sql.sql</sourceLink>
9+
<originalLink>https://jonshaulis.com/index.php/2018/12/11/how-to-stop-the-sql-scheduler-with-t-sql/</originalLink>
10+
</documentation>
11+
*/
12+
13+
14+
USE MSDB;
15+
16+
/*************************************************************
17+
Checking for history table. Creating it if it doesn't exist.
18+
*************************************************************/
19+
20+
IF OBJECT_ID('dbo.JobsEnabledTracker', 'U') IS NULL
21+
BEGIN
22+
CREATE TABLE [dbo].[JobsEnabledTracker](
23+
[Id] [INT] IDENTITY(1, 1) NOT NULL,
24+
[job_id] [UNIQUEIDENTIFIER] NULL,
25+
[schedule_id] [BIGINT] NULL,
26+
[enabled] [BIT] NULL);
27+
END;
28+
IF EXISTS
29+
(
30+
SELECT
31+
1
32+
FROM [dbo].[JobsEnabledTracker]
33+
WHERE [enabled] = 1
34+
)
35+
OR
36+
(
37+
SELECT
38+
COUNT(*)
39+
FROM [dbo].[JobsEnabledTracker]
40+
) = 0
41+
BEGIN
42+
PRINT 'There are jobs enabled or there are no jobs yet populated in the history table.';
43+
44+
/***********************
45+
Clear out history table
46+
***********************/
47+
48+
PRINT 'Truncating history table: dbo.JobsEnabledTracker';
49+
TRUNCATE TABLE [dbo].[JobsEnabledTracker];
50+
51+
PRINT 'Inserting records into history table: dbo.JobsEnabledTracker';
52+
53+
/******************************
54+
Add in values to history table
55+
******************************/
56+
57+
INSERT INTO [dbo].[JobsEnabledTracker]
58+
(
59+
[job_id],
60+
[schedule_id],
61+
[enabled]
62+
)
63+
SELECT
64+
[jss].[job_id],
65+
[jss].[schedule_id],
66+
1 AS 'enabled'
67+
FROM [msdb].[dbo].[sysschedules] AS [ss]
68+
INNER JOIN [msdb].[dbo].[sysjobschedules] AS [jss] ON [jss].[schedule_id] = [ss].[schedule_id]
69+
WHERE [ss].[enabled] = 1;
70+
71+
/**********************************************************************************
72+
Table variable to hold schedules and jobs enabled. This is important for the loop.
73+
**********************************************************************************/
74+
75+
DECLARE @JobsEnabled TABLE
76+
([Id] INT
77+
PRIMARY KEY IDENTITY(1, 1),
78+
[job_id] UNIQUEIDENTIFIER,
79+
[schedule_id] BIGINT,
80+
[enabled] BIT
81+
);
82+
83+
/*****************************************
84+
Insert schedules that we need to disable.
85+
*****************************************/
86+
87+
INSERT INTO @JobsEnabled
88+
(
89+
[job_id],
90+
[schedule_id],
91+
[enabled]
92+
)
93+
SELECT
94+
[job_id],
95+
[schedule_id],
96+
[enabled]
97+
FROM [dbo].[JobsEnabledTracker];
98+
99+
/********************************
100+
Holds the job id and schedule id
101+
********************************/
102+
103+
DECLARE @jobid UNIQUEIDENTIFIER;
104+
DECLARE @scheduleid BIGINT;
105+
106+
/***********************************
107+
Holds the ID of the row in the loop
108+
***********************************/
109+
110+
DECLARE @ID INT= 0;
111+
112+
/**********************
113+
Check if records exist
114+
**********************/
115+
116+
IF EXISTS
117+
(
118+
SELECT
119+
[Id]
120+
FROM @JobsEnabled
121+
)
122+
BEGIN
123+
PRINT 'Loop mode, jobs found enabled.';
124+
125+
/**********
126+
Begin loop
127+
**********/
128+
129+
WHILE(1 = 1)
130+
BEGIN
131+
132+
/***************************************
133+
Grab jobid, scheduleid, and id of rows.
134+
***************************************/
135+
136+
SELECT
137+
@jobid =
138+
(
139+
SELECT TOP 1
140+
[job_id]
141+
FROM @JobsEnabled
142+
ORDER BY
143+
[job_id]
144+
);
145+
SELECT
146+
@scheduleid =
147+
(
148+
SELECT TOP 1
149+
[schedule_id]
150+
FROM @JobsEnabled
151+
ORDER BY
152+
[job_id]
153+
);
154+
SELECT
155+
@ID =
156+
(
157+
SELECT TOP 1
158+
[Id]
159+
FROM @JobsEnabled
160+
ORDER BY
161+
[job_id]
162+
);
163+
164+
/************************************
165+
Re-enable schedule associated to job
166+
************************************/
167+
168+
PRINT 'Disabling schedule_id: '+CAST(@scheduleid AS VARCHAR(255))+' paired to job_id: '+CAST(@jobid AS VARCHAR(255));
169+
EXEC [sp_update_schedule]
170+
@schedule_id = @scheduleid,
171+
@enabled = 0;
172+
173+
/*********************
174+
Removes row from loop
175+
*********************/
176+
177+
DELETE FROM @JobsEnabled
178+
WHERE
179+
[Id] = @ID;
180+
181+
UPDATE [dbo].[JobsEnabledTracker]
182+
SET
183+
[enabled] = 0
184+
WHERE
185+
[job_id] = @jobid
186+
AND [schedule_id] = @scheduleid;
187+
188+
/****************************
189+
No more rows, stops deleting
190+
****************************/
191+
192+
IF
193+
(
194+
SELECT
195+
COUNT(*)
196+
FROM @JobsEnabled
197+
) <= 0
198+
BEGIN
199+
BREAK
200+
END;
201+
202+
/********
203+
End Loop
204+
********/
205+
END;
206+
PRINT 'Exiting loop, disabling schedules paired to jobs complete.';
207+
208+
/**********
209+
End elseif
210+
**********/
211+
END;
212+
ELSE
213+
BEGIN
214+
PRINT 'All done';
215+
END;
216+
END;
217+
ELSE
218+
BEGIN
219+
PRINT 'YOU HAVE JOBS STILL DISABLED, EXITING SCRIPT. PLEASE RUN SCRIPT TWO FIRST.';
220+
END;

0 commit comments

Comments
 (0)