SQL SERVER – Error: Timeout (30000 milliseconds) Waiting for the SQL Server (MSSQLSERVER) Service to Connect

SQL SERVER - Error: Timeout (30000 milliseconds) Waiting for the SQL Server (MSSQLSERVER) Service to Connect stopwatch 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
User: N/A
Computer: SQLMachine
Description:
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.”

WORKAROUND/SOLUTION

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.

  1. Click start->Run-> type regedit, and then click ok.
  2. Browse through the registry and then click the following registry sub key: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control
  3. Right-click on Control, point to New, and then click on DWORD Value.
  4. In the New Value #1 box, type ServicesPipeTimeout, and then press ENTER.
  5. Right-click ServicesPipeTimeout, and then clock Modify.
  6. 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])
  7. 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)

, , ,
Previous Post
SQL SERVER – Msg 3168, Level 16 – The Backup of the System Database on the Device Cannot be Restored Because it was Created by a Different Version of the Server
Next Post
SQL SERVER – FIX: Msg 3009, Level 16 – Could not Insert a Backup or Restore History Detail Record in the msdb Database

Related Posts

3 Comments. Leave new

  • Phil Doensen
    June 26, 2017 6:07 am

    I have also found that setting the SQL server and Agent to startup type Automatic (Delayed Start) seems to fix this as well.

    Reply
  • But in some scenario this workaround doesn’t work. So what is the permanent solution for this.

    As we observed that even if making this changes service gives same error..

    Reply
  • !You saved me

    Reply

Leave a Reply

Menu