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)
 SQL Jobs Not Restarting
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Rhahn
Starting Member

Canada
9 Posts

Posted - 12/20/2005 :  12:51:13  Show Profile  Reply with Quote
I have a number of jobs that run on my production server. This last weekend I had to disable all jobs (msdb.dbo.sys_jobs.enabled = 0) for maintenance reasons. On Monday I ran a script that set all the enabled = 0 to enabled = 1. In Enterprise manager the next run times where correct and I manually started, or set the next run date, on any jobs that should have run during the time they where disabled. Tuesday I look and there are jobs that should have run Tuesday morning but have not. Does any one know why the timers get all screwed up when you disable a job and more importantly does any one know of a Quick way to reinsulated all the timers without manually going through each one and resetting the start date?

SQLServerDBA_Dan
Aged Yak Warrior

USA
752 Posts

Posted - 12/20/2005 :  13:27:56  Show Profile  Reply with Quote
quote:
Originally posted by Rhahn

Does any one know why the timers get all screwed up when you disable a job and more importantly does any one know of a Quick way to reinsulated all the timers without manually going through each one and resetting the start date?



You'll need to run the system proc: xp_sqlagent_notify

The SQL Server Agent keeps the job data cached. You can't just update system tables and do nothing else and still think that things will work correctly.

Here's some code to run it:


DECLARE @JOBNAME varchar(150)
SET @JOBNAME = 'INSERT JOB NAME HERE'

UPDATE msdb..sysjobschedules 
SET Enabled = 1 --, active_start_date = convert(varchar(8), getdate(), 112), active_start_time = left(replace(convert(varchar(8), dateadd(n, 1, getdate()), 8), ':', ''), 4) + '00', next_run_date = convert(varchar(8), getdate(), 112), next_run_time = left(replace(convert(varchar(8), dateadd(n, 1, getdate()), 8), ':', ''), 4) + '00'
FROM msdb..sysjobs j
	LEFT OUTER JOIN msdb..sysjobschedules s ON (s.job_id = j.job_id)
WHERE j.Name = @JOBNAME

DECLARE @job_id UNIQUEIDENTIFIER,
	@schedule_id int

SELECT @job_id = j.Job_ID, @schedule_id = S.Schedule_ID 
FROM msdb..sysjobs j (NoLock) 
	LEFT OUTER JOIN msdb..sysjobschedules s (NoLock) ON (s.job_id = j.job_id) 
WHERE j.Name = @JOBNAME

-- Update the job's version/last-modified information  
UPDATE msdb.dbo.sysjobs  
SET 	version_number = version_number + 1,  
	date_modified = GETDATE()  
WHERE (job_id = @job_id)  
  
-- Notify SQLServerAgent of the change, but only if we know the job has been cached  
IF (EXISTS (	SELECT s.*  
		FROM msdb.dbo.sysjobservers s (NoLock)
			INNER JOIN msdb.dbo.sysjobs j (NoLock) on (j.job_id = s.job_id)
		WHERE server_id = 0 and j.name = @JOBNAME)		)  
BEGIN  
	EXEC master.dbo.xp_sqlagent_notify 'S', @job_id, @schedule_id, null, 'U', 'INSERT NT ACCOUNT HERE', 1, @@trancount 
END  
GO



Have fun,
Daniel, MCP, A+
SQL Server DBA
www.dallasteam.com

Edited by - SQLServerDBA_Dan on 12/20/2005 14:26:52
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36615 Posts

Posted - 12/20/2005 :  13:31:56  Show Profile  Visit tkizer's Homepage  Reply with Quote
That's what happens when you modify system tables directly. To update jobs, you can use the system stored procedures or via the GUI in EM.

Tara Kizer
aka tduggan
Go to Top of Page

Rhahn
Starting Member

Canada
9 Posts

Posted - 12/20/2005 :  13:49:43  Show Profile  Reply with Quote
So it’s a bad idea to modify systables, check. Using EM is way to slow and my fingers get soar from repetitive clicking. How would one do this:
Update sysjobs
Set enabled = 0
Where category_id = 3
Using system_sp’s?
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36615 Posts

