SQL Server Scheduled Job - says its running with no errors but doing nothing

Question

I have a SQL Server scheduled job which has been running for about 6 months every night.

However, it has stopped doing what it should (transfering data from one database to the next). There are no errors and when I run it manually it is fine.

I'm struggling to see why it would run manually and not on a schedule.

Any ideas?

Answer 1

Recently got server restarted? It may be SQL Server Agent service running in Manual Startup Mode. Have you tried to restart?

Answer 2

Is the job running and not doing anything or is it failing to run during the scheduled time? If the SQL Agent job history isn't logging any errors, check the SQL Server error log for suspicious entries. Additionally, in the case of the latter, verify not only that the job is enabled, but also the specific schedule.

Answer 3

What everyone else (three preceding posts) said. If you still can't figure things out, fire up SQL Profiler and have it running while the job is (supposed to be) running to see what, if any, queries or logins are received by the database server.

Answer 4

Check the log for errors. It might be the user under which the job runs does not have rights to one or both of the database (old user that got his right removed after he quittes your company??)

Try to put yourself as the job owner since it is running when you run it manually

Answer 5

I'm assuming this was a job owner/permissions issue, as I just had the same problem. Is there any reason why this won't give you a warning or error when this happens? Am I missing something? Is there a setting that you can change to allow sql server to report errors for things like this?