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.
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.
If we right-click, we didn’t see any option to make any change.
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.
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
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)
11 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.
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
Good one Pinal
Thanks!
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 :)
Well Pinar
In my case there is no registery key “NumErrorLogs” at all. SQL is in pending state and not writing error logs too.
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.
Thanks, It helped me.
THANK you for all the help you give