November 1, 2006 at 9:21 am
Thanks for all the explaination and feedback. It got me mad because he brought it up during an all project meeting and argued it with in front of everyone ... wasted my time and everyone elses.
-----------------------------
www.cbtr.net
.: SQL Backup Admin Tool[/url] :.
November 1, 2006 at 11:55 am
You should email the answer to everyone in the meeting,. I'm sure there are many people wanting to know the final answer ....... plus then they'll know that he is a blathering fool, and he'll think twice before trying to make you look like a fool in front of others.
November 1, 2006 at 2:19 pm
Disabling a job in SQL Server only means that the SQLServer Agent will not run it automatically. But we can fire the job from many other ways, such as manually and using a stored procedure.
November 1, 2006 at 2:20 pm
Disabling a job in SQL Server only means that the SQLServer Agent will not run it automatically. But we can fire the job from many other ways, such as manually and using a stored procedure.
December 10, 2015 at 2:20 pm
I have a client who is running SQL Server 2005 and we are trying to disable/enable SQL jobs using a SQL stored procedure.
I have two play jobs that just send me an email every five minutes.
IF @Severity=0
BEGIN
update msdb.dbo.sysjobs
set enabled=@EnableIRMSjobs
where name='JDN_BRD_Play'
SET @Severity=@@ERROR
END
This will enable or disable the job based on the value of @EnableIRMSjobs variable.
However, I keep receiving emails every five minutes. 🙁
So, I disabled the job schedule:
update SS
set enabled=@EnableIRMSjobs
from msdb.dbo.sysjobs J
inner join msdb.dbo.sysjobschedules S
on J.job_id = S.job_id
inner join msdb.dbo.sysschedules SS
on SS.schedule_id = S.schedule_id
where j.name='JDN_BRD_Play'
This disables the job schedule. Both the job and the schedule are disabled as determined by looking at sysjobs.enabled and sysschedules.enabled and by looking within the GUI interface,
yet I keep receiving emails every five minutes, even though both the job and schedule have been disabled for over an hour. And the View Job History shows the job keeps running every five minutes successfully.
Any ideas?
Before I experienced this I would have have agreed that the coworker who complained about the recurring jobs was crazy, but now...maybe I am just crazy too.
Bruce
December 10, 2015 at 2:27 pm
Found possible answer in:
http://www.sqlservercentral.com/Forums/Topic650924-146-2.aspx
Lynn Pettis (4/20/2010)
The reason I was asking is i had a similar problem when I would failover our mirror databases. The code that was enabling/disabling the database specific jobs wasn't "working". Where the jobs were being disabled, they kept working and where the were being enabled they would run.I had to modify my code to use the sp_update_job stored procedure in the msdb database. Turns out that SQL Server Agent caches the information and this stored procedure forces a notification to SQL Server Agent.
I will check if using the sp_update_job stored procedure solves the problem.
December 10, 2015 at 3:36 pm
Yes, sp_update_job solved the problem because it flushes the SQL Server Agent cache.
exec @Severity=[msdb].[dbo].[sp_update_job] @job_name='JDN_BRD_Play 2', @enabled=@EnableIRMSjobs
It also solved the problem that was the initial symptom of the problem in that when jobs were re-enabled, it did not reset the Schedule with the next time it should run during the day, but waited until the next start of the schedule period to run. But the flush of the cache also reset the schedule correctly.
Thanks Lynn for all your help (both on this issue and all the other responses on other questions).
Bruce
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply