SQL Server Agent failed to start after server restart due to "the EventLog service has not been started"

Case Environment:

Operating system: Microsoft Windows Server 2003 Standard edtion SP2

Database version: SQL Server 2005 Standard Edition SP4

Case Description:

After the server restarts, the MSSQLServer service restarts automatically, but the SQLServerAgent service fails to start (of course, the SQL Agent service startup type is auto-start (Automatic)), which is the second time this server has encountered this situation. The first encounter was not too careful, thought it was only a special case, until the second time in this server encountered this situation, it feels a little strange.

Check the SQL Server Agent's log log to find the following error message: [241] Startup error:unable to initialize Error reporting System (Reason:the EventLog Service have not been started)

The check found that the event Log service also started normally. In this article SQL Server Agent failing to start because "the EventLog service have not been started" (Environment for SQL Server 2000) explains this is SQL SER A bug in the VER Agent code: As part of the startup task of SQL Server Agent, it initializes a record component that errors, A warning or informational message is reported to the agent's log file (Sqlagent.out), and if it runs on any non-Win9x platform, the event log (through the Windows API of the event Log service) as part of the initialization process, it must ensure that the Event Log service is started, Because if this is not the case, then the agent will not function properly, reported the above error and exit. ..........

The details of the blog are as follows

It happened to being a bug in SQL Server Agent's code, and here's a brief explanation of what's it is:

As part of SQL Server Agent ' s startup tasks, it initializes a Logging component, which would report errors, warnings, or in formational messages to Agent ' s log file (SQLAGENT. Out) and, if it's running on any non Win9x platforms, to the Event Log (by using the EventLog service Windows APIs). So, as part of this initialization process, it has to make sure that the EventLog service is started, because if it isn ' t, Then the agent cannot function properly and so it exits with the error described above.

The function which checks whether the EventLog service is running or not, calls EnumServicesStatus twice with Service_acti VE as the value for its dwservicestate parameter (so it would only enumerate services, that is in the following States:ser Vice_start_pending, Service_stop_pending, service_running, service_continue_pending, SERVICE_PAUSE_PENDING, or service_paused).

The first call to this API passes NULL to the lpservices parameter and zero for Cbbufsize, which is only determine the size Of the buffer required to the list of services in such States at the moment of the call. Later, it allocates as many bytes as the pcbbytesneeded parameter reflected that were needed, and finally it calls Enumser Vicesstatus again. If at the time of the second call to EnumServicesStatus there is more services on any of the states mentioned earlier, th An during the first call to EnumServicesStatus, then it returns FALSE and GetLastError returns ERROR_MORE_DATA.

The function implemented in SQL Server Agent to check whether EventLog was running or not and doesn ' t take into account such " Exceptional "condition and simply returns a failure as if the EventLog service wouldn ' t be running.

However, because the author describes the situation in the SQL Server 2000 environment, and I compared to the other environment at hand, SQL Server 2005 server, none of this problem, only this server has this problem, so it is not clear whether the SQL Server Agent Bug is caused by operating system environment problems. However, speculation and the above blog in the same way as the EventLog service has a certain relationship.

Workaround

There is no attempt to use SQL Server Agent failing to start because "the EventLog service have not been started" blog inside the method that I described in the SQL Server Agent service Recovery (Recovery) option, set the "Restart Service" action when the service fails to start, as shown below, after two Tests, it is found that this solution can also be satisfactorily resolved.

References:

http://blogs.msdn.com/b/ialonso/archive/2007/12/13/ Sql-server-agent-failing-to-start-because-the-eventlog-service-has-not-been-started.aspx

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/575904b4-72c5-4fbf-88fb-5c943f75a6e5/ Sql-server-agent-fails-to-start

The SQL Server Agent failed to start after a server restart because the EVENTLOG service has not been started