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 OperationsManagerDWSELECT 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.DisplayNameFROM vManagedEntity MESourceINNER JOIN vRelationship R ON R.SourceManagedEntityRowId = MESource.ManagedEntityRowIdINNER JOIN ManagedEntity METarget ON R.TargetManagedEntityRowId = METarget.ManagedEntityRowIdWHERE MESource.FullName = 'Microsoft.SystemCenter.RootManagementServerComputersGroup'))
Have fun pointing your finger ;-)
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!
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.