Here, sharing my experience to deal with tricky situations while working with SQL SERVER. We came across very strange situation like when we done some configuration changes on UAT server for the testing of Bulk operations from application after doing the change, restarted SQL services using SQL Server Configuration Manager without disconnecting database sessions with UAT server but obvious not advised and absolutely not a good practice.
SQL Server Services Failed
SQL Server services are failed to restart due to active database session, Always disconnect all database session before restart services.
SQL Service Error – Change Pending
So while restarting the SQL services, it got hung as expected and showing status as “Change Pending”. Ohhho !!! SQL server is down, users will not able to connect, Application testing is on hold. Before it get escalated to Boss, DBA’s responsibility to brought SQL Server Online.
So we tried one more time restarting service but no luck, failed again. Funny SQL server tells you the problem but doesn’t tell you what needs to done to resolve such problem that’s why company hired us DBA to take care this type of issue. To drill down issue, checked SQL server log why SQL Server is showing Change pending but didn’t find anything. Sometime it becomes very tricky to find the solution for such type of issue.
Suddenly mind strike and checked to find out whether SQL services are still running somewhere in memory, Best way to check running processes using TASK MANGER and found the sqlserver.exe in the list of running process and struck in memory.
So we decided to kill the sqlserver.exe process using task manager.
Good thing is after killing the process, services are started running smoothly.
Killed old process that was running in memory
Always disconnect all database session before restart services or applying any change to SQL Server