Microsoft has a support article, ID 2913046, that addresses the issue “The System Centre Management service stops responding after an instance of SQL Server goes offline” which basically involves creating two registry entries to ensure the service reconnects in 60 seconds, and therefore prevent it from going into a Not Responding state.
The “Symptoms” section of the article reads…
“After an instance of Microsoft SQL Server that hosts the OperationsManager database goes offline, the System Center Management service of the Microsoft System Center 2012 Operations Manager Service Pack 1 (SP1) management server stops responding.
For example, the System Center Management service stops responding after the instance of SQL Server disconnects, restarts, or fails. To recover from this issue after the instance of SQL Server is available again, you must restart the System Center Management service.”
This suggests this only applies to Operations Manager, but it equally applies to Service Manager and Service Manager Data Warehouse. The “Applies to” section does include Service Manager in the list, but the rest of the article doesn’t make this clear (in my opinion).
To view the registry changes required, visit https://support.microsoft.com/en-us/kb/2913046. I recommend these be applied to ensure a more stable connection experience for the management service to the relevant database.