Recently, one of my clients contacted me for an issue about Change Pending State where they were having issues where SQL Server. As soon as I joined the call with them, they showed me below.
My next oblivious question was what was done in the SQL Server to come to this state? They told me that they had some major release of the application and there were some changes needed in the database as well. So that added a few new columns to an existing table. While doing that the tempdb database got full. They were in panic situation due to release pressure and so they changed the recovery model of the database to simple, to shrink the tempdb file. Finally, they decided to stop SQL instance via SQL Server Configuration Manager, the state changed as “Change Pending”.
Frankly speaking, I have never seen this state in configuration manager. I asked them to show me “Services.msc”. Here is what I saw there.
When we right click, we don’t see any option to do anything.
WORKAROUND/SOLUTION
Looks like the “Change Pending” state is shown in SQL Server Configuration manager when actual service is in stopping or starting state. In above situation, it was in stopping state, still we were able to make connection to SQL Server and able to run queries.
I ran below magical query to shutdown SQL service.
SHUTDOWN WITH NOWAIT
We were successfully able to shutdown SQL by using above command from SSMS query. Then we also were able to start SQL service normally.
Have you seen this behavior earlier? What was the solution you have used? I might have also used task manager to kill the SQL process, but I wanted it to do SQL way rather than windows way.
Reference : Pinal Dave (https://blog.sqlauthority.com)
40 Comments. Leave new
Thank you of sharing trick. It helps !
I can not connect to the SSMS because the service is starting.
Why we get SQL server “Change pending” state?, any other reasons.
restarting the server was my solution
thank you!
I had this problem after a problematic upgrade from sql server 2012 to sql server 2019. first the AV was blocking the upgrade process, we removed the AV then a policy from the AD activated the Windows Defender, but as the server wasnt able to connect to the internet, the smartscreen was marking the setup app as unknown, we disabled the Defender, and then found out the service user wasnt part of the adminsitrators group. we added the user to the admin group and proceed with the update, but at the last step it got stuck , we killed the sql service and the setup finished successfully. we made some tests and the service was good to go. the issue was that the application’s user that uses the server was sysadmin and could connect with no problem. but the next day we had issues with some reporting apps that wasnt working because this message “Only administrators can connect at this time”. I checked the services state in the configuration manager and found the Change pending message.