7

I have been experiencing a strange issue for last few weeks. On my two different servers the sql server Agent stops working automatically, All I have to do is to restart the agent and is back on running. But because it stops working it misses the backup jobs I and few other jobs I have scheduled on them Sql Agents.

It doesn’t happen with any of the other servers but it only happens on these two particular servers.

All the servers are SQL SERVER 2008 R2 , Data Centre Edition. 64-bit.

Has anyone ever experienced the same issue if yes what did you do to resolve it ?

M.Ali
  • 67,945
  • 13
  • 101
  • 127
  • I'm having the same issue - however whenever I start any of the other SQL services, the SQL Server Agent stops running. – Ryan Dec 18 '13 at 14:28
  • 1
    do you have any error message, warning in the windows log? Do you have any services restarting during night? Is it happening always at the same time, after or before the same event? – Mathese F Dec 18 '13 at 14:30
  • Check the SQL Server log, too, as well as the history of SQL Agent jobs to see what might have been running when they last stopped. – Paul Williams Dec 18 '13 at 14:34
  • Thank you guys for your quick replies. I dont get any error messages or anything else. When I check the Job history all it says it `Sql Agent not running` and thats all. I have been looking online for solutions and I am not alone facing this problem lots of people are having problems with Sql Agent. @PaulWilliams I have seen Log history nothing was running at that time when it stopped working and it has stopped at random times in the past. funny thing is it only happens with two particular servers never hapened with any of other servers. – M.Ali Dec 18 '13 at 16:37

4 Answers4

10
Start > search for "Services", Component Services, locar Services - "SQL Server Agent" in the list, and change Manual to automatic.

It will start the service the service automatically.

CinCout
  • 9,486
  • 12
  • 49
  • 67
D Kavitha Vijay Datchnamoorthy
  • 437
  • 4
  • 4
6

Just to add .02 cents, this was happening to me as well. I figured out that my SQL Server Agent was stopping when my server was automatically restarted for Windows updates. The associated service for the agent was set to restart manually. I went straight to the Windows Services panel (Start > search for "Services"), found "SQL Server Agent" in the list, and set it to start automatically.

hairbo
  • 3,113
  • 2
  • 27
  • 34
5

"You can configure SQL Server Agent to restart the SQL Server and SQL Server Agent services automatically if they stop unexpectedly"

  1. Open the SQL Server Management Studio Management folder, right-click the SQL Server Agent entry, and select Properties.

  2. On the General page, select the Auto Restart SQL Server If It Stops Unexpectedly check box.

  3. Here, you should also select the Auto Restart SQL Server Agent If It Stops Unexpectedly check box.

  4. Click OK.

Read more about Configure SQL Server Agent to Restart Services Automatically

Also check Access control lists for the SQL Agent group in case its related.

Configure Windows Service Accounts and Permissions

huMpty duMpty
  • 14,346
  • 14
  • 60
  • 99
  • thank you for your suggestion, I think it will do the job but is there any permanent fix for this problem ?? I mean if it stops working in the middle of a JOB, this solution will restart it but wont reschedule the job itself. I want to fix this issue once for all :) – M.Ali Dec 18 '13 at 16:40
  • I am not sure about that. But you will need to find what causing it to stop. Probably the windows error log might give you a clue! – huMpty duMpty Dec 19 '13 at 08:55
  • Where is the "SQL Server Management Studio Management folder" located? – Baddack Feb 10 '16 at 22:13
0
  1. Open The computer Management app
  2. Select services & Applications
  3. Select SQL Server Configuration Manager
  4. Select SQL Server Service (double click)
  5. Select First option SQL Server (MSSQLSERVER)
  6. Right click and go to properties then authenticate
  7. Select this account option and use your sign in credentials to authenticate service Once done service will not drop all good.
user19046875
  • 1