I was investigating an issue with OpsMgr Agents who where stuck in Maintenance Mode. A big help was this article from David Dixon.
We managed to solve the issue but we also wanted to know what caused the issue and I investigated if the RMS HealthService was put in Maintenance Mode accidentally. By the way, it is not a good thing to put the RMS in Maintenance Mode!
Update 2: I got an email from one of my co-workers Marek Tyszkiewicz telling me he made an enhancement to my SQL query to find out if the RMS HealthService was put in Maintenance Mode:
-- Find if RMS is put in MM
-- Author: Stefan Stranger
-- Version 0.3
-- Remark: Added (NOLOCK) to query. Thanks to Jeremy Pavleck.
-- Remark: No need to specify RMS server name. Thanks to Marek Tyszkiewicz
-- Date: 20-04-2011
SELECT ManagedEntity.DisplayName, MaintenanceModeHistory.*
FROM ManagedEntity WITH (NOLOCK)
INNER JOIN MaintenanceMode ON ManagedEntity.ManagedEntityRowId = MaintenanceMode.ManagedEntityRowId
INNER JOIN MaintenanceModeHistory ON MaintenanceMode.MaintenanceModeRowId = MaintenanceModeHistory.MaintenanceModeRowId
WHERE (ManagedEntity.DisplayName in (
SELECT DISTINCT METarget.DisplayName
FROM vManagedEntity MESource
INNER JOIN vRelationship R ON R.SourceManagedEntityRowId = MESource.ManagedEntityRowId
INNER JOIN ManagedEntity METarget ON R.TargetManagedEntityRowId = METarget.ManagedEntityRowId
WHERE MESource.FullName = 'Microsoft.SystemCenter.RootManagementServerComputersGroup'
Have fun pointing your finger 😉