SQL server agent node missing in SSMS
Hi experts.
SQL Server 2012 used to be backed up using tasks that were configured by SQL management studio. Lately, the underlying OS windows 8.1 was upgraded to Win10 v1803 and although the rest works normally, no SQL backups run anymore. Investigating, I found that in SQL management studio, there no longer is a node "SQL server agent" and that seems to be why we can't view or re-configure backups. The SQL server agent service is still present and can be started in services.msc, but immediately stops, saying: ""SQLSERVERAGENT service on Local Computer started and then stopped. Some services stop automatically if they have no work to do".
I had SQL management studio 2012 installed, I installed ssms 2017 (latest 17.8) as well, but no change.
Where to go from here?
SQL Server 2012 used to be backed up using tasks that were configured by SQL management studio. Lately, the underlying OS windows 8.1 was upgraded to Win10 v1803 and although the rest works normally, no SQL backups run anymore. Investigating, I found that in SQL management studio, there no longer is a node "SQL server agent" and that seems to be why we can't view or re-configure backups. The SQL server agent service is still present and can be started in services.msc, but immediately stops, saying: ""SQLSERVERAGENT service on Local Computer started and then stopped. Some services stop automatically if they have no work to do".
I had SQL management studio 2012 installed, I installed ssms 2017 (latest 17.8) as well, but no change.
Where to go from here?
ASKER
Hi Jim.
All possible DB privileges granted, don't worry. SSMS started elevated as well.
All possible DB privileges granted, don't worry. SSMS started elevated as well.
Ok. Sorry, that's all I have, so I'll withdraw to encourage other experts to respond.
It's almost as if the Windows upgrade was also coupled with a SQL Server edition change. Was the SQL Server installation touched in any way?
ASKER
Hi. No, not changed in any way.
One more thing - please do not use the services.msc to administrate SQL Server services.
The SQL Server Configuration Manager also takes care of setting and maintaining appropriate registry permissions that are needed for SQL Server to run properly.
I would recommend upgrading to the latest Service Pack/CU to ensure permissions are set properly and then use SQL Server Configuration Manager to administrate the services.
The SQL Server Configuration Manager also takes care of setting and maintaining appropriate registry permissions that are needed for SQL Server to run properly.
I would recommend upgrading to the latest Service Pack/CU to ensure permissions are set properly and then use SQL Server Configuration Manager to administrate the services.
ASKER
Hi Nakul.
Look, we have some progress:
"C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER
--
2018-08-24 14:11:53 - ? [100] Microsoft SQLServerAgent, Version 11.0.7462.6 (Build X64 unicode Retail): Prozess-ID 8476
2018-08-24 14:11:53 - ? [495] Das Dienstkonto für den Start des SQL Server-Agents ist NT Service\SQLSERVERAGENT.
2018-08-24 14:11:53 - ? [393] SQL Server stellt die Datenbank 'msdb' wieder her. Bitte warten Sie...
2018-08-24 14:11:53 - ? [000]
2018-08-24 14:11:53 - ? [101] SQL Server AAZUSI, Version 11.00.7462 (Anzahl von möglichen Verbindungen: 0)
2018-08-24 14:11:53 - ? [102] SQL Server-ODBC-Treiber, Version 11.00.7462
2018-08-24 14:11:53 - ? [103] Der Treiber verwendet folgende NetLib: DBNETLIB. Der lokale Hostserver ist ''''.
2018-08-24 14:11:53 - ? [310] 2 Prozessor(en) und 6144 MB RAM erkannt.
2018-08-24 14:11:53 - ? [339] Der lokale Computer ''AAZUSI'' wird unter ''Windows NT 6.2 (9200) '' ausgeführt.
2018-08-24 14:11:53 - ! [000] Diese Installation des SQL Server-Agents ist deaktiviert. Die Edition von SQL Server, die diesen Dienst installiert hat, unterstützt den SQL Server-Agent nicht.
2018-08-24 14:11:54 - ? [000] Die Konfigurationsoption 'Agent XPs' wurde von 1 in 0 geändert. Führen Sie zum Installieren die RECONFIGURE-Anweisung aus. [SQLSTATE 01000] (Meldung 15457)
2018-08-24 14:11:54 - ? [098] ''SQLServerAgent'' wurde beendet (normal).
--
In case you don't have access to browser translation features, I will translate the important line: "This installation of sql server agent is deactivated. The edition of SQL Server that has installed the service does not support SQL Server Agent"
...which is utter non-sense, because it did support SQL Agent before upgrading to Win10. But anyway, since we only use SQL server Agent for backups and we can easily solve these using powershell, we will no longer try to repair sql server agent. Maybe it's as easy as reinstalling SQL server, but there is no need. I was just interested in the reason.
Look, we have some progress:
"C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER
--
2018-08-24 14:11:53 - ? [100] Microsoft SQLServerAgent, Version 11.0.7462.6 (Build X64 unicode Retail): Prozess-ID 8476
2018-08-24 14:11:53 - ? [495] Das Dienstkonto für den Start des SQL Server-Agents ist NT Service\SQLSERVERAGENT.
2018-08-24 14:11:53 - ? [393] SQL Server stellt die Datenbank 'msdb' wieder her. Bitte warten Sie...
2018-08-24 14:11:53 - ? [000]
2018-08-24 14:11:53 - ? [101] SQL Server AAZUSI, Version 11.00.7462 (Anzahl von möglichen Verbindungen: 0)
2018-08-24 14:11:53 - ? [102] SQL Server-ODBC-Treiber, Version 11.00.7462
2018-08-24 14:11:53 - ? [103] Der Treiber verwendet folgende NetLib: DBNETLIB. Der lokale Hostserver ist ''''.
2018-08-24 14:11:53 - ? [310] 2 Prozessor(en) und 6144 MB RAM erkannt.
2018-08-24 14:11:53 - ? [339] Der lokale Computer ''AAZUSI'' wird unter ''Windows NT 6.2 (9200) '' ausgeführt.
2018-08-24 14:11:53 - ! [000] Diese Installation des SQL Server-Agents ist deaktiviert. Die Edition von SQL Server, die diesen Dienst installiert hat, unterstützt den SQL Server-Agent nicht.
2018-08-24 14:11:54 - ? [000] Die Konfigurationsoption 'Agent XPs' wurde von 1 in 0 geändert. Führen Sie zum Installieren die RECONFIGURE-Anweisung aus. [SQLSTATE 01000] (Meldung 15457)
2018-08-24 14:11:54 - ? [098] ''SQLServerAgent'' wurde beendet (normal).
--
In case you don't have access to browser translation features, I will translate the important line: "This installation of sql server agent is deactivated. The edition of SQL Server that has installed the service does not support SQL Server Agent"
...which is utter non-sense, because it did support SQL Agent before upgrading to Win10. But anyway, since we only use SQL server Agent for backups and we can easily solve these using powershell, we will no longer try to repair sql server agent. Maybe it's as easy as reinstalling SQL server, but there is no need. I was just interested in the reason.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Thanks for the update. Based on the log, there is a very high probability that something went wrong with registry permissions during the Windows upgrade because of which SQL Server can't recognize the edition and the Agent correctly anymore.
Anyway, thanks again for the update and have a great week ahead!
Anyway, thanks again for the update and have a great week ahead!
<Knee-jerk response> Maybe you no longer have privs to create/execute SQL Agent jobs, hence it does not appear in SSMS, and need a DBA to investigate this.