Troubleshooting startup issues with process monitor

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

The '-K ' Startup parameter in SQL Server

Last week i created my first  SQL Instance in Amazon RDS,  Its pretty simple to  create an instance  and like any other Managed database service it does offers automated backups ,patching,monitoring etc and its easy to scale up as per your needs.

As Sql Error log is DBA's great friend i thought of  going through the error log and it always used to give me some great information and something interesting stroked me, the startup parameter they have used...........

2018-01-24 15:55:47.620 Server       Registry startup parameters: 

-d D:\RDSDBDATA\DATA\master.mdf

-e D:\RDSDBDATA\Log\ERROR

-l D:\RDSDBDATA\DATA\mastlog.ldf

-k 20.000000

-T 3226

-T 7806

yes its says about the master db file,error log location , Trace flag 3226 used to suppress logging of successful backup in errorlog , Trace Flag 7806 which is used to enable DAC on SQL Express edition(i created a express instacen). So whats -K option ??? i checked in  Microsoft docs on Database Engine Service Startup Options but didn't get any information and i didn't  get any usefully information from worlds favorite search engine too 😓 or i didn't user the correct key words to search  😋

After a long time searching got  details on '-K'  from the Checkpoint documentation.  It is used to throttle I/O  behavior based on the throughput of the I/O subsystem for some types of checkpoints.

In some cases, checkpoints might issue a substantial amount of I/O, causing the I/O subsystem to get flood with write requests, which can severely affect read performance.SQL Server includes a commandline option that allows throttling of checkpoint I/O by specifying  –k parameter, followed by a decimal number, which can be added to the list of startup parameters for the SQL Server service. The value specified indicates the number of megabytes per second that the checkpoint process can write. By using this –k option, the I/O overhead of checkpoints can be spread out and have a more measured effect.By default, the checkpoint process makes sure that SQL Server can recover databases within the recovery interval that you specify.So by using -k option may cause longer recovery time based on the specified value.The –k option doesn’t apply to indirect checkpoints. So in this case checkpoint I/O  is throttled to 20 megabytes per second

I have discussed this with a few DBA's on this option and no one had used this option or came across a scenario where they have observed checkpoint was causing I/O issues. If you guys have come across such a scenario please do share them in comments