Posted - 12/20/2005 :  13:52:40  Show Profile  Visit tkizer's Homepage  Reply with Quote
Here is a sample of how to disable all jobs. The code is from my toolbox and looking at it now, it can be simplified a bit, but it should give you an idea of how to do things.



SET NOCOUNT ON

CREATE TABLE #Job_Names
(	
	Job_Name SYSNAME NOT NULL
)

INSERT INTO #Job_Names
SELECT name
FROM msdb.dbo.sysjobs
ORDER BY name

DECLARE @job_name SYSNAME
DECLARE @job_id UNIQUEIDENTIFIER

DECLARE disable_jobs CURSOR FOR	
SELECT Job_Name
FROM #Job_Names

SET @job_id = NULL

OPEN disable_jobs
FETCH NEXT FROM disable_jobs INTO @job_name

WHILE @@FETCH_STATUS = 0
BEGIN
	EXEC msdb.dbo.sp_verify_job_identifiers '@job_name', '@job_id', @job_name OUTPUT, @job_id OUTPUT

	EXEC msdb.dbo.sp_update_job @job_id, @enabled = 0
	
	SET @job_id = NULL

	FETCH NEXT FROM disable_jobs INTO @job_name
END

CLOSE disable_jobs
DEALLOCATE disable_jobs

DROP TABLE #Job_Names



There is a category_name parameter to sp_update_job, so perhaps that can be used to satisfy your request. Or you can modify the select into the temp table in my code to grab the jobs that you want and then use the rest of the code.

Tara Kizer
aka tduggan

Edited by - tkizer on 12/20/2005 13:54:22
Go to Top of Page

SQLServerDBA_Dan
Aged Yak Warrior

USA
752 Posts

Posted - 12/20/2005 :  13:55:28  Show Profile  Reply with Quote
quote:
Originally posted by Rhahn

So it’s a bad idea to modify systables, check. Using EM is way to slow and my fingers get soar from repetitive clicking. How would one do this:
Update sysjobs
Set enabled = 0
Where category_id = 3
Using system_sp’s?




Grrr...

quote:
Originally posted by Rhahn

You'll need to run the system proc: xp_sqlagent_notify

The SQL Server Agent keeps the job data cached. You can't just update system tables and do nothing else and still think that things will work correctly.





Daniel, MCP, A+
SQL Server DBA
www.dallasteam.com
Go to Top of Page

SQLServerDBA_Dan
Aged Yak Warrior

USA
752 Posts

Posted - 12/20/2005 :  13:59:30  Show Profile  Reply with Quote
quote:
Originally posted by tkizer

Here is a sample of how to disable all jobs. The code is from my toolbox and looking at it now, it can be simplified a bit, but it should give you an idea of how to do things.



SET NOCOUNT ON

CREATE TABLE #Job_Names
(	
	Job_Name SYSNAME NOT NULL
)

INSERT INTO #Job_Names
SELECT name
FROM msdb.dbo.sysjobs
ORDER BY name

DECLARE @job_name SYSNAME
DECLARE @job_id UNIQUEIDENTIFIER

DECLARE disable_jobs CURSOR FOR	
SELECT Job_Name
FROM #Job_Names

SET @job_id = NULL

OPEN disable_jobs
FETCH NEXT FROM disable_jobs INTO @job_name

WHILE @@FETCH_STATUS = 0
BEGIN
	EXEC msdb.dbo.sp_verify_job_identifiers '@job_name', '@job_id', @job_name OUTPUT, @job_id OUTPUT

	EXEC msdb.dbo.sp_update_job @job_id, @enabled = 0
	
	SET @job_id = NULL

	FETCH NEXT FROM disable_jobs INTO @job_name
END

CLOSE disable_jobs
DEALLOCATE disable_jobs

DROP TABLE #Job_Names



There is a category_name parameter to sp_update_job, so perhaps that can be used to satisfy your request. Or you can modify the select into the temp table in my code to grab the jobs that you want and then use the rest of the code.

Tara Kizer
aka tduggan



Maybe mine was for when you update the job step itself? Hmm... I forget what had led me down the path of calling the xp directly. If I'm not mistaken, I wasn't able to run sp_update_job sucessfully from SQLQA...

