Who put the RMS in Maintenance Mode? (2nd update)

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
USE OperationsManagerDW
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 (
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 😉

Comments (2)
  1. Anonymous says:

    Hi Jeremy,

    Thanks for your recommendation about adding the (NOLOCK) option to the query for not impacting any other inserts in the database. And about your first question the "N" in the query, I just copied that line from some other query I already had somewhere. But according to the info I found online, this denotes that the subsequent string is in Unicode (the N actually stands for National language character set). Which means that you are passing an NCHAR, NVARCHAR or NTEXT value, as opposed to CHAR, VARCHAR or TEXT. More info here: databases.aspfaq.com/…/why-do-some-sql-strings-have-an-n-prefix.html.

    So maybe it's not really necessary but it cannot hurt the result IMO.



  2. Jeremy D. Pavleck says:

    Two things:

    1. Did you mean for there to be an "N" in "WHERE (ManagedEntity.FullName = N'Microsoft.SystemCenter.HealthService:rmsservername.contoso.com')"


    2. To ensure you don't impact any inserts into the database while youre using that query, you should add "WITH (NOLOCK)" to the FROM statement, so it would read like:

    FROM ManagedEntity WITH (NOLOCK)

    Otherwise nice work. Adding this to the useful opsmgr sql query page as well as a ton more that are now out there!

Comments are closed.

Skip to main content