· 7 years ago · Dec 17, 2018, 02:42 PM
1SELECT * FROM
2MSDB.dbo.sysschedules ss
3INNER JOIN msdb.dbo.sysjobschedules jss
4 ON jss.schedule_id = ss.schedule_id
5WHERE ss.enabled = 1
6
7USE MSDB;
8
9/*************************************************************
10 Checking for history table. Creating it if it doesn't exist.
11*************************************************************/
12
13IF OBJECT_ID('dbo.JobsEnabledTracker', 'U') IS NULL
14BEGIN
15CREATE TABLE [dbo].[JobsEnabledTracker](
16[Id] [INT] IDENTITY(1, 1) NOT NULL,
17[job_id] [UNIQUEIDENTIFIER] NULL,
18[schedule_id] [BIGINT] NULL,
19[enabled] [BIT] NULL);
20END;
21IF EXISTS
22(
23 SELECT
24 1
25 FROM [dbo].[JobsEnabledTracker]
26 WHERE [enabled] = 1
27)
28 OR
29(
30 SELECT
31 COUNT(*)
32 FROM [dbo].[JobsEnabledTracker]
33) = 0
34 BEGIN
35 PRINT 'There are jobs enabled or there are no jobs yet populated in the history table.';
36
37/***********************
38 Clear out history table
39***********************/
40
41 PRINT 'Truncating history table: dbo.JobsEnabledTracker';
42 TRUNCATE TABLE [dbo].[JobsEnabledTracker];
43
44 PRINT 'Inserting records into history table: dbo.JobsEnabledTracker';
45
46/******************************
47 Add in values to history table
48******************************/
49
50 INSERT INTO [dbo].[JobsEnabledTracker]
51 (
52 [job_id],
53 [schedule_id],
54 [enabled]
55 )
56 SELECT
57 [jss].[job_id],
58 [jss].[schedule_id],
59 1 AS 'enabled'
60 FROM [msdb].[dbo].[sysschedules] AS [ss]
61 INNER JOIN [msdb].[dbo].[sysjobschedules] AS [jss] ON [jss].[schedule_id] = [ss].[schedule_id]
62 WHERE [ss].[enabled] = 1;
63
64/**********************************************************************************
65 Table variable to hold schedules and jobs enabled. This is important for the loop.
66**********************************************************************************/
67
68 DECLARE @JobsEnabled TABLE
69 ([Id] INT
70 PRIMARY KEY IDENTITY(1, 1),
71 [job_id] UNIQUEIDENTIFIER,
72 [schedule_id] BIGINT,
73 [enabled] BIT
74 );
75
76/*****************************************
77 Insert schedules that we need to disable.
78*****************************************/
79
80 INSERT INTO @JobsEnabled
81 (
82 [job_id],
83 [schedule_id],
84 [enabled]
85 )
86 SELECT
87 [job_id],
88 [schedule_id],
89 [enabled]
90 FROM [dbo].[JobsEnabledTracker];
91
92/********************************
93 Holds the job id and schedule id
94********************************/
95
96 DECLARE @jobid UNIQUEIDENTIFIER;
97 DECLARE @scheduleid BIGINT;
98
99/***********************************
100 Holds the ID of the row in the loop
101***********************************/
102
103 DECLARE @ID INT= 0;
104
105/**********************
106 Check if records exist
107**********************/
108
109 IF EXISTS
110 (
111 SELECT
112 [Id]
113 FROM @JobsEnabled
114 )
115 BEGIN
116 PRINT 'Loop mode, jobs found enabled.';
117
118/**********
119 Begin loop
120**********/
121
122 WHILE(1 = 1)
123 BEGIN
124
125/***************************************
126 Grab jobid, scheduleid, and id of rows.
127***************************************/
128
129 SELECT
130 @jobid =
131 (
132 SELECT TOP 1
133 [job_id]
134 FROM @JobsEnabled
135 ORDER BY
136 [job_id]
137 );
138 SELECT
139 @scheduleid =
140 (
141 SELECT TOP 1
142 [schedule_id]
143 FROM @JobsEnabled
144 ORDER BY
145 [job_id]
146 );
147 SELECT
148 @ID =
149 (
150 SELECT TOP 1
151 [Id]
152 FROM @JobsEnabled
153 ORDER BY
154 [job_id]
155 );
156
157/************************************
158 Re-enable schedule associated to job
159************************************/
160
161 PRINT 'Disabling schedule_id: '+CAST(@scheduleid AS VARCHAR(255))+' paired to job_id: '+CAST(@jobid AS VARCHAR(255));
162 EXEC [sp_update_schedule]
163 @schedule_id = @scheduleid,
164 @enabled = 0;
165
166/*********************
167 Removes row from loop
168*********************/
169
170 DELETE FROM @JobsEnabled
171 WHERE
172 [Id] = @ID;
173
174 UPDATE [dbo].[JobsEnabledTracker]
175 SET
176 [enabled] = 0
177 WHERE
178 [job_id] = @jobid
179 AND [schedule_id] = @scheduleid;
180
181/****************************
182 No more rows, stops deleting
183****************************/
184
185 IF
186 (
187 SELECT
188 COUNT(*)
189 FROM @JobsEnabled
190 ) <= 0
191 BEGIN
192 BREAK
193 END;
194
195/********
196 End Loop
197********/
198 END;
199 PRINT 'Exiting loop, disabling schedules paired to jobs complete.';
200
201/**********
202 End elseif
203**********/
204 END;
205 ELSE
206 BEGIN
207 PRINT 'All done';
208 END;
209 END;
210 ELSE
211 BEGIN
212 PRINT 'YOU HAVE JOBS STILL DISABLED, EXITING SCRIPT. PLEASE RUN SCRIPT TWO FIRST.';
213 END;
214
215USE MSDB;
216
217/*******************************************************************************
218 Check for history table. This physical table tells us what jobs we are going to
219 enable the scheduler for.
220*******************************************************************************/
221
222IF OBJECT_ID('dbo.JobsEnabledTracker', 'U') IS NOT NULL
223BEGIN
224 IF EXISTS
225 (
226 SELECT 1
227 FROM [dbo].[JobsEnabledTracker]
228 WHERE [enabled] = 0
229 )
230 BEGIN
231 PRINT 'Jobs disabled in history table: dbo.JobsEnabledTracker found.';
232
233/**********************************************************************************
234 Table variable to hold schedules and jobs enabled. This is important for the loop.
235**********************************************************************************/
236
237 DECLARE @JobsEnabled TABLE
238 (
239 [Id] int PRIMARY KEY IDENTITY(1, 1)
240 , [job_id] uniqueidentifier
241 , [schedule_id] bigint
242 , [enabled] bit
243 );
244
245/*******************************************************************************
246 Insert schedules that we had disabled that we need to go back in and re-enable.
247*******************************************************************************/
248
249 INSERT INTO @JobsEnabled( [job_id], [schedule_id], [enabled] )
250 SELECT [job_id], [schedule_id], [enabled]
251 FROM [dbo].[JobsEnabledTracker];
252
253/********************************
254 Holds the job id and schedule id
255********************************/
256
257 DECLARE @jobid uniqueidentifier;
258 DECLARE @scheduleid bigint;
259
260/***********************************
261 Holds the ID of the row in the loop
262***********************************/
263
264 DECLARE @ID int= 0;
265
266/**********************
267 Check if records exist
268**********************/
269
270 IF EXISTS
271 (
272 SELECT [Id]
273 FROM @JobsEnabled
274 )
275 BEGIN
276 PRINT 'Loop mode, jobs found disabled.';
277
278/**********
279 Begin loop
280**********/
281
282 WHILE 1 = 1
283 BEGIN
284
285/***************************************
286 Grab jobid, scheduleid, and id of rows.
287***************************************/
288
289 SELECT @jobid =
290 (
291 SELECT TOP 1 [job_id]
292 FROM @JobsEnabled
293 ORDER BY [job_id]
294 );
295 SELECT @scheduleid =
296 (
297 SELECT TOP 1 [schedule_id]
298 FROM @JobsEnabled
299 ORDER BY [job_id]
300 );
301 SELECT @ID =
302 (
303 SELECT TOP 1 [Id]
304 FROM @JobsEnabled
305 ORDER BY [job_id]
306 );
307
308/***************************************
309 Re-enable schedule associated to job
310***************************************/
311
312 PRINT 'Enabling schedule_id: '+CAST(@scheduleid AS varchar(255))+' paired to job_id: '+CAST(@jobid AS varchar(255));
313 EXEC [sp_update_schedule] @schedule_id = @scheduleid, @enabled = 1;
314
315/*********************
316 Removes row from loop
317*********************/
318
319 DELETE FROM @JobsEnabled
320 WHERE [Id] = @ID;
321
322/***********************
323 Set job back to enabled
324***********************/
325
326 UPDATE [dbo].[JobsEnabledTracker]
327 SET [enabled] = 1
328 WHERE [job_id] = @jobid AND
329 [schedule_id] = @scheduleid;
330
331/****************************
332 No more rows, stops deleting
333****************************/
334
335 IF
336 (
337 SELECT COUNT(*)
338 FROM @JobsEnabled
339 ) <= 0
340 BEGIN
341 BREAK;
342 END;
343
344/********
345 End Loop
346********/
347 END;
348 PRINT 'Exiting loop, enabling schedules paired to jobs complete.';
349
350/**********
351 End elseif
352**********/
353 END;
354 ELSE
355 BEGIN
356 PRINT 'All done';
357 END;
358 END;
359 ELSE
360 BEGIN
361 PRINT 'dbo.JobsEnabledTracker has no disabled jobs currently.';
362 END;
363END;
364ELSE
365BEGIN
366 PRINT 'dbo.JobsEnabledTracker is NULL, you may need to run the first script to create and populate this table.';
367END;