I have been dealing with a client who observed that SQL services not starting up after a server reboot. When I asked them to check event logs, they found below timeout error.
Event Type: Error
Event Source: Service Control Manager
Event Category: None
Event ID: 7009
Timeout (30000 milliseconds) waiting for the SQL Server (MSSQLSERVER) service to connect.
They also informed me another interesting fact. Once the reboot is completed, they can login to machine and when they start SQL Service manually, it starts fine. So, it looks like the system is very busy during startup of the machine and within 30 seconds there was no response from SQL to service control manager. Due to this the error message was reported in the event log. “Timeout (30000 milliseconds) waiting for the SQL Server (MSSQLSERVER) service to connect.”
If this issue happens only during startup and service starts fine while starting manually then we can try to increase the timeout from 30 seconds to 60 seconds. Here are the steps to change the timeout value.
- Click start->Run-> type regedit, and then click ok.
- Browse through the registry and then click the following registry sub key: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control
- Right-click on Control, point to New, and then click on DWORD Value.
- In the New Value #1 box, type ServicesPipeTimeout, and then press ENTER.
- Right-click ServicesPipeTimeout, and then clock Modify.
- Click Decimal, type the number of milliseconds that we want to wait until the service times out, and then click ok. For my client, we changed it to wait 60 seconds [60000 milliseconds])
- Quit Registry Editor, and then restart the computer.
For my client, there were many services and system performance was slow during startup. Adding the key helped them in getting the service started on reboot.
Have you ever used such trick to bypass performance issue? Please leave a comment.
Reference: Pinal Dave (https://blog.sqlauthority.com)