(This post has been extended with SQL Server Agent failing to start because "The EventLog service has not been started" (Second Part))
One customer reported to us that their SQL Server 2000's SQL Server Agent services, recently started to fail when trying to come up running, automatically after a reboot of the operating system hosting the SQL.
When that happened, the following messages were dumped into SQL Server Agent's log (SQLAGENT.OUT):
2007-11-04 20:23:01 - !  Startup error: Unable to initialize error reporting system (reason: The EventLog service has not been started) 2007-11-04 20:23:21 - ?  SQLServerAgent terminated (normally)
Customer claimed that even after creating a dependency on EventLog service for SQL Server Agent service, the error continued to show up after every machine reboot.
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.
A fix would be required that would rewrite the mentioned function, so that it retries the calls to EnumServicesStatus everytime the second call of the loop returns FALSE and GetLastError returns ERROR_MORE_DATA. But since that fix doesn't meet the bar at this point as to be addressed via a hotfix, I better give you a couple of workarounds you could implement in the event you encounter yourself face to face with this problem.
As it was already mentioned earlier, a simple dependency rule on the Service Control Manager doesn't suffice. So, your two options are:
use master go create proc spStartAgent as -- Version 1.0 by JohannWe, EMEA GTSC, Microsoft customer support set nocount on declare @inst sysname, @cmd nvarchar(1000), @rc int, @msg nvarchar(1000) set @msg=null set @inst=convert(sysname, serverproperty('InstanceName')) if @inst is null set @cmd=N'net start SQLServerAgent' else set @cmd=N'net start SQLAgent$' + ltrim(rtrim(@inst)) create table #res (id tinyint not null identity, msg nvarchar(1000)) insert #res(msg) exec @rc=master.dbo.xp_cmdshell @cmd if @rc=0 return 0 else if @rc=2 begin select @msg=msg from #res where id=3 and msg like '%NET HELPMSG 2182%' if @msg is not null -- we get error 2182, if SQLAgent was already started return 0 else begin raiserror ('An error was raised while starting SQLAgent. See System Event log for details!' , 19, @rc) with log return 1 end end go create proc spStartAgentDelay as declare @t char(8), @rc int set @t='00:01:00' -- Edit to change waiting time! waitfor delay @t exec @rc=spStartAgent go -- Register as startup proc: declare @rc int exec @rc=sp_procoption 'spStartAgentDelay', 'startup', 'on' select ReturnCode=@rc select IsStartup=objectproperty (object_id('spStartAgentDelay'), 'ExecIsStartup') go
I have found that SQL Server needs Read permissions on both the 'Event Log' and the 'Remote Procedure Call' services.
In a default Windows 2003 install, SQL Server should have the required permissions to these services. However, in an environment where all service permissions are locked down by GPO, then SQL will not have access to these services, and BOL does not say that access is required. If you control service access via GPO, then the relevant SQL service accounts must be given Read access to these services.
The impact of not having Read access to Event Log is that SQL Agent will not start.
The impact of not having Read access to the RPC service is that no connection can be made to MSDTC.
Thank you very much for your feedback. I've filed a bug so that the guys in product team review your feedback and update the documentation accordingly.
Regarding the Event Log service, without further testing, I would expect it to be sufficient with "Query Service Status" granted.
SQL Server Agent が起動に失敗する : 「サービス EventLog は開始されていません」
Thank you very much for this. Any chance that you could convince the powers-that-be at Microsoft to release a Knowledge Base article about this?
I've implemented the suggested script and am still monitoring for failures. I'm getting a message in my Agent Event Log 1 minute after SQL Server startup (after reboot) as follows:
 Alert 'Demo: Sev. 19 Errors' has occurred
This error also shows up in the Windows Application Event Log.
That message is showing because your SQL Server Agent ErrorLoggingLevel is set so that it reports informational messages, and you have one Alert defined which is triggering. Based on the name of the alert "Demo: Sev. 19 Errors" I'd bet it is a SQL Server event alert, which will trigger whenever it identifies an error whose severity is 19.
If you want to avoid that message, either set your Agent's logging level to not include informational messages, or disable/delete that alert if it isn't important to you.