December 7, 2009 at 5:17 am
Hi,
bit of an odd problem. Our SQL server (64-bit Standard Edition, SP3) appears to attempt to run some SQL jobs, based on their schedule, BUT the job is disabled??
Has anyone had this problem or know how to resolve it ?
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
December 7, 2009 at 7:05 am
Is the job attempting to execute based on the schedule or at a different time other than the schedule.
Since a disabled Job can also be started manually, my guess is some tried to start is manually.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 7, 2009 at 7:11 am
Check for any other sort of schedulers like web services etc.
December 8, 2009 at 3:16 am
Yeah the jobs are running based on their schedule and that I am aware of there is now web schedules.
I do have SSRS configured, but this are only SQL Server jobs.
The enabling / disabling of jobs occurs once a week, when we fail over a mirrored principle database to the mirror
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
December 9, 2009 at 5:16 am
The T-SQL I'm running as part of a scheduled job to enable / disable the job is as follows:
USE msdb
UPDATE sysjobs
SETEnabled = 0
WHERE[Name] LIKE 'DB Backup%';
This appears to work fine and some weeks I don't have the problem I've mentioned...... am at a bit of a loss as it what it's doing.
Do I need to clear some sort of cached SQL Agent job list??
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
December 9, 2009 at 6:52 am
What is that you are trying to achieve, you started this thread saying the disabled jobs are running who is it possible?
You say you are running T-SQL script that runs weekly to enable and disable jobs, so if you are doing it knowingly, what is that you are trying?? It baffles me.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 9, 2009 at 7:59 am
We are implemented a mirroring solution that fails over automatically week on week as a sort of automated DR test (so we know, that should we fail over when things go bad, then we won't get any nasty.... ' it's not working as it should...' issues).
The plan is as follows:
Week 1
Server A is the Principle Server
Server B is the mirror
All services, backups and messaging are done with the Principle.
Backups are only run on the principle for mirrored databases (for obvious reasons)
Week 2
Server B is the Principle Server
Server A is the mirror
During switchover at the start of week 2. Server A fails over the databases. It also disables the backup jobs.
On Server B the backup jobs are enabled.
The problem is, given the scenario, is that during week 2, Server A backup jobs are disabled, however SQL Server Agent seems to think that the job should be run and I am therefore getting the subsequent job failed errors as it can't backup a database that is in the mirroring state.
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
December 10, 2009 at 7:53 am
How about you script out the jobs and create them on the server that is the primary and delete them from the secondary. When you do your failover then delete from the new secondary and create them on the new primary. I guarantee you that a job that doesn't exist cannot run! 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 21, 2009 at 4:09 am
Well scripting out and re-creating seems a little over-kill to me.
However, it has lead me onto somthing else. looking at MSDN, SQL agent re-caches a job following a modification, when sp_add_jobserver is called.
So it's looks like my issue to related to the job being cached on server. Now the question is, is there simple a way I can re-cache the jobs once a week?
I'm thinking of simply restarting SQL Agent once a week.
- however is there a stored procedure for enabling / disabling a job that would implicitly re-cache the jobs?
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
December 21, 2009 at 5:53 am
sp_update_job, although I have no idea about this 'job caching' business.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 4, 2010 at 3:16 am
Hi thanks.
I gone down the route of using the stored procedure and it would seem to now work as I expected it to.
As I need to disable a few jobs, I've had to build this into a cursor, but it seems to work well now.
🙂
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
February 8, 2010 at 3:36 am
Not absolutely sure if this is the same issue, but from the initial post, we have had the same issue (on a standalone SQL2000 RTM install). A job exists with a schedule. The job is disabled but the schedule is not. The job then continues to run, on the schedule, until the schedule itself is disabled. I saw in an MS Connect post that restarting SQL Agent would also fix the problem; maybe this is related to the 'cache' mentioned? I've never heard of a cache here but I've only worked with SQL since 6.5 (13 years).:-D
February 8, 2010 at 6:05 am
well - I'm not a 100% on the cache. It would just seem that the issue we've had is related to how we update the job.
From experience on other systems, for me, it points to the job details being stored in some sort of memory, if we are restarting the schduler and it then works (which is does).
- I'm not an expert - it's just my 2 pence worth and my thoughts... 🙂
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
February 8, 2010 at 9:44 am
Found an update. SQL Server, in the sp_update_job, runs "exec msdb.dbo.sp_sqlagent_notify @op_type = N'J', @job_id = @job_id, @action_type = N'U'" which resets the cache for this Job_ID. So there is a cache. We've had issues setting the flag manually in SSMS, so it look like that might directly update the sysjobs table. Only sp_update_job seems to do it properly.
Always something new to learn!
February 8, 2010 at 9:58 am
ain't that the truth! 😀
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
Viewing 15 posts - 1 through 15 (of 32 total)
You must be logged in to reply to this topic. Login to reply