Recently i came across a startup issue with my SQL Server agent where it was not starting up with the error message 'The request failed or the service did not respond in a timely fashion consult the event log or other applicable error logs for details'
There was no useful information from the event viewer the only log i found for sql agent service in application log is 'SQLServerAgent service successfully stopped.' which doesn't help me here
SQL Agent logs also didn't give any clue
2018-03-26 09:58:40 - ? [129] SQLSERVERAGENT starting under Windows NT service control
2018-03-26 10:12:24 - ? [131] SQLSERVERAGENT service stopping due to a stop request from a user, process, or the OS...
2018-03-26 10:12:26 - ? [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)
2018-03-26 10:12:26 - ? [098] SQLServerAgent terminated (normally)
Since logs didn't give any clue i though of checking for any registry or filesystem access/permission issue using process monitor
Process monitor can be download from this path : Download link
To list only Registry and filesystem activity we can chose show registry and filesystem activity from main toolbar
To list events of only SQLAGENT i added a filter (CTRL+L) to display details only of process sqlagent.exe
Then i started event capture and tried starting the SQL server Agent service .I could see all the registry and filesystem activities captured in the procmon and i stopped the capture once the service failed to start
While analyzing the event i found an access denied message on a registry path
On checking event properties i found that sqlserveragent was missing read and set value in registry path 'HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL13.MSSQLSERVER\SQLServerAgent' once i gave set value and read permission to this path SQL Agent started up successfully. On comparing the permission in few other servers i found that sqlserveragent was having full control in the path so i gave full control in order to avoid issues which may occur due to lack of full access
SQL Agent logs also didn't give any clue
2018-03-26 09:58:40 - ? [129] SQLSERVERAGENT starting under Windows NT service control
2018-03-26 10:12:24 - ? [131] SQLSERVERAGENT service stopping due to a stop request from a user, process, or the OS...
2018-03-26 10:12:26 - ? [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)
2018-03-26 10:12:26 - ? [098] SQLServerAgent terminated (normally)
Since logs didn't give any clue i though of checking for any registry or filesystem access/permission issue using process monitor
Process monitor can be download from this path : Download link
To list only Registry and filesystem activity we can chose show registry and filesystem activity from main toolbar
To list events of only SQLAGENT i added a filter (CTRL+L) to display details only of process sqlagent.exe
Then i started event capture and tried starting the SQL server Agent service .I could see all the registry and filesystem activities captured in the procmon and i stopped the capture once the service failed to start
While analyzing the event i found an access denied message on a registry path
