Home SQL Compliance Manager SQL SERVER – What is Change Pending State of in SQL Server...

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

22
0
SHARE


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.

SQL SERVER - What is Change Pending State of in SQL Server Configuration Manager? sql-change-pending-01

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.

SQL SERVER - What is Change Pending State of in SQL Server Configuration Manager? sql-change-pending-02

When we right click, we don’t see any option to do anything.

SQL SERVER - What is Change Pending State of in SQL Server Configuration Manager? sql-change-pending-03

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)

Solarwinds





Source link

LEAVE A REPLY

Please enter your comment!
Please enter your name here