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)
38 Comments. Leave new
Did you make use of DAC to connect?
No. I was able to make normal connections.
Great Solutions. This happened to me today.
Glad to know that it worked.
Thnx Brother. You saved my life :).
that’s a big complement.
@Pinal Sir, this post has improved my understanding of SQL. That means we have one state also of SQL services which at least I never knew. So the state of SQL cones to change pending when we stop SQL but we may come to know as its very fast. Thanks for it.
I also learned it so shared it with all of you.
Hi Pinal, I had similar issue with SQL agent when we tried to fail-over instance in cluster environment. I tried to kill PID however it still did not work and I seen it took around 30 mins to complete fail-over. The reason for this behavior was that cluster nodes was pending reboot post OS patches applied in previous months :)
Once both nodes rebooted with all pending updates, fail-over / fail-back started working like a charm.
Can you please confirm if it is the same issue with the server in question at your end?
Regards,
Ashwani
Doesn’t looks like same issue.
Came across this issue while changing the SQL service and Agent service account to a Group Managed Service Account on a stand-alone SQL 2016 Enterprise. The Agent changed just fine. When working with the engine from SSCM it would just say “stopping” but never stop. Tried some reboots and same steps to no avail. Tried using SSMS to stop the service, it then went into the “change pending…” status. Found your blog and used the SHUTDOWN WITH NOWAIT and it finally fully stopped the service. I was then able to change the service account to the gMSA. BTW, had to implement these changes also in order for the services to start upon reboot using the gMSAs:
Yeah, those are needed for gMSA. Thanks for sharing another reason.
Thanks very much. You saved my life today.
That’s a big one.
Thank you my friend for having the exact answer I needed!
–Kevin Hill
I have the same issue with SSAS tabular instance. Is there a T-SQL command too?
Never worked with SSAS. I guess reboot of the machine would be the only thing.
Thanks! Service Broker processes were not responding to restart request from configuration manager. This did the trick.
Thanks for sharing this Brian.
How about when process in service.msc have state is stopping?
sometimes you need to reboot server.
thanks you so much, regards
My pleasure.
why this happen what what was reason
“Stopping” state due to some antivirus activity.
This was my first time seeing this. Plenty of space on tempdb, but almost out of space on one of the mdf/data drives on the server. Maybe that’s what caused it, low space. Thanks for this post.
Thanks for sharing.
Just happened on one of my systems today. This did the trick. Thanks!
Perfect. Thanks.
This is happened to one of my prod. server EC2 instance. this solution worked for me.
This happened to one of my sql server 2016 prod server yesterday. It happened while a windows update process installing Security Update for SQL Server 2016 Service Pack 2 GDR (KB4532097). The update failed. The solution provided worked for me. Thanks.
After a long day looking for solution, finally I meet one here. It was exactly the same problem , thanks in advance.
Thank you very much, its a amazing tricks. It save me.
I had this issue with the SSAS Analysis Services being stuck on Change Pending in Config Manager
I found that in Task Manager, SSAS was running
I stopped it in Task Manager
I was then able to start SSAS in Config Manager