*EDIT: LOL. Ok I looked at my old code and remembered why I did it that way. The requirements were to update the job start time from code.

Daniel, MCP, A+
SQL Server DBA
www.dallasteam.com

Edited by - SQLServerDBA_Dan on 12/20/2005 14:26:10
Go to Top of Page

Rhahn
Starting Member

Canada
9 Posts

Posted - 12/20/2005 :  15:02:17  Show Profile  Reply with Quote
Thank you both, I've adapted the script to fix my start date problem. but
EXEC msdb.dbo.sp_update_jobschedule @job_id, @active_start_date = @nextDate

is throwing
Server: Msg 201, Level 16, State 4, Procedure sp_update_jobschedule, Line 0
Procedure 'sp_update_jobschedule' expects parameter '@name', which was not supplied.

I understand it want the name of the schedule but why, can't it work like your scipt:
EXEC msdb.dbo.sp_update_job @job_id, @enabled = 0
where you only update the info you need updated?

Here is my full proc
SET NOCOUNT ON

CREATE TABLE #schedule
(
job_id UNIQUEIDENTIFIER NOT NULL,
next_run_date int not null
)

INSERT INTO #schedule
SELECT s.job_id, s.next_run_date
FROM msdb..sysjobs j (NoLock)
LEFT OUTER JOIN msdb..sysjobschedules s (NoLock) ON (s.job_id = j.job_id)
where j.category_id = 3 and j.enabled = 1 and s.enabled = 1
ORDER BY s.job_id

DECLARE @job_name SYSNAME
DECLARE @job_id UNIQUEIDENTIFIER
DECLARE @nextDate int

DECLARE Start_date CURSOR FOR
SELECT Job_id, next_run_date
FROM #schedule

SET @job_name = NULL

OPEN Start_date
FETCH NEXT FROM Start_date INTO @job_id, @nextDate

WHILE @@FETCH_STATUS = 0
BEGIN
EXEC msdb.dbo.sp_verify_job_identifiers '@job_name', '@job_id', @job_name OUTPUT, @job_id OUTPUT

EXEC msdb.dbo.sp_update_jobschedule @job_id, @active_start_date = @nextDate

SET @job_name = NULL

FETCH NEXT FROM Start_date INTO @job_id, @nextDate
END

CLOSE Start_date
DEALLOCATE Start_date

DROP TABLE #schedule



Go to Top of Page

SQLServerDBA_Dan
Aged Yak Warrior

USA
752 Posts

Posted - 12/20/2005 :  15:20:56  Show Profile  Reply with Quote
quote:
Originally posted by Rhahn

Thank you both, I've adapted the script to fix my start date problem. but
EXEC msdb.dbo.sp_update_jobschedule @job_id, @active_start_date = @nextDate

is throwing
Server: Msg 201, Level 16, State 4, Procedure sp_update_jobschedule, Line 0
Procedure 'sp_update_jobschedule' expects parameter '@name', which was not supplied.

I understand it want the name of the schedule but why, can't it work like your scipt:
EXEC msdb.dbo.sp_update_job @job_id, @enabled = 0
where you only update the info you need updated?



You can use the code I posted. Read it over before you run it and run it against a test job before you run it in production. I thinkg this is the problem I had when I was attempting to update the job schedule time and the code I posted was the workaround.

You'll need to uncomment the part of the first update that has the active start date. If I'm not mistaken this updates the scheduled time too. So experiment on a test job first.



Daniel, MCP, A+
SQL Server DBA
www.dallasteam.com
Go to Top of Page

Rhahn
Starting Member

Canada
9 Posts

Posted - 12/20/2005 :  15:29:49  Show Profile  Reply with Quote
Thanks,
Just out of curiosity why wouldn’t restarting sql server agent clear its cash… that seems a lot easer than messing around with these scripts.. I’m pretty new to this admin stuff and I’m starting to see that the strait forward way to do something never wroks.
Go to Top of Page

SQLServerDBA_Dan
Aged Yak Warrior

USA
752 Posts

Posted - 12/20/2005 :  16:08:28  Show Profile  Reply with Quote
quote:
Originally posted by Rhahn

