server reboots and Job timings

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

server reboots and Job timings

Author

Topic

DB King
Starting Member

21 Posts

Posted - 2006-09-29 : 05:40:17

hi

I am trying to understand the best time to bring down a server for maintenance. I have a number of SQL 2000 and SQL 2005 servers which have many jobs configured to execute at numerous times during the day and night.

I would like to ascertain when I can safely reboot a server without disturbing a job. I would like to do this using a single query which will give me a list of offending jobs given a time period. Anyone know how to do this ?? with MSDB I suppose ?

regards

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2006-09-29 : 05:57:55

Would something like this help?
SELECT
j.Name,
s.Next_Run_Date,
RIGHT('000000' + LTRIM(RTRIM(STR(s.Next_run_time, 6))), 6) AS Next_Run_Time
FROM
msdb.dbo.sysjobs j
inner join msdb.dbo.sysjobschedules s
on j.job_id = s.job_id
WHERE
j.enabled = 1 AND
s.Next_Run_Date > CAST(CONVERT(VARCHAR, GETDATE(), 112) AS INT)
order by
s.Next_Run_Date,
s.Next_Run_Time

Duane.

DB King
Starting Member

21 Posts

Posted - 2006-09-29 : 06:07:41

Almost. I would like to specify a time and receive a list of jobs that occur at that time.

Kristen
Test

22859 Posts

Posted - 2006-09-29 : 07:30:44

That's handy Ditch! but it misses jobs scheduled for today, but after current time. I made that change (and reformatted to my own style as I have stored it in my "snippets" folder - sorry about that!


SELECT	J.[name],
	S.next_run_date,
	[next_run_time] = RIGHT('000000' + LTRIM(RTRIM(STR(S.next_run_time, 6))), 6)
FROM	msdb.dbo.sysjobs AS J
	join msdb.dbo.sysjobschedules AS S
		on J.job_id = S.job_id
WHERE	    J.enabled = 1 
	AND 
	(
		    S.next_run_date > CONVERT(int, CONVERT(VARCHAR(8), GETDATE(), 112))
		OR (
			    S.next_run_date = CONVERT(int, CONVERT(VARCHAR(8), GETDATE(), 112))
			AND S.next_run_time > CONVERT(int, REPLACE(CONVERT(VARCHAR(8), GETDATE(), 108), ':', ''))
		)
	)
ORDER BY
	S.next_run_date,
	S.next_run_time

DB_King: Have a look at sysjobschedules in BoL for details of the "freq_XXXX" columns that will probably give you what you want with some small modifications to Ditch's query. We stop all jobs running before we reboot the SQL box to prevent things being half-way-though. Sometimes patches etc. restart SQL and then want another cycle, so its a pain if something has then started.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources