Since yesterday morning SQL Server Agent on one of the SQL Server 2000 instances is not starting. This problem started ever since the Server had an unexpected reboot. The problem is SQL Server service starts but not the Agent. The errors in the Event Log are
The SQLSERVERAGENT service failed to start due to the following error: The service did not respond to the start or control request in a timely fashion.
Application popup: sqlagent.exe - Application Error : The application failed to initialize properly (0xc0000142). Click on OK to terminate the application.
These errors are very generic. The next step that I did was to take out the executable from the SQLSERVERAGENT service and run it manually from the command prompt.
When I ran this from the command prompt, the error message read StartServiceCtrlDispatcher failed (error 6).
I could not make sense even with this error. Then I started the agent in verbose mode to check if it gives more information as to why it is not starting.
sqlagent.exe -c -v
-C indicates that sqlagent.exe is running from command prompt and not as a service.
-v Enables the verbose logging mode on
To my surprise, this time there were no errors at all and the sqlagent.exe continued to run in the command prompt. The Agent Error Log (sqlagent.out) file did not have any errors at all.
Waiting for SQL Server to recover databases... Microsoft SQLServerAgent version 8.00.2282 (x86 unicode retail build) : Process ID Microsoft SQLServerAgent version 8.00.2282 (x86 unicode retail build) : Process ID 3072 SQL Server [servername] version 8.00.2282 (0 connection limit) SQL Server ODBC driver version 3.85.1128 NetLib being used by driver is DBMSSHRN.DLL; Local host server is 8 processor(s) and 2048 MB RAM detected Local computer is [servername] running Windows NT 5.0 (2195) Service Pack 4 The Messenger service has not been started - NetSend notifications will not be sent SQLSERVERAGENT starting under Windows NT service control Using MAPI32.DLL from C:\WINNT\SYSTEM32 (version 1.0.2536.0) Attempting to start mail session using profile 'Notification'... Mail session started (using MAPI1) An idle CPU condition has not been defined - OnIdle job schedules will have no effect
Since I was able to start sqlagent.exe, I ran sqlagent.exe under the credentials of the Service Account. Here as well sqlagent.exe executed successfully. With this it was clear that there was nothing wrong with the setup of SQL Agent, it had to do something with the service. Quickly checked if the Service Account had privileges on the Service, SQLServerAgent key in the registry.
Nothing wrong here as well. It had FULL permissions on all the registry keys and the Service Account is part of the Local Administrators group on the box and on the instance it has sysadmin privileges. I also checked in the Local Security Settings (secpol.msc) if this account was denied access somewhere. It was not the case. Strangely if I changed the Service Account to Local System and grant it sufficient privileges, the service starts normally. This clearly indicates that somewhere in the system there is a deny on some object for this account. Process Monitor tool would surely have helped in finding out this. But this server is running on Windows 2000 Server, I cannot run the Process Monitor.
No smiley, eureka this time With this I ran out of all options. As a final resort, a case has been raised with MS PSS. They surely have the old FileMon, Regmon tool which would work on Windows 2000 servers. Once they fix the issue, I will post here how it was fixed. Since there are no jobs running from SQL Server Agent there is no business impact and we have time to resolve this issue.
Am I missing something here? Do you have any suggestions to fix this?
Update on 3rd December 2010:
The issue started when the Server had an unexpected reboot. Today again the server had a unexpected reboot and guess what, SQL Server Agent started successfully
The root cause analysis is still on.