SQL SERVER – What is Change Pending State of in SQL Server Configuration Manager?

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)