Thanks,
Just out of curiosity why wouldn’t restarting sql server agent clear its cash… that seems a lot easer than messing around with these scripts.. I’m pretty new to this admin stuff and I’m starting to see that the strait forward way to do something never wroks.




I don't know. It might work... I wrote that script to update a single job rather than mess with the entire server. Keep in mind other jobs could be running. Maybe you have a job that has to run every minute and you wont be able to shut down the agent? I dunno.

Daniel, MCP, A+
SQL Server DBA
www.dallasteam.com
Go to Top of Page

Rhahn
Starting Member

Canada
9 Posts

Posted - 12/20/2005 :  16:49:41  Show Profile  Reply with Quote
So Daniel,
This is what I came up with. It is a hybrid of the last script to facilitate multiple jobs. I’m now getting a usage error on xp_qlagent_notify. I’m having no luck finding good documentation on this xp on Google.

Server: Msg 22022, Level 16, State 1, Line 0
Usage: EXECUTE xp_sqlagent_notify <operation type>, <job id>, <schedule id>, <alert id>, <action type> [, <login name>] [, <error flag>]

SET NOCOUNT ON

CREATE TABLE #schedule
(
job_id UNIQUEIDENTIFIER NOT NULL,
next_run_date int not null,
sched_ID int not null
)

INSERT INTO #schedule
SELECT s.job_id, s.next_run_date, s.schedule_id
FROM msdb..sysjobs j (NoLock)
LEFT OUTER JOIN msdb..sysjobschedules s (NoLock) ON (s.job_id = j.job_id)
where j.category_id = 3 and j.enabled = 1 and s.enabled = 1
ORDER BY s.job_id

DECLARE @job_name SYSNAME
DECLARE @job_id UNIQUEIDENTIFIER
DECLARE @nextDate int
DECLARE @Schedule_ID int

DECLARE Start_date CURSOR FOR
SELECT Job_id, next_run_date, sched_ID
FROM #schedule

SET @job_name = NULL

OPEN Start_date
FETCH NEXT FROM Start_date INTO @job_id, @nextDate, @schedule_ID

WHILE @@FETCH_STATUS = 0
BEGIN
EXEC msdb.dbo.sp_verify_job_identifiers '@job_name', '@job_id', @job_name OUTPUT, @job_id OUTPUT

--EXEC msdb.dbo.sp_update_jobschedule @job_id, @active_start_date = @nextDate
UPDATE msdb.dbo.sysjobschedules
set active_start_date = @nextDate
where job_id = @job_id
UPDATE msdb.dbo.sysjobs
SET version_number = version_number + 1, date_modified = GETDATE()
WHERE (job_id = @job_id)
IF (EXISTS ( SELECT s.*
FROM msdb.dbo.sysjobservers s (NoLock)
INNER JOIN msdb.dbo.sysjobs j (NoLock) on (j.job_id = s.job_id)
WHERE server_id = 0 and j.job_id = @job_id) )
BEGIN
EXEC master.dbo.xp_sqlagent_notify 'S', @job_id, @schedule_id, null, 'U', 'INSERT NT ACCOUNT HERE', 1, @@trancount
END

SET @job_name = NULL

FETCH NEXT FROM Start_date INTO @job_id, @nextDate, @schedule_id
END

CLOSE Start_date
DEALLOCATE Start_date

DROP TABLE #schedule


Go to Top of Page

Rhahn
Starting Member

Canada
9 Posts

Posted - 12/20/2005 :  17:35:57  Show Profile  Reply with Quote
Came up with this... sp_update_jobschedule @name isn't optinal as it is in update job. but it takes a closer look at the sysntax on msdn to see that...
SET NOCOUNT ON

CREATE TABLE #schedule
(
job_id UNIQUEIDENTIFIER NOT NULL,
next_run_date int not null,
sname sysname not null
)

INSERT INTO #schedule
SELECT s.job_id, s.next_run_date, s.name
FROM msdb..sysjobs j (NoLock)
LEFT OUTER JOIN msdb..sysjobschedules s (NoLock) ON (s.job_id = j.job_id)
where j.category_id = 3 and j.enabled = 1 and s.enabled = 1
ORDER BY s.job_id

