SQL Server Performance Forum – Threads Archive
can’ run new created jobs on sql
Just got reported by developer that new created job stuck on sql server. Also I built a very simple job to run ‘select * from sysdatabases’ every one minute which didn’t run as well. I know it should be ok after restarting sql server agent service, but can anyone tell me the reason in more details? thanks
What version of SQl server with which service pack u r using.
Chekc the SQL Server error log and SQLagent log for more information. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
SQL 2k enterprise version.
quote:Originally posted by ranjitjain
What version of SQl server with which service pack u r using.
Have checked, but no clue.
quote:Originally posted by satya
Chekc the SQL Server error log and SQLagent log for more information.
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
after I checked msdb by executing the following statement:
select * from sysjobs j, sysjobschedules s
where j.job_id=s.job_id I found that the job that was enabled but didn’t run has active_start_time=0
next_run_date=0
next_run_time=0 So it means all jobs don’t run have been added into system table but the schedule doesn’t work.
A job can be created without a schedule. In fact, when you first create a job, it has no schedule until you add one. For a job to run automatically, it needs to be set to enabled, runnable and scheduled. You’re missing the schedule for sure, but may want to check the enabled and runnable settings too. Chris
hi, chris: Thanks for your info. But definitely the schedule has been set and enabled in the job setting interface. as I mentioned, the test job will run every minute to execute " select * from sysobjects". also in the job list, this job is enabled and runnable. but it can’t identify the next run date so I am sure sth wrong with agent service inside with schdule work. I got this issue before and it’s been solved by restarting sql agent, this time I just want to dig inside a little bit more.
I havent seen this happen before and we have almost 200 jobs in our main production DB and 132 on our reporting server, and at least a 100 others on our other servers. You said you’re using sql2k enterprise, what build? Maybe you’re missing a hotfix somewhere.
SP3 was applied more than one year ago.
What are the results when you run sp_help_jobschedule? I haven’t ever seen it not work either. Also, don’t run "select * from sysobjects" every minute. That’s crazy. Use sp_who or something. At least put WITH(NOLOCK) on that. You’re going to cause all kinds of issues doing that. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
And only one other suggestion…..sounds obvious, but sometimes isnt. Was there an expiry date on the schedule?
Actually guys, I thought all the relevant settings were right. And I know the solution is just to restart sql server agent. I did it yesterday and it did work after that, which can also prove all the settings were correct. So what I wondering is what caused this problem. Because this was the third time I encounter same problem with agent service. So I just wanted to dig inside a little bit more. Thanks all for your efforts.
You must check the SQLAgent log to see if any other process is overlapping and stopping this service. And ensure to maintain required privileges to the SQLAgent account if it involves any network file operations. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
]]>