SQL Server Performance

Jobs not running at all

Discussion in 'General DBA Questions' started by mannu_leo, Dec 9, 2003.

  1. mannu_leo New Member

    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
  2. Luis Martin Moderator

    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
  3. mannu_leo New Member

    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
  4. Luis Martin Moderator

    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
  5. mannu_leo New Member

    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
  6. Luis Martin Moderator

    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
  7. mannu_leo New Member

    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
  8. Luis Martin Moderator

    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
  9. mannu_leo New Member

    well I also hope that some one else could help me out in this particular issue...

    Regards,
    Manoj Verma
    DBA
  10. satya Moderator

    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
  11. mannu_leo New Member

    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
  12. satya Moderator

    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
  13. mannu_leo New Member

    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
  14. satya Moderator

    Try to restart SQLAgent alone, it may resolve the issue.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  15. mannu_leo New Member

    Just tried that too but didnt work.

    Regards,
    Manoj Verma
    DBA
  16. satya Moderator

    Its weird, can you post details from SQLAgent error log.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  17. mannu_leo New Member

    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
  18. Luis Martin Moderator

  19. satya Moderator

    Any recent changes to SQL server?

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  20. Jon M Member

    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
  21. mannu_leo New Member

    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
  22. satya Moderator

    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
  23. Luis Martin Moderator

    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
  24. satya Moderator

    Are you running SQL server in "lightweight pooling" mode (fiber mode)?

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  25. Luis Martin Moderator

    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
  26. satya Moderator

    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
  27. mannu_leo New Member

    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
  28. mannu_leo New Member

    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
  29. mannu_leo New Member

    Yipppeeeeeeeeeeee....................
    Reinstallation of SP3a has done the trick. But dont know how...

    Regards,
    Manoj Verma
    DBA
  30. satya Moderator

    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
  31. mannu_leo New Member

    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
  32. satya Moderator

    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

Share This Page