DECLARE @job_name SYSNAME
DECLARE @Schedule_name SYSNAME
DECLARE @job_id UNIQUEIDENTIFIER
DECLARE @nextDate int

DECLARE Start_date CURSOR FOR
SELECT Job_id, next_run_date, sname
FROM #schedule

SET @job_name = NULL

OPEN Start_date
FETCH NEXT FROM Start_date INTO @job_id, @nextDate, @Schedule_name

WHILE @@FETCH_STATUS = 0
BEGIN
EXEC msdb.dbo.sp_verify_job_identifiers '@job_name', '@job_id', @job_name OUTPUT, @job_id OUTPUT

EXEC msdb.dbo.sp_update_jobschedule @job_id,@name=@Schedule_name, @active_start_date = @nextDate

SET @job_name = NULL

FETCH NEXT FROM Start_date INTO @job_id, @nextDate, @Schedule_name
END

CLOSE Start_date
DEALLOCATE Start_date

DROP TABLE #schedu
Go to Top of Page

Rhahn
Starting Member

Canada
9 Posts

Posted - 12/21/2005 :  12:16:25  Show Profile  Reply with Quote
So I ran the script with sp_update_jobschedule and I've got the same problem as befor. I Don't understand why SQL is having such a hard time looking at its own DataBase and figuring out to start a job.
Go to Top of Page

SQLServerDBA_Dan
Aged Yak Warrior

USA
752 Posts

Posted - 12/22/2005 :  09:04:37  Show Profile  Reply with Quote
quote:
Originally posted by Rhahn

So I ran the script with sp_update_jobschedule and I've got the same problem as befor. I Don't understand why SQL is having such a hard time looking at its own DataBase and figuring out to start a job.




Is the SQL Agent started? Have you tried my script, yet? You can put it into an SP and call it from there. I'd test it on 1 job to be sure it gives the results that you want before doing a blanket update with a cursor...
Go to Top of Page

Rhahn
Starting Member

Canada
9 Posts

Posted - 12/22/2005 :  14:39:01  Show Profile  Reply with Quote
I never could get the xp to work with the syntax in your script, and I couldn't find any thing on the web regarding it. I know the service was running b/c jobs that i did not tuch with the original enable= 0 still worked on there schedule. I had to restart the agent to get the jobs working on schedule again.
Go to Top of Page

SQLServerDBA_Dan
Aged Yak Warrior

USA
752 Posts

Posted - 12/23/2005 :  08:09:24  Show Profile  Reply with Quote
quote:
Originally posted by Rhahn

I never could get the xp to work with the syntax in your script, and I couldn't find any thing on the web regarding it.



You need to put in your user name in the xp call where it says 'INSERT NT ACCOUNT HERE'. You can get the correct user name by running "select system_user". I'm not sure what permissions your user needs but if you're a SQL Admin then permissions wont be a problem. If that doesnt work then you'll need to post the error.
Go to Top of Page

Rhahn
Starting Member

Canada
9 Posts

Posted - 12/26/2005 :  19:08:15  Show Profile  Reply with Quote
Yeah I figured that out. The problem was your eg. dosn't have all the prams the xp wants or something.... Do you have a web page where that xp is explained?
Go to Top of Page

SQLServerDBA_Dan
Aged Yak Warrior

USA
752 Posts

Posted - 12/27/2005 :  08:33:33  Show Profile  Reply with Quote
What version + sp of SQL Server are you running? That code works fine for me. Keep in mind that this is commented out:

UPDATE msdb..sysjobschedules 
SET Enabled = 1 --, active_start_date = convert(varchar(8), getdate(), 112), active_start_time = left(replace(convert(varchar(8), dateadd(n, 1, getdate()), 8), ':', ''), 4) + '00', next_run_date = convert(varchar(8), getdate(), 112), next_run_time = left(replace(convert(varchar(8), dateadd(n, 1, getdate()), 8), ':', ''), 4) + '00'
FROM msdb..sysjobs j
	LEFT OUTER JOIN msdb..sysjobschedules s ON (s.job_id = j.job_id)
WHERE j.Name = @JOBNAME


Because it updates your jobschedule to the very next minute.
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.19 seconds. Powered By: Snitz Forums 2000