SQL SERVER – Why SQL Server Service is Stuck in Starting / Change Pending State?

Recently, I wrote a blog about “Change Pending” state of SQL Server service. After looking at that blog one of my readers contacted me via email for the “similar” issue. In this blog, we would learn about another variation of “Change Pending” state. ­­­­

Here is my earlier blog for your reference. SQL SERVER – What is Change Pending State of in SQL Server Configuration Manager?

This was the state in SQL Server Configuration Manager.

SQL SERVER - Why SQL Server Service is Stuck in Starting / Change Pending State? sql-change-pending-01

So, I went ahead and checked Services.msc and found that it was in “Starting” state and this is not same as my earlier blog.

SQL SERVER - Why SQL Server Service is Stuck in Starting / Change Pending State? sql-change-pending-02

Solarwinds

If we right-click, we didn’t see any option to make any change.

SQL SERVER - Why SQL Server Service is Stuck in Starting / Change Pending State? sql-change-pending-03

Now, I started to look to see what does ERRORLOG say and I was surprised to see that ERRORLOG file was not generated at all. If you are not aware of ERRORLOG file, then please refer one of my earlier blogs.

SQL SERVER – Where is ERRORLOG? Various Ways to Find ERRORLOG Location

Since there was no ERRORLOG, I wanted to know what sqlservr.exe is doing. So, I captured Process Monitor and found something interesting.

SQL SERVER - Why SQL Server Service is Stuck in Starting / Change Pending State? sql-num-errorlogs-02

As we can see, SQL Server is looking for ERRORLOG with some big number. I have generally seen ERRORLOG.6, ERRORLOG.5 etc. but here it was a big weird number.

Based on my search on internet, it is picked from registry key and I found below key NumErrorLogs

SQL SERVER - Why SQL Server Service is Stuck in Starting / Change Pending State? sql-num-errorlogs-01

Due to that big number, SQL Server was looking for that file and I guess it was trying to go until ERRORLOG.1 and hence taking time.

WORKAROUND/SOLUTION

Since SQL was always getting stuck in starting state, I was not able to connect and change the number of logs from SQL Server Management Studio. I killed sqlservr.exe from task manager, changed the value to of NumErrorLogs to 10 in above registry key and then attempted to start SQL service and Voila, we were back in business.

My client didn’t know how the value got changed but it was truly an interesting situation.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, , ,
Previous Post
SQL SERVER – How to Execute the Query Without Using Management Studio?
Next Post
SQL SERVER – What is Tail-Log Backups?

Related Posts

10 Comments. Leave new

  • Yikes! I’d be a little nervous changing a registry value behind SQL Server’s back but good that it worked for you.

    Reply
    • Well, someone has already made change and set bad value. Now for me, it was important to get SQL back to life. This was the only way I could find to solve this specific issue

      Reply
  • Kamran Shahid
    April 4, 2018 12:37 pm

    Good one Pinal

    Reply
  • Vaibhav Agarwal
    April 13, 2018 11:41 am

    You must thanks to Microsoft Human Resource Team who hire such brilliant developers (simply cherry pick from top institues like Harward etc) who leave SQL bugs during sql server app development which Pinal can solve :)

    Reply
  • Lufuno Ratshitanga
    July 1, 2018 9:56 pm

    Well Pinar

    Reply
  • In my case there is no registery key “NumErrorLogs” at all. SQL is in pending state and not writing error logs too.

    Reply
  • In My case SQL Server Agent Service was in Change Pending state. But I also checked registery key “NumErrorLogs” and I did not find it. I did not find anything in error logs and not in event viewer for same.
    So at last I had to reboot the Windows server and issue got resolve.

    Reply
  • Ekta Pandey Pareta
    January 30, 2020 5:20 pm

    Thanks, It helped me.

    Reply

Leave a Reply

Menu