Ryzora's Blog

Sometimes in a busy server and high workload a rare event could happen when you want to stop SQL Service from either SQL Server Configuration Manager or Command Prompt. The SQL Service is stuck in Change Pending state but you can still login to SQL Server. It might look like the picture below. When this kind of thing happens, then login to SSMS with sysadmin role and then execute the following SQL Query Command: SHUTDOWN WITH NOWAIT Then the SQL Server Service will be stopped. After that you can continue to start the SQL Server Service again. This command will do the following: SQL Server exits after attempting to terminate all user processes. When the server restarts, a rollback operation occurs for incomplete transactions. This part of the solution is following Microsoft Documentation:  SQL Server Shutdown And that's all for stopping SQL Server in Change Pending State

If you notice that there is a database in secondary node in your AlwaysOn Availability Group SQL Server that has state "Not Synchronizing/Suspect" then its data movement is in error state and you might notice the huge growth in log file size either in Secondary or Primary. The thing is, you might need to resolve this issue quickly and after that try to troubleshoot what is the cause of the issue. Here is the step on how to resolve this issue if it happens.