SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 server reboots and Job timings
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

DB King
Starting Member

21 Posts

Posted - 09/29/2006 :  05:40:17  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

South Africa
1466 Posts

Posted - 09/29/2006 :  05:57:55  Show Profile  Visit ditch's Homepage  Reply with Quote
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.
Go to Top of Page

DB King
Starting Member

21 Posts

Posted - 09/29/2006 :  06:07:41  Show Profile  Reply with Quote
Almost. I would like to specify a time and receive a list of jobs that occur at that time.
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 09/29/2006 :  07:30:44  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000