SQL Server Performance Forum – Threads Archive

Jobs not running at all

Whenever I try to run the job through Database Maintenance Plan and it does not run at all. And If I try to run it manually then it actually does nothing.
In the Next Run Date Column it says (Date and Time are not available) for any new
plans I create. When I right click and say start nothing happens. Its as if new
plans are not being recognized in the system but they are enabled and show up in
jobs. I have checked the SQL Agent Services and these are running with the SysAdmin rights. Regards,
Manoj Verma
DBA

SQL Version? SP?.
Any messages in even viewer? Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell

I am having SQL 2000 with SP3a on Windows 2000 Standard Server with SP4.
And there is no entry in event viewer even except one that is "SuperSocket info: ConnectionListen(Shared-Memory (LPC)) : Error 5. ". The source of this error is MSSQLSERVER but I dont know whether it is related with this error or not. Regards,
Manoj Verma
DBA

When you start SQL Server Agent is there any error in log?
Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell

No there is no error at all. I restarted the SQL Services & Agent Services many times but it never gave any error. Regards,
Manoj Verma
DBA

Mannu: When you said error log, do you mean SQL Server Agent, right click, Display error log…? Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell

When I say error log then it means the same as u said as well as the Error Log files in the default SQL Instal location and Windows Event Viewer too. Regards,
Manoj Verma
DBA

Error 5 typically means access denied to a resource.
But, you check this and is OK. I suggest to wait for others oppinions. Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell

well I also hope that some one else could help me out in this particular issue… Regards,
Manoj Verma
DBA

As Luis refers it says access denied so what permissions does the SQL Server Agent service account have in windows? It must be a SYSADMIN to continue the process. (observed from initial post it is under SYSADMIN). BTW what is the level of service pack on SQL & OS? _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

Hi Satya,
Well SQL Server Agent service account is a part of local admin and is also a part of sa role in SQL Server.
And I m having SQL 2000 with SP3a on Windows 2000 Standard Server with SP4. Regards,
Manoj Verma
DBA

Apart from maint.plan can you just schedule a simple backup job or run manually.
BTW how about database sizes, disk space available etc. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

Hi,
I just tried to make a job which takes the full backup but its also not working. Same problem is there with it also. And one thing as I mentioned earlier too is that in the column "Next Run Date" its showing "(Date and Time are not available)". I have tried this on a newly created database too but its still not working.
No job is working at all. Regards,
Manoj Verma
DBA

Try to restart SQLAgent alone, it may resolve the issue. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

Just tried that too but didnt work. Regards,
Manoj Verma
DBA

Its weird, can you post details from SQLAgent error log. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

Thats the main issue that there is nothing in SQLAgent error log too. This is the information I am getting in SQLAgent log:
[101] SQL Server MANNU version 8.00.760 (0 connection limit). And this warning I am getting over there:
[396] An idle CPU condition has not been defined – OnIdle job schedules will have no effect. Regards,
Manoj Verma
DBA

Check: http://msdn.microsoft.com/library/en-us/adminsql/ad_automate_1o4z.asp Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell

Any recent changes to SQL server? _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

Check your sa password or any other sysadmin users that is being used by the job. This already happened to me once when I changed my admin password. Jon M

No changes to SQL Server have been done since a month.
And I have gone through that article also which Luis have specified but there is nothing related with scheduled date/time too.
But one more thing I have just come across is that I scheduled the Transaction Log Backup with Database Backup Maintenance Plan and its working on its scheduled time only but if I try to run it manually then the same problem that it doesnt run at all. Regards,
Manoj Verma
DBA

I believe the restart of SQLAgent might resolved in running jobs when scheduled.
How about your account privileges on the SQL server? _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

The warning : [396] An idle CPU condition has not been defined – OnIdle job schedules will have no effect
is why I went to Microsoft to find it. I don’t remember, but I happens to me. I will try to find something.
Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell

Are you running SQL server in "lightweight pooling" mode (fiber mode)? _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

I was looking others forums.
There is a guy, same problem, same warning.
Solution was reinstall Service Pack.
Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell

Even I can see that messages on my services with no affect to the jobs at any time.
I can confirm even we re-apply the SP the message re-appears when you restart SQLAgent, and I think its an informational message. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

Well I also went through many articles on the net and found that the problem could be that SQL server is running in "lightweight pooling" mode (fiber mode) but I checked that too and that is not on that mode rather its on Thread mode only and I ahve started SQLAgent many times with and without the SQL Server. I will try to reinstall the Service Pack and come back with the result.
Thanks for all of you guys help. Regards,
Manoj Verma
DBA

One more interesting thing is that when I create Transaction Log Backup through Maintenance Plan then its working but at its scheduled time only, when I try to run it manually then also its not responding at all. Regards,
Manoj Verma
DBA

Yipppeeeeeeeeeeee………………..
Reinstallation of SP3a has done the trick. But dont know how… Regards,
Manoj Verma
DBA

All credit goes to Luis for pointing out in right-direction and where this didn’t worked for me earlier. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

Yes for sure.
Thank you Luis.
And Satya by the way I would like to know something about "lightweight pooling" mode (fiber mode).
Can you please give me a link or so where I can find some info about this???
Regards,
Manoj Verma
DBA

The server configuration lightweight pooling option controls whether SQL Server uses threads or fibers. In fiber mode, SQL Server allocates one thread per CPU, then allocates a fiber per concurrent user command, up to the max worker threads value. SQL Server uses the same algorithms to schedule and synchronize tasks when using either threads or fibers. This is how its related to SQL task scheduling – it has an internal layer that implements an environment similar to an operating-system for scheduling and synchronizing concurrent tasks without having to call the Windows kernel. This internal layer can schedule fibers as effectively as it works with threads. SQL Server maintains a pool of either threads or fibers for user connections Books online refers :
….lightweight pooling option to provide a means of reducing the system overhead associated with the excessive context switching sometimes seen in symmetric multiprocessor (SMP) environments. When excessive context switching is present, lightweight pooling may provide better throughput by performing the context switching inline, thus helping to reduce user/kernel ring transitions. http://www.sql-server-performance.com/q&a8.asp – Q&A link. HTH _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

]]>