SQL Agent jobs do not run as scheduled

On Friday the Application Team started complaining that their jobs have not run as scheduled on a SQL Server 2000 instance. But the DBA team did not receive any alerts either regarding the job failure or regarding the SQL Server instance/SQL Server Agent being down.

The DBA started working on that ticket. As usual he suspected that the SQL Server Agent might have gone down. But it was not the case, Agent was up and running fine. Did any of the jobs fail? No, all the jobs had completed successfully. Did someone modify the job? No, the jobs were untouched for a long time. Was those daily jobs’ schedule changed? No, it was scheduled to run every day.

The job details screen was interesting. Even though the jobs were scheduled to run everyday except Sunday, the last run date was showing as 4th and the next run was 7th.

How did the jobs did not run as scheduled on 5th and 6th? The best place to look for the answer was the SQL Server Agent Error Log. It was full of error messages like these.

SQLServer Error: 18456, Login failed for user 'sa'. [SQLSTATE 28000]
Logon to server '(local)' failed (JobManager)
SQLServer Error: 18456, Login failed for user 'sa'. [SQLSTATE 28000]
Logon to server '(local)' failed (ConnAttemptCachableOp)
SQLServer Error: 18456, Login failed for user 'sa'. [SQLSTATE 28000]
Logon to server '(local)' failed (ConnAttemptCachableOp)
SQLServer Error: 18456, Login failed for user 'sa'. [SQLSTATE 28000]
Logon to server '(local)' failed (SaveAllSchedules)

For every action that the SQL Server Agent tried to do, it was getting an error Login failed for user ‘sa’. This error message seemed very similar. I had come across a situation where the SQL Server Agent was not starting because of same issue and I had talked about it here. Right away checked the Connection properties of SQL Server Agent.

Now the Error Messages in SQL Server Agent Log made sense. The password of sa login was changed on the 4th of this month and none of the jobs have run ever since. Since the Agent was configured to connect to the SQL Server Instance using the credentials of sa account, all the functionalities of SQL Server Agent were failing with error.

Why SQL Server Agent was configured this way? All I can say is that it wasn’t me ;-) This was done when the server was *built*. The DBA on duty did a great job in processing a Change Control real quick and changed the connection properties to Use Windows Authentication and restarted the SQL Server Agent service. The jobs ran successfully as scheduled and everyone ended the week on a happy note!

7 thoughts on “SQL Agent jobs do not run as scheduled

  1. Pingback: SQL Agent jobs do not run as scheduled | SQL Server DBA Diaries of … - sql

  2. Uday

    Pradeep,

    How the SQL server agent Jobs shows of next run on 7, as 5 & 6 were skipped ,So Iam intersting know that how it Updated the next run as 7 ……

    Regards,
    uday

    Reply
    1. PradeepAdiga Post author

      Uday, the date is not updated to 7th instead it is showing the current date. If you check it the next day it will show as 8th. There is no way for SQL Server to update the system tables since it cannot connect to the SQL Server instance.

      Reply
  3. Pingback: SQL Server Agent does not start | '(Unknown)' is not a member of the SysAdmin role | SQL Server DBA Diaries of Pradeep Adiga

  4. Lor Escobal

    Pradeep,

    Do you know any scripts that will allow me to see the last modifed date of a SQL server Jobs?

    Regards,
    Lor

    Reply
    1. PradeepAdiga Post author

      Hi Lor,
      You may query the sysjobs table in msdb database to get that information. Here is an example.
      select name,date_modified from msdb..sysjobs

      Reply

Leave a Reply