This started out as my trying to determine why I wasn't getting emails from a SQL Server 2012 Standard Edition default instance. Agent jobs have been running fine, but the last time anything tried to send email on the server and succeeded was on 2/2/2016 and the next attempted email on 3/8/2016 (that and all subsequent emails have a NULL sent_account_id and a sent_status of 0).
I decided to delete the current email configuration and reconfigure it from scratch on the default instance, which requires restarting the Agent service so that it will be able to send Notifications via email. Restarting the Agent service was unsuccessful. SQLAGENT.OUT shows:
2016-03-15 17:16:04 - ? [100] Microsoft SQLServerAgent version 11.0.6020.0 (X64 unicode retail build) : Process ID 5608
2016-03-15 17:16:04 - ? [495] The SQL Server Agent startup service account is OurDomain\DomainServiceAccount.
2016-03-15 17:16:04 - ? [393] Waiting for SQL Server to recover database 'msdb'...
2016-03-15 17:16:04 - ? [000] Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install. [SQLSTATE 01000] (Message 15457) Configuration option 'Agent XPs' changed from 0 to 1. Run the RECONFIGURE statement to install. [SQLSTATE 01000] (Message 15457) Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install. [SQLSTATE 01000] (Message 15457)
2016-03-15 17:16:04 - ? [101] SQL Server S-SW-DEVSQLAX version 11.00.6020 (0 connection limit)
2016-03-15 17:16:04 - ? [102] SQL Server ODBC driver version 11.00.6020
2016-03-15 17:16:04 - ? [103] NetLib being used by driver is DBNETLIB; Local host server is
2016-03-15 17:16:04 - ? [310] 4 processor(s) and 16384 MB RAM detected
2016-03-15 17:16:04 - ? [339] Local computer is S-SW-DEVSQLAX running Windows NT 6.1 (7601) Service Pack 1
2016-03-15 17:16:04 - ! [000] This installation of SQL Server Agent is disabled. The edition of SQL Server that installed this service does not support SQL Server Agent.
2016-03-15 17:16:04 - ? [000] Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install. [SQLSTATE 01000] (Message 15457) Configuration option 'Agent XPs' changed from 1 to 0. Run the RECONFIGURE statement to install. [SQLSTATE 01000] (Message 15457) Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install. [SQLSTATE 01000] (Message 15457)
2016-03-15 17:16:04 - ? [098] SQLServerAgent terminated (normally)
The third line from the bottom is the most interesting: "The edition of SQL Server that installed this service does not support SQL Server Agent." To my knowledge, there has never been an Express Edition instance on this server. There are two Standard Edition instances on this server, both have the same email issue, and Executing "SELECT @@VERSION" in either instance returns:
Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64) Oct 20 2015 15:36:27 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) (Hypervisor)
The service was newly started after SP3 was installed on 2/8/2016, so something must have changed after that to cause Agent some issues. I found another server (a test instance for the same project) and found the same issue, which started some time between 7/27/2015 and 9/5/2015 and no SQL Server updates were applied during that range (or since).
While fixing the email issue is what sent me down this road, getting Agent running again is the higher priority (and might fix the email issue).
What could cause Agent to be confused into thinking that this is an Express Edition instance when it is clearly Standard Edition, and the Agent service had been running jobs minutes before the service restart?
I have not restarted the Agent service on the second instance on this server, which has been successfully running jobs all this time (as had the service on the default instance up until the attempted service restart).
[Addition] An attempt to change the Agent service to be run by the Local Service account resulted in "The system cannot find the file specified. [0x80070002]"
I found this in the SQL Error Log:
The activated proc '[dbo].[sp_sysmail_activate]' running on queue 'msdb.dbo.ExternalMailQueue' output the following: 'Could not create DatabaseMail.exe process. Executing API 'CreateProcess' failed with error number 2.'
