伺服器重啟後SQL Server Agent由於"The EventLog service has not been started" 啟動失敗

標籤：

案例環境：

    作業系統   ： Microsoft Windows Server 2003 Standard Edtion SP2

    資料庫版本 ： SQL Server 2005 Standard Edition SP4

 

案例描述：

    伺服器重啟過後，MSSQLSERVER服務自動重啟了，但是SQLSERVERAGENT服務啟動失敗（當然SQL Agent服務的啟動類型為自動啟動（Automatic）），在這台伺服器第二次遇到這種情況，第一次遇到時沒太注意，以為只是特殊案例，直到在這台伺服器第二次遇到這種情況，才感覺有點奇怪。

檢查 SQL Server Agent‘s log日誌發現如下錯誤資訊：[241] Startup error: Unable to initialize error reporting system (reason: The EventLog service has not been started)

 

 

檢查發現Event Log服務也正常啟動了。在這篇文章SQL Server Agent failing to start because "The EventLog service has not been started"（環境為SQL Server 2000）解釋這個是SQL SERVER Agent代碼裡面的一個bug來的：作為SQL Server代理的啟動任務的一部分，它初始化一個記錄組件，將錯誤、 警告或資訊性訊息報告給代理的記錄檔（SQLAGENT.OUT），如果它在任何非Win9x的平台上運行，事件記錄（通過事件記錄服務的Windows API）作為初始化過程的一部分，它必須確保該事件記錄服務已啟動，因為如果不是這樣，那麼該代理無法正常運作，報上述錯誤後退出。..........

部落格具體內容如下所示

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

As part of SQL Server Agent‘s startup tasks, it initializes a Logging component, which will report errors, warnings, or informational 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 that 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_ACTIVE as the value for its dwServiceState parameter (so it will only enumerate services that are in the following states: SERVICE_START_PENDING, SERVICE_STOP_PENDING, SERVICE_RUNNING, SERVICE_CONTINUE_PENDING, SERVICE_PAUSE_PENDING, or SERVICE_PAUSED).

The first call to that API passes NULL to the lpServices parameter and zero for cbBufSize, that to only determine the size of the buffer required to hold 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 EnumServicesStatus again. If at the time of the second call to EnumServicesStatus there are more services in any of the states mentioned earlier, than 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 is running or not, doesn‘t take into account such "exceptional" condition and simply returns a failure as if the EventLog service wouldn‘t be running.

不過由於作者描述的情況發生在SQL SERVER 2000環境下，而我對比了手頭其他一樣環境的SQL SERVER 2005的伺服器，都沒有這個問題，只有這台伺服器出現這個問題，所以弄不清到底是SQL Server Agent的bug還是作業系統環境問題所致。不過猜測跟上面部落格裡面所敘說的那樣跟EventLog服務有一定關係。

 

解決方案

    沒有嘗試使用SQL Server Agent failing to start because "The EventLog service has not been started" 部落格裡面介紹的方法，我在SQL Server Agent服務的恢複（Recovery）選項裡面，設定當服務啟動失敗時，採取“重新啟動服務”的措施，如下所示，經過兩次測試，發現這個方案也可以完滿解決這個問題。

 

參考資料：

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

伺服器重啟後SQL Server Agent由於"The EventLog service has not been started" 啟動失敗